April 9, 2014 at 9:28 pm
Hi Guys
We have a 5TB SQL2005 SP1 Enterprise edition database running on end of life hardware that we need to move to new hardware.
Please see brief details below:
- All system and user datafiles reside on SAN storage
- The database has various replication subscriptions
- We cannot afford more than a few hours of downtime
Our approach is the following:
- Build another server running SQL2005
- Detach the SAN and attach to the new server.
- Detach all databases from source and reattach on target
I have the following in place:
- Detach scrips on Source
- Attach scripts on target
- login script on target to create all users
- script to create all jobs on target
Our steps would be:
Stop replication distribution agents
Detach databases
Shut down source server (old server)
Start target server with same name as original server
Attach SAN to new server
Run scrips to attach all databases
Run script to create all users
Run script to create all jobs
Start replication agents
I would like to discuss:
- Any possible issues that might arise that has been experienced before?
- Is there any value in rather restoring the master database as opposed to running scripts for users & jobs?
- Provided the new server has the same DNS name and config staying in the same domain I should be able to just start the distribution agents for replication once the databases are attached ?
Any info would be greatly appreciated.
Thanks
Hen
April 10, 2014 at 12:09 am
If I understand correcly than you are just changing hardware excluding SAN storage; I guess SAN will take care of all the possible things. As your system and user datafiles on SAN you do not need to do anything with creating users and scripts, detach & attach db etc. Just you can stop the replication.
Only challanges I can see is installation files on c: drive and databaseinstance name.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 10, 2014 at 12:58 am
I would try doing it in a test environment.. replication may chuck a hissy if the hostname is different (even if DNS name is swapped over). There used to be an issue with renamed servers being unable to create new replications unless sys.servers was updated.
Keep in mind you need to maintain NTFS permissions, the replication snapshot shared folder permissions.
Is the distribution database on this server?
You'll need to restore MSDB to get replication and agent settings. Either that or rebuild replication entirely from script.
Also on a new server, your SQL users will likely be orphaned and require remapping.
April 10, 2014 at 5:42 am
If the servers have the same name, just overwrite the master & msdb database files on the new server with the files from the old server when they're both shut down. No need to reattach databases, recreate logins, jobs, linked servers etc then 🙂
If the user databases are on different file paths on the new server a couple of extra steps may be needed.
As Andrew asks, is this server also the distributor?
April 10, 2014 at 3:50 pm
hendrik.wentzel (4/9/2014)
Hi GuysWe have a 5TB SQL2005 SP1 Enterprise edition database running on end of life hardware that we need to move to new hardware.
Please see brief details below:
- All system and user datafiles reside on SAN storage
- The database has various replication subscriptions
- We cannot afford more than a few hours of downtime
Our approach is the following:
- Build another server running SQL2005
- Detach the SAN and attach to the new server.
- Detach all databases from source and reattach on target
I have the following in place:
- Detach scrips on Source
- Attach scripts on target
- login script on target to create all users
- script to create all jobs on target
Our steps would be:
Stop replication distribution agents
Detach databases
Shut down source server (old server)
Start target server with same name as original server
Attach SAN to new server
Run scrips to attach all databases
Run script to create all users
Run script to create all jobs
Start replication agents
I would like to discuss:
- Any possible issues that might arise that has been experienced before?
- Is there any value in rather restoring the master database as opposed to running scripts for users & jobs?
- Provided the new server has the same DNS name and config staying in the same domain I should be able to just start the distribution agents for replication once the databases are attached ?
Any info would be greatly appreciated.
Thanks
Hen
Replication passwords are stored encrypted within the SQL server instance, they are encrypted by the Service Master Key.
Ensure you use the same service account for the sql server service logon account otherwise you'll have issues with decrypting stored passwords.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2014 at 4:05 pm
Hi Guys
Thanks for the replies guys.
We have a distribution instance on another server for replication which sole purpose is distribution.
I will be creating a test env for this and take into consideration what was said here.
Thanks, I will report back soon
Hendrik
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply