One of the things I always recommend is that when you upgrade a SQL Server instance, you perform a side by side migration to a new host instead of an in-place upgrade. My main concern is risk. While the upgrade process is fairly smooth, I still have hiccups installing SQL Server at times, and for a live server, the last thing I want to do is have to uninstall SQL Server and reinstall an old version.
Apart from the risk, I also think an upgrade is a great time to refresh hardware. If you're paying for the latest bits, I'd spend a little more for newer hardware if I can. The cost usually isn't much compared to SQL licenses, especially these days with hardware being very cheap and powerful. New hardware also gives me a staging place to test the migrations, without disturbing the existing system.
Planning the migration across hosts usually isn't too difficult, but that there can always be small issues that I need to fix after the job is done. In most cases, that's not a problem. In some, it can cause downtime (and plenty of embarassment), not to mention a loss of confidence in the DBA team. I find that I often end up building a checklist, working through the existing instance to ensure I don't forget any items, fixing my list as I run test migrations to new hardware and find issues.
What items do you need in a checklist for a SQL Server migration?
This could be just moving the existing SQL Server instance to new hardware. It could be a version upgrade, consolidation, or any other reason to move. In any case, I'll start with a general list of things to check. Let me know what I've missed:
Check new hardware/software meets requirements for SQL Server
Verify patches levels are the same (with items needed for installs)
Map paths from old to new drives, verifying space
Ensure all logins, server roles, credentials, and permissions are migrated.
Migrate all sp_configure items
Migrate startup stored procedures
Migrate all linked servers
Migrate all XE sessions
Migrate any server level cryptographic objects
Migrate all jobs and agent settings (operators, alerts)
Migrate SSIS stuff
Migrate Resource Governor data
Migrate Database Mail settings
migrate replication settings at the instance.
Backup all databases
Backup and certificates needed for TDE
Restore databases with new paths
Verify database ownership
Ensure backups are running on the new instance
As a side note, dbatools will perform much, or maybe all (still trying to determine that), of what you need. There is a Start-SqlMigration that is very impressive. While I would still want a checklist to ensure the new system works as needed, I think I'd use the PoSh tools and then add anything else I need to them.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
NEW SQL Clone - version 1 now available!
Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free.
I’ll be giving a webinar for MSSQLTips.com at March 30th. It’s a sponsored webinar, meaning there will also be a vendor... More »
Question of the Day
Today's Question (by Uwe Ricken):
You have a database running with READ COMMITTED SNAPSHOT Isolation ON! The database contains a table called [dbo].[CustomerOrderDetails] which has an unique clustered index on the attributes [Order_Id] and [Position].
CREATE TABLE dbo.CustomerOrderDetails
Order_Id INT NOT NULL,
Position TINYINT NOT NULL,
AnyOtherCol CHAR(100) NOT NULL
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrderDetails
The table has increasing order numbers from 1 to 10,000! Someone is running the following command in a SSMS window
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET Price = Price * 1.10
WHERE Order_Id = 1001;
In an application a user wants to see the order details from order 1002. What will happen in the application when it fires the following sql statement
SELECT * FROM dbo.CustomerOrderDetails WHERE Order_Id = 1002;
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth
2 points in this category: RangeS-S.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
Yesterday's Question of the Day
(by Steve Jones):
I need to purchase a new SQL Server 2016 Standard Edition server, but I don't want to waste hardware. Which of these is a poor purchasing decision for a dedicated SQL Server machine?
Answer: 6 socket 4 cores per CPU
The poor choice is the 6 socket machine. The limitations for Standard Edition are the "lesser of 4 sockets or 24 cores".
Ref: Editions and Supported Features for SQL Server 2016 - click here
When i was trying to execute the SSIS package through command prompt below:
C:\Users>dtexec /f "C:\Users\Documents\Visual Studio 2015\Projects\SSISPackage\Package.dtsx" /Set "\Package.Variables.Properties";"C:\\AzureDirectory\\Scripts\\" /Set...
SQLJOBVIS - anyone still have a copy?
- This is the nifty old utility talked about in this article.
It seems the company and the website no longer...
Create Indexes on Computed Columns
- Hi everyone
Hi have a doubt about create indexes on computed columns.
Microsoft provide in its web with comments about this subject:
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.