Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using High Availability to move to a new server? Expand / Collapse
Author
Message
Posted Thursday, September 6, 2012 12:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 42, Visits: 191
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!

Thanks in advance!
Post #1355562
Posted Thursday, September 6, 2012 3:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,358, Visits: 13,688
Just out of interet why is hyperv installed, are you hosting VMs on this box too?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1355648
Posted Thursday, September 6, 2012 3:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 42, Visits: 191
Yes, the whole enviroment is virtualized, no stand alone installs ;)
Post #1355650
Posted Saturday, September 8, 2012 5:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:15 AM
Points: 384, Visits: 1,270
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.
Post #1356423
Posted Monday, September 10, 2012 11:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 42, Visits: 191
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.

Mirroring is working very well!
Post #1356896
Posted Tuesday, September 11, 2012 2:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 3:03 AM
Points: 466, Visits: 1,920
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.

Thanks
Chandan
Post #1357192
Posted Tuesday, September 11, 2012 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.

Thanks
Chandan


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.
Post #1357403
Posted Tuesday, September 11, 2012 9:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:15 AM
Points: 384, Visits: 1,270
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.

Thanks
Chandan


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.
Post #1357525
Posted Tuesday, September 11, 2012 9:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:15 AM
Points: 384, Visits: 1,270
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.
Post #1357536
Posted Tuesday, September 11, 2012 9:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
Mirroring has an processing overhead on the principle server which can, and does, have a performance impact on high TPS servers. I've seen it and discounted this technology for this reason.

Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.


http://msdn.microsoft.com/en-us/library/ms189852.aspx

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.
Post #1357552
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse