We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?
Using:
SQL2000
Down time max of 4 hours.
My concerns are once a failure occurs and we are running production at the offsite facility how quickly could we get back to the primary cluster?
Thanks for any input... :DThe ever popular answer: It depends.
If cost control is the primary concern, log shipping is almost always cheaper and easier. If quick failover is the primary concern, transactional replication allows you to get failover down to minutes, maybe less. Depending on how you implement things, there can be more than a factor of ten difference in price.
This is one area where I would recommend nearly anyone hire a consultant. You need to have someone with considerable experience help you make these decisions, because you've got a lot riding on the outcome and it is an area that very few people really understand.
-PatP|||i'm a big fan of logshipping in these situations, mostly because as pat said it is cheap but also because its is an automated restore and very simple.
xact repl is a very good situation but the simplest way for you to see it's downside is pretty simple. search through this forum for replication and count how many questions there are then try log shipping, i think log shipping will have fewer due to it's simplicity and if we have learned anything, it is K.I.S.S.
now to get your five nines up consider hardware clustering with log shipping.
you will have immediate uptime with parallel data at all times.|||OK, I have to bite!
How can Log Shipping be cheaper than replication? IT'S THE OPPOSITE!!! For replication the minimum requirement is SQL Standard and a connection (doesn't even need to be a network connection, it can be a dial up). For Log Shipping you HAVE TO HAVE EE ON BOTH BOXES, unless you script your own "custom log shipping". But then you can't call it "Log Shipping" because the participants do not have a knowledge of server roles as they do in EE environment.
To answer the question, I'd say if money is an issue, then write your own transaction backup/restore process that will keep the servers semi-synched with a lag equal to the scheduled frequency.
And Scott, the number of questions on replication is not indicative of it's simplicity or complexity. It demonstrates poor planning and lack of experience with the technology.|||When you are looking at DR, you have to scale the whole system (including the infrastructure) to support the largest possible load. That means buying a LOT of network "pipe" to move your data or a truly massive distributor (a separate box from the production database to avoid single point of failure). By the time you buy the third box, its licenses, and the "pipe" (which is an ongoing monthly expense) replication is usually considerably more expensive than log shipping.
The answer still boils down to "it depends" in my mind, due to the number of possible influences, many of which are non-technical in nature.
-PatP|||Now you really contradicted yourself. In the first post you said that ...log shipping is almost always cheaper and easier...while now you're saying that replication is the way to go...Is it Kerry's influence from last night debate?
Here's more food for thought in regards to non-technical aspect of the dilema (I didn't have MS pricing handy, so here's their comparison with Horacle spin):
http://www.microsoft.com/sql/evaluation/compare/pricecomparison.asp
10K (2CPU SE) vs. 40K (2CPU EE)
Which one? Decisions, decisions...|||Now you really contradicted yourself. In the first post you said that while now you're saying that replication is the way to go...Is it Kerry's influence from last night debate?Would you care to reevaluate that observation just a wee bit? Unless I missed my own point (which I suppose is possible), replication offers lots of benefits, but at (potentially) much higher cost. At least I think that I said the same thing both times.
-PatP|||i agree replicatioin has a much higher cost in man hours.
my point in the earlier post about how many questions are for repl vs logship was designed to highlight the common knowledge about repl that we all have.
It aint always the easiest of db technologies to implement. occasionally you get lucky where it sets up textbook and you will never have any issues with it. but then there is the other 90% of the time.
by the way rdjabarov , I cant believe i am agreeing with pat. i feel so john mccain right now. :eek:|||"I just love this country!" (has to be heavily accented - RUSSIAN)
If cost control is the primary concern, log shipping is almost always cheaper and easier.You guys rock! ...But please stick to the principles, don't try to make a "quick win", and we'll all be happy, I promise!!!|||I guess I forgot to "reevaluate my observation", huh?
Log Shipping will require EE on both boxes. Assuming each box has at least 2CPUs, that's $40K per box. If the current prod server is running EE already, then the number above is the minimum non-hardware investment required to implement Log Shipping.
For warm stand-by solution or DR site (which is pretty much the same thing) I am not even recommending replication because it's not what it was designed for to begin with. I think writing a couple of scripts (literally a couple) while having even MSDE edition is all that is needed.
I started arguing with everybody here because I thought that suggestions made did not target the requirement presented by RobS.We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?|||Ok here we go. More things I thought about after reading your all's posts and doing more thinking over the weekend.
What happens if Host A goes down (internet connectivity) and we have to switch to Host B (DR Site). No need to worry about how that is done right now. My question is now which would be the easiest to get Host A back in sync?
My thought, expense has not yet been budgeted so we'll go with Money is not an obstacle at the moment.
Using replciation, setup a distribution server at Host C. Use Bi-Directional Replication so when Host A goes down Host B is up to date. The Host C (Distribution Server) is queing active transactions waiting for Host A to come back online. Once Host A is back online Host C should bring it back in Sync continuing to sync Host A while Host B is being used for production. At night (Maintenance Window) everything is then switched back to Host A with minimal downtime.
During normal operations (Host A) Host B would not be doing anything so there would be no transactions coming down the pipe to Host A.
I see replication being the quickest way to get Host A back online if a failure occurs. This entire system would have to be babysat I know. Are my thoughts flawed? :D Be kind... ;)|||My $0.02 worth since I had to maintain replicated databases for over 2 1/2 years. The replicated database(s) cannot be fully functional at once if the application(s) relies (rely) on identity columns in any table, because the value if the identity column is replicated and the receiving table has to accept that value and not try to create it's own identity value.
RobS ... as you can see, there are pro and con for each method. I personally like log shipping, even if home grown (ala BORK 4.5 for SQL 7).
And of course that brings back to the never ending debate of surrogate keys vs. natural keys :eek:|||There is one aspect of replication that I do not like. When you go to change a table (add/drop a column) or add a new column, there is manual intervention involved with getting the new data added to the publication. Admittedly, this does not happen a lot in most shops, but it happens just often enough to bite you in a sensitive spot.
The other aspect that I never liked may have been fixed. Has anyone played around with replicating stored procedures and views in SQL 2000?
If you can, I would go with the Log Shipping. In order to fail back to your regular systems, you would have to set up log shipping in "reverse" for the time it takes to get the regular system back on its feet data-wise. This could take a lot of time behind the scenes with no backup server in place, depending on how large the database is.|||For DR solution there 2 proven methods that have been tested and retested for the past 5 years by many companies: Log Shipping (or a custom backup/restore), and HW-based replication, like EMC or other giants of proprietary technologies. Caveats mentioned by the previous 2 posts clearly demonstrate that replication is not a solution for DR, because it's not what it is designed for.
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
Log Shipping Error
After studying Log Shipping last week and reading up on it, it appears to be
the best solution for us. I am using to test servers to get it up and
running before releasing it to production.
I upgraded both servers from Standard to Enterprise edition. This allows me
the capabilities of using the DB Maint. Wizard to setup Log Shipping.
However, in setting it up, I am now encountering errors. I have researched
the errors on MS website but to no avail. Here is what I am doing and
getting:
1) I step through the Wizard as discussed in
http://msdn.microsoft.com/library/de...erver_8elj.asp
2) Upon finishing the wizrd I receive the error: "Unable to copy the
initialization file to \\SOURCE\data\file.bak"
Does anyone have any ideas on this? I have provided detailed steps below as
to my wizard selections.
Thanks,
Marty
Wizard:
1) Right clicked on the database on the SOURCE server.
2) Chose DB Maint. Plan
3) Clicked Next
4) Chose my DB then checked the Log Shipping option
5) Clicked Next
6) Clicked Next until the Specify the Transaction Log Share screen came up.
In this field I chose \\SOURCE\data
7) Clicked Next
8) Clicked ADD to add log shipping destination.
9) Chose the DESTINATION server.
10) Clicked OK
11) Clicked Next
12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
13) Clicked Next to the end
14) Clicked Finish
15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
I tried with different login options and nothing works. When I tried it with
PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'. Device
error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally."
have a look at kb article 811890.
You also don't need the Enterprise Edition to do log shipping. SQL Server EE
has a log shipping wizard but you can roll your own. Here is a link to an
article which talks about how to do this:
http://www.sql-server-performance.co...g_shipping.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> After studying Log Shipping last week and reading up on it, it appears to
be
> the best solution for us. I am using to test servers to get it up and
> running before releasing it to production.
> I upgraded both servers from Standard to Enterprise edition. This allows
me
> the capabilities of using the DB Maint. Wizard to setup Log Shipping.
> However, in setting it up, I am now encountering errors. I have researched
> the errors on MS website but to no avail. Here is what I am doing and
> getting:
> 1) I step through the Wizard as discussed in
>
http://msdn.microsoft.com/library/de...erver_8elj.asp
> 2) Upon finishing the wizrd I receive the error: "Unable to copy the
> initialization file to \\SOURCE\data\file.bak"
> Does anyone have any ideas on this? I have provided detailed steps below
as
> to my wizard selections.
> Thanks,
> Marty
> Wizard:
> 1) Right clicked on the database on the SOURCE server.
> 2) Chose DB Maint. Plan
> 3) Clicked Next
> 4) Chose my DB then checked the Log Shipping option
> 5) Clicked Next
> 6) Clicked Next until the Specify the Transaction Log Share screen came
up.
> In this field I chose \\SOURCE\data
> 7) Clicked Next
> 8) Clicked ADD to add log shipping destination.
> 9) Chose the DESTINATION server.
> 10) Clicked OK
> 11) Clicked Next
> 12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
> 13) Clicked Next to the end
> 14) Clicked Finish
> 15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
> I tried with different login options and nothing works. When I tried it
with
> PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
> end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'.
Device
> error or device off-line. See the SQL Server error log for more details.
> BACKUP DATABASE is terminating abnormally."
>
|||Thanks for the response Hilary...Acutally I have already read both of those
articles :-) I started my research with the one at sql-server-performance
and after researching thought that the wizard in EE would be easier.
So, I will step back and try to do it manually and see how that turns out.
Thanks again,
Marty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecNEp4KKFHA.1172@.TK2MSFTNGP12.phx.gbl...
> have a look at kb article 811890.
> You also don't need the Enterprise Edition to do log shipping. SQL Server
> EE
> has a log shipping wizard but you can roll your own. Here is a link to an
> article which talks about how to do this:
> http://www.sql-server-performance.co...g_shipping.asp
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "M.Smith" <martys@.bennyhinn.org> wrote in message
> news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> be
> me
> http://msdn.microsoft.com/library/de...erver_8elj.asp
> as
> up.
> with
> Device
>
the best solution for us. I am using to test servers to get it up and
running before releasing it to production.
I upgraded both servers from Standard to Enterprise edition. This allows me
the capabilities of using the DB Maint. Wizard to setup Log Shipping.
However, in setting it up, I am now encountering errors. I have researched
the errors on MS website but to no avail. Here is what I am doing and
getting:
1) I step through the Wizard as discussed in
http://msdn.microsoft.com/library/de...erver_8elj.asp
2) Upon finishing the wizrd I receive the error: "Unable to copy the
initialization file to \\SOURCE\data\file.bak"
Does anyone have any ideas on this? I have provided detailed steps below as
to my wizard selections.
Thanks,
Marty
Wizard:
1) Right clicked on the database on the SOURCE server.
2) Chose DB Maint. Plan
3) Clicked Next
4) Chose my DB then checked the Log Shipping option
5) Clicked Next
6) Clicked Next until the Specify the Transaction Log Share screen came up.
In this field I chose \\SOURCE\data
7) Clicked Next
8) Clicked ADD to add log shipping destination.
9) Chose the DESTINATION server.
10) Clicked OK
11) Clicked Next
12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
13) Clicked Next to the end
14) Clicked Finish
15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
I tried with different login options and nothing works. When I tried it with
PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'. Device
error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally."
have a look at kb article 811890.
You also don't need the Enterprise Edition to do log shipping. SQL Server EE
has a log shipping wizard but you can roll your own. Here is a link to an
article which talks about how to do this:
http://www.sql-server-performance.co...g_shipping.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> After studying Log Shipping last week and reading up on it, it appears to
be
> the best solution for us. I am using to test servers to get it up and
> running before releasing it to production.
> I upgraded both servers from Standard to Enterprise edition. This allows
me
> the capabilities of using the DB Maint. Wizard to setup Log Shipping.
> However, in setting it up, I am now encountering errors. I have researched
> the errors on MS website but to no avail. Here is what I am doing and
> getting:
> 1) I step through the Wizard as discussed in
>
http://msdn.microsoft.com/library/de...erver_8elj.asp
> 2) Upon finishing the wizrd I receive the error: "Unable to copy the
> initialization file to \\SOURCE\data\file.bak"
> Does anyone have any ideas on this? I have provided detailed steps below
as
> to my wizard selections.
> Thanks,
> Marty
> Wizard:
> 1) Right clicked on the database on the SOURCE server.
> 2) Chose DB Maint. Plan
> 3) Clicked Next
> 4) Chose my DB then checked the Log Shipping option
> 5) Clicked Next
> 6) Clicked Next until the Specify the Transaction Log Share screen came
up.
> In this field I chose \\SOURCE\data
> 7) Clicked Next
> 8) Clicked ADD to add log shipping destination.
> 9) Chose the DESTINATION server.
> 10) Clicked OK
> 11) Clicked Next
> 12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
> 13) Clicked Next to the end
> 14) Clicked Finish
> 15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
> I tried with different login options and nothing works. When I tried it
with
> PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
> end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'.
Device
> error or device off-line. See the SQL Server error log for more details.
> BACKUP DATABASE is terminating abnormally."
>
|||Thanks for the response Hilary...Acutally I have already read both of those
articles :-) I started my research with the one at sql-server-performance
and after researching thought that the wizard in EE would be easier.
So, I will step back and try to do it manually and see how that turns out.
Thanks again,
Marty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecNEp4KKFHA.1172@.TK2MSFTNGP12.phx.gbl...
> have a look at kb article 811890.
> You also don't need the Enterprise Edition to do log shipping. SQL Server
> EE
> has a log shipping wizard but you can roll your own. Here is a link to an
> article which talks about how to do this:
> http://www.sql-server-performance.co...g_shipping.asp
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "M.Smith" <martys@.bennyhinn.org> wrote in message
> news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> be
> me
> http://msdn.microsoft.com/library/de...erver_8elj.asp
> as
> up.
> with
> Device
>
Wednesday, March 7, 2012
log shipping and replication
Ive got my publishers and distributors on the same server say Server A . We
have implemented log shipping as Disaster Recovery solution.
Ive got my published databases log shipped to Server B.
My questions are : Do I need to log ship the distribution database as well ?
How can I recover from this scenario should Server A fail ?
I have read Books Online on Replication and Log shipping.. We cannot
implement the " sync with backup " option because of the increased latency.
It also talks about the semi-synchronous mode. I havent really followed it
for the fact that the distribution database is on the same server as the
publisher and currently we are not log shipping that.
Can someone guide me here on how to recover based upon my settings ? Using
SQL 2000
Thanks
Hassan,
In my experience when replication databases are restored onto another server
than the original, restoration is only partially successful using
KEEP_REPLICATION, and on failover it was not possible to rename the server
then start synchronizing - I found it easiest to recreate the publications
from scratch. However if you restore to Server A itself (or another "Server
A") then it should be ok.
The "sync with backup" option is to ensure that transactions don't get into
the distribution database before the publication database is backed up,
otherwise the distribution database backup could be ahead of the publication
backup. You can achieve the same effect by making sure that the restore of
the publication database and logs is older than the restore of the
distribution database - then you have essentially performed a synchronous
mode backup. In your case you will need a distribution backup, as regular as
possible.
To see the order of restores if Server A goes down, have a look in BOL at
"replication, backup and restore operations".
hth,
Paul Ibison
have implemented log shipping as Disaster Recovery solution.
Ive got my published databases log shipped to Server B.
My questions are : Do I need to log ship the distribution database as well ?
How can I recover from this scenario should Server A fail ?
I have read Books Online on Replication and Log shipping.. We cannot
implement the " sync with backup " option because of the increased latency.
It also talks about the semi-synchronous mode. I havent really followed it
for the fact that the distribution database is on the same server as the
publisher and currently we are not log shipping that.
Can someone guide me here on how to recover based upon my settings ? Using
SQL 2000
Thanks
Hassan,
In my experience when replication databases are restored onto another server
than the original, restoration is only partially successful using
KEEP_REPLICATION, and on failover it was not possible to rename the server
then start synchronizing - I found it easiest to recreate the publications
from scratch. However if you restore to Server A itself (or another "Server
A") then it should be ok.
The "sync with backup" option is to ensure that transactions don't get into
the distribution database before the publication database is backed up,
otherwise the distribution database backup could be ahead of the publication
backup. You can achieve the same effect by making sure that the restore of
the publication database and logs is older than the restore of the
distribution database - then you have essentially performed a synchronous
mode backup. In your case you will need a distribution backup, as regular as
possible.
To see the order of restores if Server A goes down, have a look in BOL at
"replication, backup and restore operations".
hth,
Paul Ibison
Subscribe to:
Comments (Atom)