May 21, 2007 at 3:07 am
Hi,
We are looking to upgrade to SQL 2005 and install either Mirroring or Log Shipping. My preference is to go for Mirroring but I have been asked how easy it would be to switch to Log Shipping, should management so wish?
Is it possible to switch? I think so.
Are there any issues/problems to consider? Probably!!
Any comments most welcome and thank you to all who respond.
Regards
Colin
May 21, 2007 at 6:33 am
Hi,
the first question I have: What should the mirrored DB do?
If you use DB mirroring, mirrored DB is only in read-only mode. So if you want to use this system as a standby server, then DB mirroring is the most easyiest way!
If you use the logshipping with updatable subscriber - both sides can write into the database.
But there are some points you have to look for.
Nested Trigger
The old datatypes ntext, text, image are not supportet. You have to change to nvarchar(max), varchar(max) varbinary(max)
greetings
Holger
May 21, 2007 at 8:06 am
I don't think you have updatable subscribers with log shipping. That's with replication.
Both mirroring and log shipping work in similar fashions, by applying transactions to the secondary server. Mirroring allows automated failover if you have the ADO.NET 2.0 clients. Log shipping is automated failover.
Log shipping also allows a delay of restores, meaning you can move logs over immediately, say every minute, but then not restore them for 15 minutes or some other interval. That way if you have issues, like unexpected deletes, you can catch them in that interval and recover the data. In a failover, you'd need to "catch up" the unrestored logs.
May 21, 2007 at 11:36 am
hi dude,
As far as logshipping is concerned,the failover is manual and the steps to be followed if primary server is down are,
1.try 2 backup the syslogins table of the primary server.
2.if the primary server is still accessible try to backup the tail end of transactional log and restore it with recoveryoption in secondary.
3.run the sp_resolve_logins procedure in secondary.Always the syslogins table and sysusers table of the primary shud be in sync with that in the secondary.
As far as Mirroring is concerned, u can configure failover as both manual and automatic.If the mirroring is configured in high availability,the failover is automatic,in that case witness server is a must.In the latter case,the mode might be high protection or high performance.
hence based on ur importance of the db u can configure any 1 of the modes.
mirroring seems 2 be a better choice due to automatic failover rather than logshipping
Rgds
Deepak
[font="Verdana"]- Deepak[/font]
May 22, 2007 at 3:02 am
Thanks all.
I believe that auto switch over in Miirroring is only available with the Enterprise version. It is a manual process with Standard Edition, which we will be using. We simply want a standby server, it will not be used, unless the worst happens.
Am a little concerned about the unsupported data types. Could you clarify this a little. Is it only in nested Triggers, Mirrororing, log shipping etc. Seems very strange as we are simply updating a copy of the database.
One of my original queries was about the ability to switch from Mirroring to Log shiping. Is that a problem?
Thanks again.
Colin
May 22, 2007 at 6:15 am
Colin, are you saying you would want to start the mirror database as the standby database iin a log shipping set up? I guess if you were 100% certain databases were synchronised and you then removed mirroring and set up log shipping to same database it would work without having to initialise the standby with a restore, but I would want to test it out as its an unusual request. You would have to be very careful with the timing of the first transaction log backup you try to restore, the LSN would need to be spot on. I would say you would need to ensure databases syncronised, take log backup of primary. Set up log shipping, take another log backup and ensure first log applied by log shipping is that tran log backup
You can of course have mirroring and log shipping running at the same time on the same database.
Hope that helps, I have a question for you, can you do asynchronous mirroring (safety OFF) with SQL 2005 standard edition?
---------------------------------------------------------------------
May 22, 2007 at 9:22 am
George, We would set up Mirroring on its own and use the secondary server as the stand by. We would NOT set up Log Shipping. My boss has asked the question, can we turn off Mirroring and convert to Log Shipping if we want? I suppose that we could by restoring the secondary Database at the last FULL back up from the primary server and then start Log Shipping. It is a question of having either/or rather than both.
As for asynchronous mirroring, I believe that it is NOT possible with Standard Edition as you cannot tuurn SAFETY off. The followinng article was very useful in helping me understand things. http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Colin
May 24, 2007 at 4:18 am
Colin, thanks for your info.
I see no reason why you cannot switch from mirroring to log shipping, using the process you describe.
---------------------------------------------------------------------
May 25, 2007 at 9:28 am
hi all,
We have installed 3 instances of SQL server as instance 1 ,instance 2 and instance 3.All 3 instances in mystandalone PC for testing.
1.) Yes we successfully configured Mirroring b/w instance 1 and 3 with instance 1 as principal and instance 3 as Mirrored Server.
2.) Simultaneously,we had configured logshipping b/w Instance 1 and Instance 2 and as expected Instance 2 DB will be in Standby(read only) mode.
So Instance 1 will act as both the prinicipal server in mirroring and primary server in Logshipping.
3.) We created a table in Instance 1 and verified if it was available in Instance 2 and 3,yes it was available.
4.)Inorder to read the Instance 3 mirrorDB ,we need to create a snapshot of the mirrorDB.We did it and it was successfull
Rgds
Deepak
[font="Verdana"]- Deepak[/font]
August 15, 2008 at 7:20 am
Hi, little question here..
I believe that database snapshotting is only avail in Enterprise edition (correct.?)
So if we setup mirroring on a standard edition, that would mean that the standby (mirrordb) db is not available for read only purposes.??
Can someone confirm or decline this please..
Wkr,
Eddy
August 15, 2008 at 10:23 am
Yes Eddy, you will not be able to use database mirroring for reporting purpose if you dont have an enterprise edition of Sql server 2005, since database snapshot is a feature available only in Enterprise edition of Sql server 2005..
[font="Verdana"]- Deepak[/font]
August 15, 2008 at 12:51 pm
Hi Guys
You can use database snapshot on Developer edition as well 🙂
August 15, 2008 at 10:30 pm
Another thing that may or may not be a factor in your decision is the amount of transactions that the servers will have to handle. With mirroring a transaction cannot be committed until both servers are done. With log shipping the main server doesn't have to wait for the mirror to finish. If you have a LOT of traffic, mirroring might not work for you.
For that reason the company I'm currently contracting for doesn't use mirroring at all but instead uses clustering for everything (they have deep, deep pockets and lots of resources on-hand). I think the only time I've seen them use log shipping is when they upgrade production databases from one server to another during lease refreshes and need to minimize downtime.
David
August 16, 2008 at 12:35 am
hi deepak,
we will goning to create Log shipping, our primary server will be in mumbai and secondary will be in delhi as our transaction Log goes some time into GB .....(we take Log back up every 1 hr schduled) bcoz as our database size is +90 GB so shud we go for mirror or Log ship.
...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply