Ryzora's Blog

If you notice that there is a database in secondary node in your AlwaysOn Availability Group SQL Server that has state "Not Synchronizing/Suspect" then its data movement is in error state and you might notice the huge growth in log file size either in Secondary or Primary. The thing is, you might need to resolve this issue quickly and after that try to troubleshoot what is the cause of the issue. Here is the step on how to resolve this issue if it happens.

When you apply a partition to SQL Server table you might have a question, what if the partition that was created is reaching its limit? And what if it's already past the limit and new data keeps on coming? The answer is simple, if there is no more partition for the data then all the new data outside of the partition range will be placed at the PRIMARY filegroup which was created at default by SQL Server when a Database is created or it can be dumped all on the latest partition. So then, how to extend a partition when it's already at limit or even nearing its limit? On this part, we will see how to extend a partition from an already partitioned table. Partition usually is made by Date, which can be an Int data type or Date data type. To setup a partitioned table, follow the previous guide on setting partition on SQL Server:  SQL Server Setup Partition

Sometimes we want to know the performance of the server that host SQL Server or even check the performance of SQL Server itself for a period of time. One way of doing it is by using the Performance Monitor tool which is available on the Windows Server and we can schedule this tool to capture the data (which is called performance counter) to later be analyzed using PAL tools. For this part, you need to download PAL tool first and install it on your windows: PAL Tool (you can download the PAL 2.8.2.1 installer from the github) Here are the steps that can be followed to setup Performance Monitor and Use PAL Tool.

One of the thing that we can do to maintain data distribution is by using partitioning on the table. By using partitioning you can partition datafiles according to the partition function which will be mapped to each filegroup. This can be used to maintain data files distribution on drives so we can separate disk drives for certain data which might be used frequently on a specially differentiated fast disk. On this part, I will show you how to setup this partitioning on a table.

When we are migrating from one SQL Server Instance to another SQL Server Instance, often times we can migrate databases without any issue but the problem that occurs next is that the Logins for Users in each database are actually not migrated. The next problem is that we can create the same Logins but we need to input the password again, the thing is, sometimes it's not feasible to create same Logins with different password because this Logins might already be used by users for their processes. So in this part I want to show how to migrate SQL Server Instance Logins to new SQL Server Instance. This is very quick but helpful guide and this is official from Microsoft Documentation. You can refer to the documentation here:  Transfer Logins

Change Data Capture (CDC) Change data capture utilizes the SQL Server Agent to log insertions, updates, and deletions occurring in a table. So, it makes these data changes accessible to be easily consumed using a relational format. The column data and essential metadata need to apply these change data to a target environment are captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables. Furthermore, table-valued functions are available for systematic access to this change data by consumers. Change data capture is used by application to track changes which happen in database. Some ETL apps like Kafka need this feature to be enabled when we want to stream data from one database to another destination. CDC is a feature at table level, so you need to enable this per each table if there are many tables in a database that need to be tracked. Here is the steps to setup CDC for tables in database.

Move Database Files to Different Location on Disks On this part I'm going to show you how to move database files from one location to another location on the server disks. Bear in mind that this will need downtime for the database, so you need to make sure that there is no activity going on inside the SQL Server to the database. Also you need to make sure the new disk drives have enough spaces to store data files and log files with also in consideration of the files' growth.

Cannot Connect to SQL Server Sometimes after you install/setup SQL Server and trying to connect to the server you might face this problem: A network-related or instance-specific error occured while establishing a connection to SQL Server. The server was not found or was not accessible. This is a very common problem that often occurs and it is related to the user trying to connect to the SQL Server but cannot find the server or it is not accessible from the user's current connection. So on this docs I will show you how to troubleshoot this problem: 1. Check the Instance Name or IP Address Before we proceed further, make sure that you typed the correct SQL Server instance name or IP Address which you are trying to connect to. If it's a typo then everything should just be fine after you input the right name or IP Address 2. Check SQL Server Protocol and Port Go to the Server which host the SQL Server instance, and then go to SQL Server Configuration Manager. Make sure the protocol...