SQLServerCentral / Discuss Content Posted by Paul Mu / Article Discussions / Article Discussions by Author / Moving Your Database to a New Server / Latest Posts

RE: Moving Your Database to a New Server

christopher.kile — Fri, 20 Jul 2012 16:58:15 GMT

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).

RE: Moving Your Database to a New Server

ck900i — Sat, 19 Mar 2011 16:51:55 GMT

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 ServerBmy concern is that all subscribers will stay okay without the need to recreate them on ServerBhow can we do that?regards,

RE: Moving Your Database to a New Server

joe debuzna — Thu, 16 Nov 2006 17:33:00 GMT

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).

RE: Moving Your Database to a New Server

john hill — Thu, 16 Nov 2006 12:23:00 GMT

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.

RE: Moving Your Database to a New Server

eduardo Pin — Thu, 16 Nov 2006 11:53:00 GMT

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.

RE: Moving Your Database to a New Server

TF-245998 — Thu, 16 Nov 2006 10:39:00 GMT

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.

RE: Moving Your Database to a New Server

Sandy Wood — Thu, 16 Nov 2006 09:08:00 GMT

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.

RE: Moving Your Database to a New Server

Christopher P. Kile — Thu, 16 Nov 2006 09:00:00 GMT

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.

RE: Moving Your Database to a New Server

Sandy Wood — Thu, 16 Nov 2006 08:15:00 GMT

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?

RE: Moving Your Database to a New Server

SQL-DBA — Thu, 16 Nov 2006 05:56:00 GMT

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.

RE: Moving Your Database to a New Server

liebesiech — Thu, 16 Nov 2006 04:31:00 GMT

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.ThanksRene

Moving Your Database to a New Server

Paul Mu — Fri, 03 Nov 2006 09:47:00 GMT

Comments posted here are about the content posted at temp