April 15, 2010 at 8:47 am
I'm setting up to move all the databases from an existing SQL 2008 Standard 32-bit server to an SQL Server Enterprise 64-bit server.
So far I've:
moved copies of the read-only databases (since I don't need to worry about having up-to-date data in them)
set up DBMail with the same profiles and accounts
got a script to create all SQL Agent jobs
set up SQL Agent Operators and Proxies
copied all logins
copied all linked servers
copied DAC settings
copied Maintenance Plans
made sure DTC has the same settings
copied all server-level DDL triggers
set SSRS up, including security
I also have a checklist of these actions to take after the databases have been moved:
run the SQL Agents jobs script
schedule the Maintenance Plans
set databases with CLR up for trustworthy and with the correct owner (SID matching)
reset the server name to match the old server
make sure all SQL Agent Alerts are matched
What am I missing? Anyone have any thoughts?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 8:55 am
GSquared (4/15/2010)
I'm setting up to move all the databases from an existing SQL 2008 Standard 32-bit server to an SQL Server Enterprise 64-bit server.So far I've: ...
reset the server name to match the old server
What am I missing? Anyone have any thoughts?
I assume here that you are talking about the physical name from windows. However, you will have to make changes in sql also... not sure if you thought about that. See http://msdn.microsoft.com/en-us/library/ms174411.aspx.
You might also have to change the SSRS / SSIS config.xml files with this name. If you need more info, I'll dig into it a little more.
Edit: This will probably also require changing the connection information in all of the maintenance plans / SSIS packages.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 9:02 am
I was including the dropserver/addserver step as part of the rename step.
The current server doesn't have any SSIS packages running, so I don't have to worry about those.
I'll check the connections in the Maintenance Plans. Wouldn't have thought of that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 9:09 am
any user code in system databases (naughty)?
any user defined error messages? (held in master)
backed up and copied over your RS Key if you are copying RS databases over.
---------------------------------------------------------------------
April 15, 2010 at 10:02 am
george sibbald (4/15/2010)
any user code in system databases (naughty)?any user defined error messages? (held in master)
backed up and copied over your RS Key if you are copying RS databases over.
Good thought on user code in system databases. Found some in msdb.
No user defined error messages, but I hadn't checked.
Not sure what you mean by "RS Key". It's probably just too early in the day, but you lost me on that one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 10:04 am
Sorry, reporting services encryption key, backed up via RS configuration manager (or it was in SQL 2005). You will need that if you are restoring the reporting databases to another server as the encryption key for SSRS will not be the same on a different server
---------------------------------------------------------------------
April 15, 2010 at 10:17 am
george sibbald (4/15/2010)
Sorry, reporting services encryption key, backed up via RS configuration manager (or it was in SQL 2005). You will need that if you are restoring the reporting databases to another server as the encryption key for SSRS will not be the same on a different server
Ah yes. The encryption key has already been copied. Forgot that from my list.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 10:22 am
Gus, FWIW, I think you have an excellent list of steps to do. Can we get you to update this when you actually do move the databases to include those things that we all forgot about? This would be an excellent checklist for others to use/consider.
Don't forget that after the databases are moved you might want to run DBCC CHECKDB, rebuild all indexes and refresh all statistics. May not be that important when going from 2008 32-bit to 2008 64-bit, but should be mandatory going from any lesser version of sql to a higher version.
Did you consider 1 tempdb file / CPU?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 10:27 am
WayneS (4/15/2010)
Gus, FWIW, I think you have an excellent list of steps to do. Can we get you to update this when you actually do move the databases to include those things that we all forgot about? This would be an excellent checklist for others to use/consider.Don't forget that after the databases are moved you might want to run DBCC CHECKDB, rebuild all indexes and refresh all statistics. May not be that important when going from 2008 32-bit to 2008 64-bit, but should be mandatory going from any lesser version of sql to a higher version.
Did you consider 1 tempdb file / CPU?
I'll update it as I go along.
DBCC and stats are a good idea. Probably not necessary, but sure can't hurt.
I'll actually be reconfiguring tempdb as a separate project. That and a number of other things, like backups, DR, table partitioning, data compression, and some others, are all in the works, but are subsequent to getting the new server to be as close as possible to the old server, except for the amount of RAM it's using.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 10:48 am
Out of interest why are you renaming the server as you have no SSIS packages which are most affected by server name?
with the logins have you captured default database, language and any server roles they might have been granted.
Are you doing any database encryption?
---------------------------------------------------------------------
April 15, 2010 at 12:24 pm
I'm renaming it so that applications don't have to have any changes made to them. With the way things are set up here, that's moderately important.
It also keeps the naming convention intact.
Since renaming it is no big deal, and saves some amount of effort in other areas, I think it's best to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply