|
-
log shipping vs replication
As a finger in the air generalisation would log shipping or replication provide the least impact on a network?
We are moving some servers from our local LAN to a remote location on the other side of an MPLS cloud (anyone understand those network guys?!) and the replication that is currently running will need to move or be replaced. Obviously this traffic is going to have to fit in amongst the general office to internet traffic now where previously it was only on the LAN so we want to reduce the traffic as much as possible.
Many thanks for any thoughts that you might have ...
-
There are pros and cons on both
a. Feature
b. Flexibility
c. Speed
Some cons....
snapshot sucks in replication. You can do customized replication for snapshot using backup , ZIP , COPY and Unzip, restore.
Full backup synchonization sucks in log shipping. Can be customized using backup , ZIP , COPY and Unzip, restore.
How big is your database and how many transactions you are expectin and how fast you like the primary and the DR be synchronized.
-
figured it wouldnt be a clear yes or no decision!
The database is about 8gb although it varies depending on the calendar, at the end of a quarter it could be up to 11gb. transactions per day/hour/minute are as yet unknown but in the '000s certainly. we currently have merge replication, although the subscriber has no updates, running every 15 minutes and the processes take only a few seconds to sort themselves out.
when you are mentioning backup/zip/copy/unzip/restore are you using an automated process? if so what zip product do you use, presumably it is one that takes command line prompts?
-
yes. SQL Server job. I create customized log shipping to include zip and unzip.
I use Winzip 9.0 with command line addon.
I used sqlzip too before.
-
MAK , when you say you have customised log shipping, do you mean actual log shipping with some tweaks or are you referring to something that you have created to mimic the process that logs shipping carries out?
can you illustrate a step by step approach to this? Is the whole automation from the sender or do you have a job on the receiver that unpacks and restores the database?
Last edited by fatherjack; 06-01-2005 at 02:56 AM.
-
More details:
we are seeing transfers of about 1000 changes every 10 minutes although there is a mass update job that runs overnight via SQL Agent that causes about 40000 changes.
hope this is the info your were asking about ...
-
Mimiced the log shipping job.
Example:
Two servers : PROD1 and DR1
a. PROD1 takes full backup and zip it. Daily.
b. PROD1 takes transaction log backup and zip it - Daily every 15 minutes
c. DR1 server copies the full backup zip from PROD1 server to DR1 server and unzip it. Daily.
d. DR1 server restores the full backup in stanby mode.
e. DR1 server copies the tranlog backup zip files from PROD1 server and unzip it. every 15 minutes
f. DR1 server restores all available Tranlog files in standby mode.
Most of the load is on DR1 server, since it is idle.
-
Hi Mak,
Point C - How about when the full backup is 35+GB? wouldnt this be hammering the the network?
I've ran into this problem in my previous employment. Before I introduced transactional replication, we would cross our fingers and hoped that the copy would be completed successfully and unattended.
-
Yes it would be. How much so depends heavily on the size of the pipes the data is going through at both ends.
Fortunately, our off-site place is only about an hour's drive away and when we restore backups of our biggest db to our dev or test servers, we take a USB hard drive down there, copy the files to it, and physically bring it back to the office.
We don't do this daily, obviously.
Personally, I'm a fan of replication.
-
Our network is quite quiet out of hours so a full copy would be possible and the zipped log backups shouldnt really notice through the day so i think i will be trying to get something set up along these lines.
Our DR site is going to be 300 miles away in London so there is no chance of 'popping down the road' so i guess i need to learn to type with my fingers crossed!
fortunately we have the DR server on site currently so i can get it set up before it moves later this month.
If all else fails i will leave replication in place and see how the network copes. My main concern is the network load, even though i can set the replication profile to treat the connection as low bandwidth.
I am concious though that replication shouldnt be used as a part of a disaster recovery plan
-
Compression % for a SQL Server backup file is usually above 80%.
I do log shipping for 66 GB database backup file from NJ to LA.
You can continue using replication too. Bu remember when you do your first snapshot it will take for ever. You have to customize the replication using one time backup,zip and unzip,restore for snapshot.
-
MAK,
i have the winzip 9 installed and the command line add on but cvannot get it to run via a SQL job. I and just creating a job and entering the syntax to run as a step type of operating system command (CmdExec).
every run errors with
The system cannot find the file specified. The step failed.
i have tried wrapping the unc's in quotes
wzzip "C:\Program Files\microsoft sql server\mssql\backup\northwind\NWindZip.zip" "C:\program files\microsoft SQL Server\MSSQL\backup\northwind\*.trn"
and just plain with 8.3 naming
wzzip C:\NWindZip.zip C:\progra~1\mi6841~1\MSSQL\backup\northw~1\*.trn
have set path for wzzip to run.
have pasted above into command prompt and the file is created
whats going wrong?  can you post any sample code please?
-
Tried specify path for wzzip in your command?
-
doesnt the "set path ... " command let you call the exe by name from any directory/folder?
DOS - people have told me about that, its what they used in the olden days isnt it !!!!!
sadly lacking in command line skills i am affraid
-
OK, we are there - almost.
I have server 1 creating the TRN and BAK files and zipping them.
I have server 2 copying them to itself and unzipping them and restoring them.
what i dont have is any archive of the TRN files 'shipped' to server 2. If i ever have to pick up this process where the TRN files have not been restored i only have the most recent on the server. How can i have server 1 name them with a date but still have the restore on server 2 rstore the file, given that i dont know the file name at run time?
so close....
TIA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|
Reply With Quote
