We have an existing windows 2003 server with sql 2008 r2 enterprise. Its part of a Hyper-V configuration.

A new windows 2008 server was purchased. I need to move all databases, jobs and user accounts to the new server. We are staying with the same sql 2008 version. (The new server will also use Hyper-V)

I do not want to manually detach, copy/restore & attach each and very database and job to the new server. Additionally, I would like to keep the old production system online and roll over in a quick manner.

I did some reading and would like to hear from you experts which High Availability method would be good to move to the new system. (I have setup mirroring, log shipping and fail over clustering in lab enviroments in the past)

Additionally, I have another question (it may be out of context for this forum, but its very related to my first question). I have a old widows server 2008 with sharepoint 2010, and sql 2008, this whole setup is also being moved to the new windows 2008 server. With all the info on the web, any pointers where to look will be very appreciated!

I do not believe it is possible for you to move the server objects (SSIS packages, customized jobs, etc) but you may re-create those (prior migration) and test. Actually, I am assuming you will test the migration anyway.

But yes, the databases can be migrated quickly using Mirroring.

I did it recently with several big databases and found no issues and it was to move from SQL2005 to SQL2008. The key is, ensure you have the same users on the other servers and you already reset the SSID. Once done, it takes a few seconds.

sql-lover (9/8/2012)I do not believe it is possible for you to move the server objects (SSIS packages, customized jobs, etc) but you may re-create those (prior migration) and test. Actually, I am assuming you will test the migration anyway.

But yes, the databases can be migrated quickly using Mirroring.

I did it recently with several big databases and found no issues and it was to move from SQL2005 to SQL2008. The key is, ensure you have the same users on the other servers and you already reset the SSID. Once done, it takes a few seconds.

Thanks for the responce.

After some more research and testing I will be using mirroring. Clustering is out of the question, due to the servers being ver 2003 and 2008.

chandan_jha18 (9/11/2012)Mirroring solution will work fine to minimize the downtime but please make sure that you have exact versions of sql server with exact level of service pack.

ThanksChandan

This is why logshipping is a better method of migrating/update database server as there are no restrictions. Logshipping is also guaranteed not to have an impact on the production server where as mirroring potentially can in a high TPS environment.

chandan_jha18 (9/11/2012)Mirroring solution will work fine to minimize the downtime but please make sure that you have exact versions of sql server with exact level of service pack.

ThanksChandan

Good point.

But I would like to add something.

As a DB migration strategy, like moving from old to new MS-SQL flavor, such requirement is not needed. The source can be a lower MS-SQL version. The caveat though, is that moving back won't be possible. But that's the whole idea of Mirroring is if we are using it as a migration strategy to a new environment with a most recent MS-SQL version on it.

As a migration strategy or tool, I honestly prefer (big databases) Mirroring instead of Tlog shipping. To me, Mirroring is easier to configure, assuming all things are in place, like security and network bandwidth.

MysteryJimbo (9/11/2012)[quote]This is why logshipping is a better method of migrating/update database server as there are no restrictions. Logshipping is also guaranteed not to have an impact on the production server where as mirroring potentially can in a high TPS environment.

Sorry, I may not be following you correctly. Do you mind elaborating your statement a bit more?

I do not see how Mirroring can affect during a migration. Assuming the login(s) were created already and SIDs reset on target, it may take just seconds to move the database. I actually done this myself several times with databases between 500GB and 1TB of size.

Both requires good bandwidth, for sure. On both, we need to re-create login(s) and reset SIDs, target instance. But after the Mirror failover, the db is up and running. Using Tlog shipping additional steps are required.

I see myself Mirroring as a Tlog shipping with steroids. And Tlog shipping more data recover oriented, like on a different data center as warm standby.

Virtually all environments can support and transaction log backup plan which can generally be compressed prior (native or 3rd party) to transporting to a subscriber. Generally they almost always do this anyway. Obviously compressing a log backup has an overhead but this is much smaller than a continual stream to a network connection

I'm not saying mirroring doesn't work. But you are introducing new technology which isn't required and isn't tested on production. This is especially redundant if transaction log backups are already in place.