I blogged about this in August, but wanted to do this video as well:
Thanks for reading and/or watching!
If you need to find the SQL Server ErrorLog in a hurry and don’t want to spend 30 minutes drilling into every drive on the server:
“I don’t watch videos” version:
That’s it…let me know if you already knew this or not!
Thanks for reading!
As a CIO or CTO, one of your primary responsibilities is to ensure that your organization’s data is managed effectively and efficiently. To do this, you need to have a strong understanding of SQL Server best practices. In this post, we’ll discuss some of the most important best practice areas for SQL Server management.
This is a high-level discussion with items that we will explore more in future posts
Deploying regular backups:
One of the most important best practices for SQL Server is to deploy regular backups. This will ensure that your data is always protected in the event of a system failure or data loss. There are many different ways to backup your SQL Server data, so it’s important to choose the method that best suits your needs.
Another important best practice is to incorporate security measures into your SQL Server deployment. This will help to protect your data from unauthorized access and prevent malicious attacks. There are many different security features available in SQL Server, so it’s important to choose the ones that best fit your organization’s needs.
Another vital best practice is to monitor the performance of your SQL Server regularly. This will help you identify any potential issues and correct them before they cause major problems. There are many different performance monitoring tools available, so it’s important to choose the ones that best fit your organization’s needs. Some of these are free, some require licensing.
High Availability and Disaster recovery plans:
Another crucial best practice is to implement a disaster recovery plan for your SQL Server estate. This will ensure that your data is always safe and accessible in the event of a major outage or disaster. There are many different disaster recovery strategies available, so it’s important to choose the ones that best fit your organization’s needs and budget.
Finally, it’s also important to maintain comprehensive documentation for your SQL Server deployment. This will help you keep track of all the different settings and configurations, and make it easier to troubleshoot any issues that may arise. Comprehensive documentation also makes it easier to train new staff members on how to use and manage your SQL Server deployment.
These are just a few of the most important SQL Server best practices for CIOs and CTOs. By following these best practices, you can help ensure that your organization’s data is managed effectively and efficiently. If you have any questions about these best practices, or if you need assistance implementing them, please contact us today. We would be happy to help you get started!
Thanks for reading!
How long has this worked, and why didn’t anyone tell me?
A typical restore statement from a FULL backup and a LOG backup:
USE [master] RESTORE DATABASE [XE_demo] FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 25, REPLACE RESTORE LOG [XE_demo] FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' WITH FILE = 1, NOUNLOAD, STATS = 25 GO
100 percent processed. Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE DATABASE successfully processed 354 pages in 0.016 seconds (172.393 MB/sec). 30 percent processed. 60 percent processed. 90 percent processed. 100 percent processed. Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE LOG successfully processed 27 pages in 0.011 seconds (18.909 MB/sec). Completion time: 2022-05-11T09:26:44.3023396-05:00
Change the Restore Log to Restore Database:
USE [master] RESTORE DATABASE [XE_demo] FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 25, REPLACE RESTORE DATABASE [XE_demo] FROM DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' WITH FILE = 1, NOUNLOAD, STATS = 25 GO
100 percent processed. Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE DATABASE successfully processed 354 pages in 0.015 seconds (183.886 MB/sec). 30 percent processed. 60 percent processed. 90 percent processed. 100 percent processed. Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1. Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1. RESTORE LOG successfully processed 27 pages in 0.006 seconds (34.667 MB/sec). Completion time: 2022-05-11T09:30:42.0225244-05:00
I had no idea the engine would determine the type of backup file and apply it appropriately. I knew it worked for Differential backups, but not Log.
20+ years and I still find new (to me) things every day. Technically Jon (t) found it but hey…we’re a team here 🙂
Thanks for reading!
We are currently performing migrations with upgrade of multiple-instance SQL Servers to new servers. The migrations are going smoothly (knock on wood), and I wanted to relay some information about the migration process. There are four phases you need to perform when migrating a SQL instance to a new server. These phases are Review, Prepare, Test, and Migrate. It sounds simple enough but let’s take a deeper look into what goes into each phase.
In this case, the Test server and instances are migrated, but the matching Production server and instances have not been. A followup blog is expected with any lessons learned.
During the review phase we check the existing environment for best practices, the SQL databases for upgrade readiness and the target SQL server for appropriate sizing.
When it comes to best practices there are Microsoft SQL Server best practices, vendor best practices and industry best practices that need to be considered. Here are two new best practices that I dealt with during the migrations:
All best practices need to be reviewed and discussed on whether they will be implemented or not. Check the current environment and determine why a best practice is not followed and if it should be followed.
If you are upgrading SQL Server to a new version during the migration, Microsoft provides a tool named the Data Migration Assistant to help determine database upgrade readiness. This tool will assess databases for compatibility issues by SQL Compatibility Level. You may find that you have issues that exist in your databases at your current compatibility level.
When migrating to a new SQL server this is a great time to determine if you need to modify the resources on the server. Do you need more or faster CPUs? Do you need more memory? Do you need larger storage volumes? This needs to be discussed and decided during the review step. Microsoft provides an advanced tool named the Database Experimentation Assistant which can be used to determine how your workload will perform on the target SQL Server. It will also provide query compatibility issues and degraded queries and query plans.
During the prepare phase we are determining the migration method, making changes to service accounts and permissions, deciding on shared feature migration such as SQL Server Integration Services and SQL Server Reporting Services, configuring the target SQL Servers, and using Query Store for query regression review.
There are multiple methods of migrating the databases to a new SQL Server instance. These include:
There are a couple of tools to help make the backup/restore method easier.
During install and configuration of SQL Server you can choose Windows accounts to use for the various SQL Services. Some of the common services that need Windows accounts are:
For these services it is an industry best practice that you utilize domain accounts. When setting the services to utilize domain accounts this can be done during installation or using the SQL Server Configuration Manager utilities after installation. It is highly recommended that accounts are set using either method above as the minimum required permissions should be set automatically for the domain accounts. If you set service accounts manually through the services control panel, you will have to manually set permissions for the accounts. It is also an industry best practice that you allow SQL Server to set permissions for domain accounts and do not elevate the permissions for those accounts without an acceptable reason.
Prior to migration you need to determine which shared features will be migrated to the target SQL server. Not all SQL shared features have to run on the same server as the SQL Server database engine service. These include SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS). Plan out the migration of these shared features as they will be done separately from the database migrations. Starting with SQL Server version 2017, SQL Server Reporting Services is a stand-alone installation that is no longer part of the SQL Server installation. This means:
When planning out the target SQL Server configuration is the time to make changes to the SQL Server environment. Aside from the best practice configurations that have been reviewed and agreed upon, now is the time to clean up the database file structure if necessary. According to industry best practices you should separate your database files as follows:
Each type of file above has different requirements on the storage sub-system. Depending on your environment you can have separate I/O queues for each volume or you can have physical drives that meet different read and write patterns. Plan this out appropriately when configuring the target SQL Server(s). Logically separating the files can make future infrastructure changes easier.
An option you can use during migration is the Query Store. You can enable Query Store in the databases once they are running on the target SQL Server (SQL 2016 and higher) and leave the database compatibility level to the same level it was on the source SQL Server. After a few weeks of running and collecting data in Query Store you can update the compatibility level of the databases to the new level of the target SQL Server. Query Store provides reports on “Regressed Queries” and “Query Wait Statistics” that can be used to determine which queries are performing worse on the newer version. Query Plans can be forced to quickly revert queries to their previous plan prior to the compatibility level change and can then be evaluated for tuning.
During the test phase we are testing our migration plan. There are always unforeseen issues during a migration regardless of how well you plan. Here are things testing can help with:
Aside from just testing the migration plan and working through the timing and unforeseen issues you should plan for end user acceptance testing. Here are things end user acceptance testing can help with:
If you have a test environment for your application, then test phase can also be used to migrate the test environment permanently to the target SQL Server(s). If you do not have a test environment, then this is a good time to consider implementing one. If you are unable to implement a test environment, then use the production target SQL Server for your test migration and consider it a dress-rehearsal for the final production migration.
Once you have reviewed, prepared, and tested your migration then it is time to migrate. You should have a solid idea of how long you will need for downtime and how long it will take to test the new environment. Plan for rollback to the source environment in the event of unforeseen issues:
Define and agree on success criteria for the migration. Success criteria will help define when a migration is considered successful. If this is defined ahead of the migration and is acceptable to all parties involved, when the criteria are all met you can send out communication calling the migration a success. This will help with future communication claiming the migration was a failure or the project stretching out for weeks and having to discuss whether an issue is a “migration” issue or a normal operating issue. Examples of success criteria can be:
A SQL Server migration is fundamentally the same as any system migration. You may have some considerations which are specific to SQL server but overall, the phases of the migration will be the same. If you properly follow the four phases of Review, Prepare, Test, and Migrate then you will not just have a successful migration of your SQL Server, you will have a successful migration of your SQL Server in the eyes of your client or manager.