Solved

SQL-Server live backup

Posted on 2002-05-28
18
305 Views
Last Modified: 2012-05-04
Hi,

I have two full installed SQL2000 Enterprise Servers.
The first (SQL1) is the big machine, which takes the workload. The second one (SQL2) is a smaller computer which only should get a live backup of the big one.
It (SQL2) should also be capable to serve as the backup-machine --> SQL1 fails, Users can work with SQL2.

Problem 1:
 The database is about 8Gig large so snapshot-replication would produce too much traffic on the net.

Problem 2:
 Not only SQL1 should backup itself to SQL2! If SQL1 fails and users continue to work on SQL2, SQL1 should synchronize itself (when it's repaired and online) with SQL2.

Has anybody a solution for this problem?

Big thanks in advance

 Sascha
0
Comment
Question by:Freaky
  • 11
  • 5
  • 2
18 Comments
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7039602
We use log shipping for this.  The Concept is that SQL1 and SQL2 have to be identical, same databases and sizes, same users, same dboptions, same drive subsytem, same data and log file locations, etc . . . Then SQL1 backs it's transaction log files up to a device (a shared folder) that is shared with SQL2.  SQL2 is synchronized with SQL1 and performs a restore from the same device after SQL1 has shipped its log.  In the event of a fail over and bringing SQL1 back online, data synchronization is a simple matter of shipping the log in reverse from sql2 tp sql1.

While conceptually, this may seem complicated, Microsoft has a wizard to step you through the setup.  All the synchronization and stuff occurs as part of each server's maintenance plan.  The frequency at which the shipping occurs is completely configurable.

Here's a little more info . . .

http://www.sql-server-performance.com/sql_server_log_shipping.asp
0
 

Author Comment

by:Freaky
ID: 7039786
Sounds great .. btw: what's the difference to database replication in transaction-mode?

Greets

 Sascha
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7040222
Prolonged Latency - transactional replication will have a latency measured in seconds.  Here at work, we have alerts set for thresholds of 50 seconds or so . . . that's when your replicated server is really getting hammered and replication is falling way far behind. Over a LAN, the latency generally sits at around 3 seconds or so.  Typically, log shipping is set to update every 15 minutes.  So depending on the phase of the cycle, your maximum latency could be 15 minutes.  That's what the article is reffering to when it mentions data loss.  In the event of a crash, any transactions that have occurred since the last log ship event are lost.  That parameter is totally configurable but becomes impractical for small increments of time.  

Log shipping is an inexpensive and relatively non-intrusive way of maintaining a failover capabilty.  We considered clustering, but decided against it not because of the cost but praticality.  Our Microsoft consultant advised against it.  Apparently clustering and load balancing make great web servers but not SQL servers.  

Looking through the article I noticed the servers don't have to be identical for log shipping to work, that's the way we do it here; the only real requirement is space . . .
0
 
LVL 6

Expert Comment

by:acampoma
ID: 7040270
listening
0
 
LVL 6

Expert Comment

by:acampoma
ID: 7040271
listening
0
 

Author Comment

by:Freaky
ID: 7040524
hm .. isn't transactional replication better than log-shipping? Where's the difference? I can't find the real advance of log-shipping compared with transactional replication.
Could you please explain this a bit?

Thanks

 Sascha
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7040976
Replication itself is not, per se, a fail over scheme.  In a replicated environment, The publisher and subscriber share an on-line relationship.  Among other things, this means that if one of the servers involved becomes disabled because of a hardware issue then there won't be any writes allowed in the data involved in replication even on the server that is still online.  Even if we do manage to overcome that hurdle, if the publisher crashes and you manually fail over to the subscriber by some IP aliasing or something, a fail back to the publisher after it comes back on-line thus synching with any subsequent transactions that have occured on the subscriber is not a simple task. Also, the Off-line nature of log shipping is more transparent. By this I mean when comparing the two, transactional replication is fairly network and resource intesive. This means higher CPU utilization, network traffic, disk time.  We would also have record locks to contend with.

    While the concepts of transactional replication and log shipping are fundamentally the same,  the implenations of that concept are vastly different.  The point of transactional replication is to dump alot of the report processing off to another server, allowing the production server free for order processing , etc.  It is so well-designed for this that it is next to imposible to deploy in a fail over scheme.
0
 

Author Comment

by:Freaky
ID: 7041326
ahh - I see - I'll try to implement log-shopping .. :o)
It seems to be much better.
Anyway - isn't there a log-shipping tool which does the major work for the administrator or should I follow the complete (big) procedure in the website you've posted before? I've read that some sort of tool is included within the BackOfficeKit - however, we don't have this package. Where can I get this tool elsewhere or is it included in the SQLServer?

Thanks

 Sascha

PS: Your comments are really, really good!
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041697
I'm not sure Sascha,  I always use the maintenance plan wizard in Enterprise manager. =)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Freaky
ID: 7041756
Yes .. =)
But ........ the checkbox mentioned in the article http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_8gtl.asp isn't available in my enterprise manager (perhaps because of my german version?).

The dialog-pages appear in the following order (freely translated =) )

1.) Choose database
2.) Parameters for data-optimization
3.) Integrity-check
4.) Backup-plan
5.) (if I choose HDD as target) Harddisk-Dir for Backup
6.) Maintenance-Plan for transaction-protocol
7.) (same as 4+5 - target hdd) Hdd-dir for backup
8.) created reports saving
9.) maintenance-plan history
10.) finito

Where now should I activate LogShipping?

greets

 Sascha
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 450 total points
ID: 7041805
This describes how to set up log shipping manually using SQL server 7.0 sp2.  This should still apply to a SQL2k manual configuration only the restrictions aren't the same.  You'll still be able to use replicate you primary server, and monitor log shipping from there.  Good Luck =) . . .

http://support.microsoft.com/support/sql/content/2000papers/LogShippingFinal.asp#setup
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041818
Soory, let explain that more clearly.  Using log shipping on a 7.0 server with sp2 has the following resrictions.  1.) You can't use log shipping and replication on the same server.

2.) You can't monitor Log Shipping.

What I meant to say was that these restrictions should not apply since you would be configuring two 2k machines manually . . .
 
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041837
Thanks, here's more possibilities for implementation:

A VB soluution:
http://www.sql-server-performance.com/dt_log_shipping.asp

Also the Microsoft SQL server resource kit comes with a simple log shipping tool:
http://www.microsoft.com/mspress/books/4939.asp

0
 

Author Comment

by:Freaky
ID: 7041973
Great info! Thanx for your time.

 Sascha

PS: I'll kindly appreciate if you could send me your eMail-Adress to info@sihv.de
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041974
Here's some more implementation tools.  These are sql scrips that will set up log shipping without the GUI.  Let the download complete and read the documentation in the zip . . .


http://www.swynk.com/downloads/log_shipping.zip
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7042111
Having trouble sending an e-mail to info@sihv.de  My exchange server comes back with a "message undeliverable" so  . . . here (No one really reads these anyway, do they?)


Here's my e-mail, I've got two.  I'm at work more often than home (a typical scenario, eh?) . . . Anyway, please use work for general questions regarding SQL Server, etc . . . and home for anything else . . .

Home:
spcmnspff@bellsouth.net

Work:
seanp@libertymedical.com

Looking forward to corresponding =)



0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7042154
Yeah, if you have sp2 installed. Although there are some limitatons.  Log shipping in 7.0 was still new so there's no wizard in the 7.0 EM.  And apparently for 7.0, log shipping and replication on the same server are incompatible.  Also on 7.0 the monitoring of log shipping is not supported (although I think there are exterior ways to monitor this form vb).  See if these articles help . . .

Microsoft's inf about log shipping and 7.0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q275146

Microsoft's guide to setting it up between 7.0 and 2k:
http://support.microsoft.com/support/sql/content/2000papers/LogShippingFinal.asp#setup

A vb solution:
http://www.sql-server-performance.com/dt_log_shipping.asp

Here's a script that sets it up without the GUI. Download this file and read the documentation in the zip archive:
http://www.swynk.com/downloads/log_shipping.zip
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7042155
Sorry, please ignore that lst comment, someelse has posted a Question regarding this post . . . =)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now