Ryzora's Blog

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

In this part I will show you guide to setup SQL Server Database Log Shipping. SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled. SQL Server Log Shipping consists of some jobs, so you must enable SQL Server Agent Jobs: backup job: A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance. copy job: A SQL Server Agent job tha

SQL Server Failover Cluster Instance (FCI) On this guide, I will show you how to setup a SQL Server Failover Cluster Instance or mostly called SQL Server FCI. It is a bit different than SQL Server AlwaysOn Availability Group where we can have primary node (owner node) and secondary node but the SQL Server itself is actually treated as a single Instance using Shared Cluster Disk.  Notes: This guide will use VMWare on my local computer for testing purpose only The requirements for setting up SQL Server Failover Cluster Instance are these: - Primary node and Secondary node for hosting SQL FCI joined to Active Directory - Windows Server Failover Cluster (WSFC) configured for all nodes - Shared Cluster disk for the Cluster which will be used for SQL FCI - Network Name for the SQL Server name in Active Directory - An IP Address for SQL FCI For configuring and installing SQL Primary node and Secondary Node, also with configuring and installing WSFC you can follow the guide which I previously

 This part will show you how to setup a database mirroring on SQL Server. Because this feature needs 2 different servers (3 if you use witness server) so I will use the server that I've already set on the previous part when showing how to setup SQL Server AlwaysOn Availability Group, you can check that part on:  here So in summary I already have 2 servers that host SQL Server, called SQL01 and SQL02, the servers are already joined to domain and can connect to one another. And now I want to setup a database mirroring from SQL01 to SQL02, here are the steps that should be done: 1. So in here, I created a new database called "Test_Mirror" and also a table inside the DB called SimpleTable on SQL01, I want to make this server the primary server 2. Now the next thing to do is, we need to backup and restore the Test_Mirror DB on the SQL02 which will become the mirror server (secondary server). Database mirroring is quite different than AlwaysOn AG because there is no automatic s