Ryzora's Blog

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...

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 previou...

 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...

On this part I'm going to show a demo on how to set up SQL Server AlwaysOn Availability Group on local computer. First, we need to have virtual machines on our local computer because we need to spawn at least 3 VM servers that will be domain controller, SQL Server 1 (primary), and SQL Server 2 (secondary) To do that, you need to install VMWare Workstation first (or any other tools for virtual machine), it can be downloaded for free from:  VMWare Also you need to have Windows iso, SQL Server iso, and SSMS installer. What I use: Windows Server 2019 evaluation edition from Microsoft:  Windows Server 2019 ISO SQL Server 2019 Developer Edition:  SQL Server Developer Edition Downloads  (this, you need to login first because official SQL Server Developer Edition now can only be downloaded from Visual Studio website) SQL Server Management Studio:  SSMS Notes: if you are planning to use SQL Server 2012 or earlier, you need to download .NET Framework 3.5 installer too bec...

  What is SQL Server Management Studio (SSMS)? SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, administer, and develop all components of SQL Server,  Azure SQL Database ,  Azure SQL Managed Instance ,  SQL Server on Azure VM , and  Azure Synapse Analytics  . SQL Server Management Studio provides one comprehensive utility that combines an extensive group of graphical tools with many rich script editors to provide access to SQL Server for developers and database administrators of all skill levels. SSMS is a must have tool if you want to easily manage your SQL Server. You need a windows computer to use the tool, but it can connect to any SQL Server supported by Microsoft including SQL Server hosted in Linux OS. Almost all activities in SQL Server can be managed by using SSMS, and it provides GUI interface so it’s easier to get a good look on SQL Server. Download SQL Serv...

  Get started Editions and features : Review the supported features for the different editions and versions of SQL Server to determine which best suits your business needs. SQL Server 2022 (16.x) SQL Server 2019 (15.x) SQL Server 2017 (14.x) SQL Server 2016 (13.x) SQL Server 2014 (12.x) Requirements : Review hardware and software installation requirements for  SQL Server 2016 and SQL Server 2017 ,  SQL Server 2019 ,  SQL Server 2022 , or  SQL Server on Linux , as well as system configuration checks, and security considerations in  Planning a SQL Server Installation Installation media The download location for SQL Server depends on the edition: SQL Server Enterprise, Standard, and Express editions  are licensed for production use. For the Enterprise and Standard Editions, contact your software vendor for the installation media. ...

Microsoft SQL Server is a relational database management system (RDBMS). Applications and tools connect to a SQL Server  instance  or  database , and communicate using  Transact-SQL  (T-SQL).