Explaining SQL Server in plain english
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!
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
Results:
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
Results:
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.
This is not another IRL post, but rather is more of “Help, my SQL Express database is full!” post.
I was contacted by a lawyer that was using a 3rd party application to store emails, keep track of time, etc.
The backend of the application is SQL Server Express edition, which has a hard limit of 10GB for the data file.
The biggest table by rowcount and size in the DB is (of course) the email table, which stores the email body in a ‘text‘ datatype. I don’t know what version of SQL Express he is using, but I was working with a restored copy on SQL 2016 Developer. His vendor was no help.
Steps we tried after a lot of digging and testing:
0. Initial datafile size: 9969MB
1. Purge oldest 20%
Delete [email] Where sysid in (Select top 20 percent sysid from [email] order by date); go
File size 9600MB. Not much improvement
2. Purge by a specific date
Delete [email] Where [date] < '2012-01-01'
File size 9500MB. Slightly better.
3. Of the remaining emails, delete by largest memo field, older than 1/1/2017:
Select top 1000 sysid, len(Cast(memo as nvarchar(max))) as size Into #BigMemos from [email] Where [Date] < '2017-01-01' Order by len(Cast(memo as nvarchar(max))) desc Delete [email] Where sysid in (Select sysid from #BigMemos) Drop table #BigMemos
Still not much difference, but at this point I know the indexes and half-full pages must be in full chaos mode
4. Rebuild, then Reorganize all of the indexes on this table
Nope...can't show the statements...too much application specific naming :)
Down to 9200MB
5. Time to Shrink!
DBCC SHRINKFILE (N'MyDB', notruncate); GO DBCC SHRINKFILE (N'MyDB', truncateonly); GO -- Yes, both were required, and in this order. Your mileage may vary
This got us down to 8696 MB, which will last my new lawyer friend a solid 3-5 years.
And yes, I billed him lawyer rates for this 😉
If you need part-time DBA help, or just want to have a Senior DBA around for emergencies, Contact us!
Thanks for reading!
I was chatting with Jeff (b|t) on my team yesterday and the context escapes me but I had this thought:
“Can you Group By the beginning characters, or a subset, of a field?”
I’m not a developer, so this question never comes my way. Except yesterday.
TL;DR – Yes.
Basic Group By code, using StackOverflow public data:
Select [Location], Count(Location) From StackOverflow..Users Where [Location] Like 'D%' Group By [Location] Having Count(Location) > 1 Order By [Location]
Same code, Grouping By the first 6 characters:
Select Left([Location],6), Count(Left(Location,6)) From StackOverflow..Users Where [Location] Like 'D%' Group By Left([Location],6) Having Count(Left(Location,6)) > 1 Order by Left([Location],6)
Results. Lets pretend I was looking for people in Dallas.
There is more than 1 Dallas BTW:
I’m sure there are better use cases…but I only spent 7 minutes on this nonsense, just to see what would happen.
When find yourself saying “I wonder if SQL Server can….”, go try it. Learn first, Google later 🙂
Thanks for reading!
