Ryzora's Blog

Sometimes in a busy server and high workload a rare event could happen when you want to stop SQL Service from either SQL Server Configuration Manager or Command Prompt. The SQL Service is stuck in Change Pending state but you can still login to SQL Server. It might look like the picture below. When this kind of thing happens, then login to SSMS with sysadmin role and then execute the following SQL Query Command: SHUTDOWN WITH NOWAIT Then the SQL Server Service will be stopped. After that you can continue to start the SQL Server Service again. This command will do the following: SQL Server exits after attempting to terminate all user processes. When the server restarts, a rollback operation occurs for incomplete transactions. This part of the solution is following Microsoft Documentation:  SQL Server Shutdown And that's all for stopping SQL Server in Change Pending State

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.