September 6, 2012 at 12:37 pm
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!
September 6, 2012 at 3:50 pm
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" 😉
September 6, 2012 at 3:59 pm
Yes, the whole enviroment is virtualized, no stand alone installs 😉
September 8, 2012 at 5:47 pm
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.
September 10, 2012 at 11:02 am
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!
September 11, 2012 at 2:32 am
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
September 11, 2012 at 7:39 am
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.
September 11, 2012 at 9:35 am
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.
September 11, 2012 at 9:44 am
MysteryJimbo (9/11/2012)
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.
September 11, 2012 at 9:59 am
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.
September 11, 2012 at 10:21 am
Thanks everyone for the replys, I am looking at all these options!
In regards to recreating logins and resetting SIDs:
All the logins and service accounts are using domain accounts. I have setup the test/dev server with all the required logins and service accounts. Do I still need to look at restting SIDs? I dont have login accounts that will access any other servers or resources outside of their SQL server. (I do know the credentials created for a few jobs will need to be recreated)
September 11, 2012 at 11:26 am
wb.spiekerdoyle (9/11/2012)
Thanks everyone for the replys, I am looking at all these options!In regards to recreating logins and resetting SIDs:
All the logins and service accounts are using domain accounts. I have setup the test/dev server with all the required logins and service accounts. Do I still need to look at restting SIDs? I dont have login accounts that will access any other servers or resources outside of their SQL server. (I do know the credentials created for a few jobs will need to be recreated)
You don't have to worry about SID if you're using domain accounts and they're on the same domain.
September 11, 2012 at 12:30 pm
Great Thanks!
September 11, 2012 at 5:46 pm
MysteryJimbo (9/11/2012)
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.
I have never seen this on my production boxes. What I've seen, and on SQL 2005, was some latency issues.
But as a temporary solution to migrate big databases with minimum downtime, I think Mirroring is really good.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply