November 16, 2006 at 4:31 am
Very well described! What I specially like is that this article covers also the approach of such an exercise and how to minimize the impact on the business which has high demands regarding availability. Once again very helpful and complete for my purposes.
Thanks
Rene
November 16, 2006 at 5:56 am
Watch out for default collations on the new server. That one got me recently b/c I like to use accent insensitive (AI) and the SQL 2000 default is AS.
November 16, 2006 at 8:15 am
Great, detailed article especially helpful for a new DBA like myself. I've done a few database moves and I'm ashamed to admit that I've taken another way to accomplish the same task. I've detached and reattached the databases and they seem to work fine on the new servers. Any downsides to just doing it this way?
November 16, 2006 at 9:00 am
Your primary downside to detach/attach-to-new scenarios is that the new server might not be completely backward compatible. This has been the case in the past with some mighty well-known database offerings; on the plus side, it's usually well-documented in their release notes.
If you usually give up a great deal of time in stepping down from these scenarios to data transfer scenarios (backup/restore,bulk copy, etc.), detach/attach is a good candidate. I usually prefer to use a backup/restore, which insures, first, that I HAVE a backup and allows for the DBMS to reformat my database as it is restored, which feels to me to be more likely to work than a database either working perfectly in its new home or reformatting itself (if such is required) on attachment to a database server hosting a new version of the DBMS.
November 16, 2006 at 9:08 am
Great points. We've been doing a bit of 2000 to 2005 moving and we never took the compatibilitly issues as serious as we should. We've always had the attitude that "Well the database is a Microsoft database and they said it would work in 2005......."
I like the backup/restore model you mention. It does seem to offer a better method for sucess.
Thanks again for the help.
November 16, 2006 at 10:39 am
I agree with SAM, sort order/collation should be a major consideration, if you're in anything but a tiny shop - we have 5 different flavors here. Putting a user database on a server having system databases of different sort/collation can give you plenty of problems.
November 16, 2006 at 11:53 am
This is one way to perform a server change, but using differential backups the system downtime may be long.
I Belive that using transactional log backups this operation is faster than using diferential backups.
November 16, 2006 at 12:23 pm
The last time I had to move a heavily used database to a new server as part of an upgrade to SQL 2005, I took a full backup, restored it on the new server, later took a differential backup when I was getting close to migration and then copied tlogs over to the new server when I was ready to migrate.
Downtime was minimal while backed up, copied, and restored the last tlog... Good article.
November 16, 2006 at 5:33 pm
Good article especially considering that there's a definite lack of concise, to the point articles out there on SQL Server migrations.
It may be good to note also that GoldenGate Software just graduated from SQL Server trigger-based replication to replicating right off the transaction logs. DataMirror has something as well. And Quest Software could very well be coming out with something too.
Another consideration from the "Understanding What is Involved" section may also be whether the OS environment is changing (i.e. going from 32-bit to 64-bit to get that extra RAM we're always starved for).
March 19, 2011 at 4:51 pm
Hello,
if my database has a merge publications with web sync. and has around 85 subscribers. i want to move it from ServerA to ServerB.
ServerA will stay online since we have other databases and publications on it, but to decrease the load we will move this database to ServerB
my concern is that all subscribers will stay okay without the need to recreate them on ServerB
how can we do that?
regards,
July 20, 2012 at 4:58 pm
Oddly enough, I found your question while searching for my name on Google. I suspect we have the same name (and possibly the same middle initial).
Personally, I would generate scripts for the subscriptions and verify that no references to serverA appear in the scripts. Following my own advice, I find that the server name for my subscriber (transactional in this case) DOES in fact appear, but only in comments and job names. Try generating the script for your merge subscription (it was easily done to a new query window in SSIS 2008).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply