DallasDBAs.com

Explaining SQL Server in plain english

Kevin3NF

SQL Server Migration and Upgrade

by 2 Comments

(This post written by Jon Biggs (t), one of our Senior DBAs)

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.

Review

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:

  • AWS EC2 best practice is to utilize Lock Pages in Memory (LPM). While LPM was not a new best practice to me, it was surprising to learn that AWS recommends this configuration when running SQL Server on EC2 VMs.
  • Microsoft SQL Server best practice when using FILESTREAM is to disable “last access time” and to disable “8 dot 3 naming” on the volumes where FILESTREAM data is stored. Disabling 8 dot 3 naming led me to the options of disabling this setting globally or per volume. It turns out that per volume is the default configuration for Windows Server.

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.

 

screenshot of Data Migration Assistant
(click to enlarge)

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.

Prepare

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:

  • Backup/Restore. You can simply backup the databases, copy the backup files to the new server and restore the databases. This can be a time-consuming process which you may not have the downtime for. A few things to help speed this up:
    • Backup the databases directly to the new server, eliminating the copy step. This requires the network share to allow the source SQL Server service account write permissions. This is the method I am using for the migrations as we have the time to do this, and we are using SQL Server Standard.
    • If you have a SAN, move the volume with the backup files to the target SQL Server, also eliminating the copy step.
    • You can perform a full backup of the databases ahead of time and restore them with NO RECOVERY. When the migration window starts you can perform a differential backup of the databases and restore them with RECOVERY. A caveat here is that you must perform Copy-Only backups if a full backup is required before the migration starts, otherwise you will break the backup chain.

There are a couple of tools to help make the backup/restore method easier.

  • DBATools.io is a set of Powershell commands for migrating databases, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, etc. A caveat with using dbatools to migrate databases with the Start-DbaMigration command is that it does not move Full Text Index Catalog(s) to a new location if you are migrating to different paths on the target SQL Server. If the source path does not exist on the target SQL Server, the restore of the database will fail.
    • If this is the case, you can manually backup/restore the database using with MOVE commands and it will be successful.
    • There are other dbatools commands for migration that can be used to avoid this scenario as well.
  • Microsoft Database Migration Assistant can be used to migrate databases using the backup/restore method.
  • Detach/Copy/Attach. You can simply detach the databases from the source SQL server instance, copy the database files to the target SQL Server and attach them to the target SQL Server instance. This is faster than backup/restore but does require time to copy the files over the network. There is also the risk of data corruption occurring during the copy process.
    • If you have a SAN, move the volume(s) with the database files to the target SQL Server.
    • If you have a SAN, replicate or snapshot the volume(s) with the database files to new volumes. Shut down the source SQL Server services so the databases are in a clean, shutdown state prior to final replication or snapshot.
  • Log Shipping. With log shipping you can have the databases restored on the target SQL Server ahead of migration with NO RECOVERY and continuously restore the Transaction Log backups to the target SQL Server to keep the databases synchronized. When it is time to migrate, the final Transaction Log restore is done with RECOVERY and the databases are brought online. This is also an older Disaster Recovery method for SQL Server and the databases can be brought online without a restore of the last set of Transaction Log backups.
  • Always On Availability Groups. If you are using the Enterprise version of SQL Server and upgrading from 2012 or later, you can utilize Always-On Availability Groups to migrate and upgrade. Configure a target AG Secondary replica that is a higher version of SQL Server than your source SQL Server and failover to the target AG Secondary replica to finalize your migration with upgrade.

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:

  • SQL Server database engine service
  • SQL Server Agent service
  • SQL Full-text Filter Daemon Launcher service
  • SSIS service
  • SSAS service
  • SSRS service

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:

  • You can only have one SSRS installation per Windows Server now. You can no longer have an installation of SSRS per SQL Server instance.
  • If you are migrating multiple SQL instances to a new server and you have multiple installations of SSRS on your source server(s) you will have to plan out how to combine them into one installation or choose to migrate them to separate Windows Servers.
  • You can use the rs.exe utility to migrate reports from one SSRS install to another SSRS install. This can be done at various folder levels. In our migration we migrated one SSRS install over to the target server as-is and then migrated a second SSRS install into a sub-folder on the target server.

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:

  • System database files
  • TempDB database files
  • User Database files (.mdf, .ndf)
  • User Database log files (.ldf)

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.

Test

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:

  • Migration method timing can be gathered during testing. You might have looked at your backup times and doubled it for migration but restore could take longer than anticipated. Getting an accurate length of downtime for the migration will help meet expectations.
  • Tool or script usage can be tested. Did you have any unexpected failures during the migration?
    • For me I had issues with dbatools and full text index catalogs that I had to work through.
  • SQL feature migration can be tested. Was it straightforward migrating SSIS or SSRS?
    • For me I had issues restoring the SSIS database master key that I had to work through.
    • I also had issues with getting SSRS Subscriptions working again on the target server due to a stored procedure referencing the SQL server by name.

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:

  • Once the SQL server has been migrated, do the applications still work correctly?
  • Did you have trouble changing DNS or connection strings?
  • Did you forget about a server that was part of the application that needed to be changed?
  • Were any settings on the SQL Server that affect the application missed?
    • For me I missed setting trace flags in SQL on the target SQL server.
    • I also missed static ports that were set on the source SQL server for the named instances.
    • These are things I know should be done but when juggling moving parts, they can get dropped and the testing phase is the appropriate time to catch them.
  • Is all functionality of the application still working when using the target SQL server?
  • Is your testing plan comprehensive of all the components affected by the migration?

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.

Migrate

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:

  • If issues come up during migration or testing, then determine the window of time allowed to resolve those issues before rolling back to the source environment. If you cannot be down for more than X hours and the testing has not been completed by that time you need a plan to get the source environment back online to maintain business continuity.
  • When planning for rollback, define steps that are go/no go for rollback. If you reach a point where it is more beneficial to resolve issues than roll back or where live data is being inserted into the target databases, then you can define a point where rollback is no longer an option.
    • You can mandate that all parties involved in the migration give consent to proceed past a point where rollback is no longer an option.

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:

  • End user acceptance testing is completed with all tests passed.
  • Applications Y and Z come online.
  • If application X is not online, it will not affect the success of the migration.
  • SSIS packages run successfully.
  • Reports and schedules run successfully.
  • 48 hours of production run time will determine a successful migration regardless of new issues.
  • Issues opened during the first 24 hours of production run time need to be resolved to determine a successful migration.

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.

Purging a SQL Server Express Database

by 2 Comments

This is not another IRL post, but rather is more of “Help, my SQL Express database is full!” post.

Dog that barely fits in her bed
Just barely fits

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!

Kevin3NF

Group By Starting Characters

by Leave a Comment

A litter of puppies
Image by Tom Staziker from Pixabay

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:

Results from the 2 queries

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!

Kevin3NF

How old are those stats?

by 2 Comments

Image by angel1238812 from Pixabay

SQL Server maintains a variety of stats about all sort of performance items.

  • Index usage (or missing indexes)
  • Query performance
  • Corrupt pages
  • Disk IO performance
  • Way more than I care to list here

I was trying to troubleshoot some TerriBad * tempdb write performance…almost 3000ms per write, on a server that we recently migrated to.

Our data center vendor of course said the storage was perfect and that we should try troubleshooting SQL Server.  (Duh?)

These stats are stored in the sys.dm_io_virtual_file_stats DMF, and I found this gem under the ‘sample_ms’ column description in the result set:

“The data type is int for SQL Server 2008 through SQL Server 2014 (12.x). In these versions, the value will reset to 0 after approximately 25 days of continuous database engine uptime.”

Now, this is a SQL 2017 box, but this tells me that these stats are going to reset at some point.

When I re-ran my IO stalls query…the writes for all 8 tempdb data files are at 7ms.  The sample_ms is currently 3,097,351,985 which is about 36 days if I’ve done my math right. Server uptime is approaching 90 days.

The takeaway for you, dear reader, is to know where to find the stats but also to know what timeframe they represent.

* Thanks to Brent Ozar (b|t) for this highly amusing term 🙂

Thanks for reading!

Kevin3NF

Home Blog About Privacy Policy