 | A community of more than 1,600,000 database professionals and growing |
| Featured ContentsFeatured Script |
| | The Migration Checklist 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. That makes me think I should have a good checklist that I can start from, marking this up for my specific instances. BOL doesn't give much information. I did see a nice one in a forum post on SQLServerCentral, but I'd really like a good, solid checklist. An overall list from @spaghettidba might be a good start, and I'm hoping he'll write an article on each of these, with the list for someone to check. However, I'm hoping to give him, and others, a jump start. 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: Instance Level - 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 Audits
- 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.
Database Level - 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. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
| The Voice of the DBA Podcast Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.  The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. |
|
|
|
| | ADVERTISEMENT |  | | 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. |
|
|
|
| | |  | Sadan Kumar Budde from SQLServerCentral.com The article is about Configuring TempDB Database on Local Disk in SQL Server 2012/2014 Failover Cluster to improve performance. More » |
 | Additional Articles from MSSQLTips.com John Miner shows how to move databases between a cloud development environment and an on-premise production environment using PowerShell cmdlets. More » |
 | Enrico van de Laar from SQLServerCentral Blogs In case you didn’t know this already, dbatools is an awesome collection of Powershell functions that will help you immensely... More » |
| | Koen Verbeeck from SQLServerCentral Blogs 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 » |
|
|
| | | 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 ); GO CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrderDetails ON dbo.CustomerOrderDetails ( Order_Id, Position ); GO 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; GO BEGIN TRANSACTION; GO UPDATE dbo.CustomerOrderDetails SET Price = Price * 1.10 WHERE Order_Id = 1001; GO 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 Contribution Center. |
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (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 Explanation: 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
» Discuss this question and answer on the forums |
|
|
| | | Sergiy Floka from SQLServerCentral.com The function is based on well known Tally function published on SSC. I added parameters to make it more flexible and easier to use in complicated queries. Use cases: Sequential numbers from 1 to 100, step 1: select N from dbo.TallyGenerator (1,100, null, 1) 100 sequential numbers starting from 15 , step 1: select N from dbo.TallyGenerator (15,null, 100, 1) Counting down from 100 to 1, step 11: select N from dbo.TallyGenerator (100,1, null, -11) Top 100 numbers less or equal 256: select N from dbo.TallyGenerator (256, null, 100,-1) More »
|
|
|
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums.
Please explain difference between these terms - Server authentication” vs “Windows authentication Server Roles vs Database Roles Logins vs Users RDP into SQL server BAD or not - I am just qurious if I overlook somthing, keep asking why..... I started doing some BI work recently and like to RPD...
Any way of joining data on two unique records, trying to cancel out matches - In MSSQL 2016 I am trying to find charges that cancel each other out. For example: if a group of... dtexec error - Hi, 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... T- SQL Query to fetch values -Pattern Matching - Createtable
Audit Connections to a database - Hello, I would like to audit all user connections(Login name, Hostname, IP,Program,data and time) to a particular database(say AdventureWorks) on server.Tried... PLE and BufferPool Contents - Hello. I have been looking at Memory utilisation for some time now, and am looking for a definitive way to determine what... SQL Server 2014 Patching (on Cumulatives updates) and Rolling Updates - Need advice - Hi all, As most of of you already know, MS recommends to patch CUs as the same as SPs. I have generally... Very large Indexes (~3TB) and need to rebuild fast - Hi, Hi, We have a 40 TB DB, and have some big tables and few indexes which are too big now to... SQLJOBVIS - anyone still have a copy? - This is the nifty old utility talked about in this article. http://www.sqlservercentral.com/blogs/chadmiller/2010/05/15/sql-agent-visual-job-schedule-viewer/ 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: https://msdn.microsoft.com/en-us/library/ms189292(v=sql.120).aspx Days... How to overcome the identity column "jump 1000" issue - Hey, Since SQL2012 MS changed something with the identity column; after a restart the value jumps with 1000 (for INT identity...
The Accidental DBA - Wait Stats - Hello All, Am after a little bit of advice. In my ever expanding career as a DBA with my current company, a... Mitigating STIGs - I am trying to STIG my Instance and databases and wonder if you can help. I need help figuring out...
How to manually make up the final result for this query on this dataset - Hello, I have a table defined as below: CREATE TABLE .( IDENTITY(1,1) NOT NULL, (50) NULL, (20)...
Import problem - Hello, I have 2 servers. I've created a select query to import data from server host to server destination. When... SSIS job completion - hi i have an ssis job that picks up .csv files and posts them into various tables. when i manually run the...
Cursor Insert into table with IDENTITY crisis - I have been coming at this problem from several different angles, off and on, for a few weeks now. This...
Help with % calculation please - Hello I am trying to get an output that will show me: Total number of deliveries missed / total deliveries expressed as a...
The SQL Saturday Thread - As popular as SQL Saturday is, I'm surprised that nobody created a thread dedicated to SQL Saturday, so I created... |
|
| This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
| 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.
| This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com |
|
|
