August 17, 2007 at 5:31 am
Hi,
I have a live SQL Server 2005 instance running on Win2K3 Enterprise Addition and I am wanting to move all the data in these databases to a warm standby on a regular basis (I cannot loose more than 15 minutes worth of data). The live system has a massive amount of transactions occuring every minute and the warm standby server is located over 100 miles away, and I need to be able to access/backup the databases on the warm standby on an adhoc basis.
Therefore I have ruled out the following:
# Scheduled full and transaction log backup, copy, restore.
# Mirroring, as I would have to use "High Performance" and this would leave the databases on the warm standby in a non recovered state, and I need to be able to access/backup the databases on the warm standby.
# Transactional replication, due to the massive volume of transactions and there are loads of tables that don't have primary keys.
This leaves me with the log shipping solution.
I am considering using the following settings for the Primary Database:
# Transaction log backups every 5 minutes
# Network Path to the backup folder which is located on the warm standby server
# Delete files older than 48hrs
# Alert if no backup occurs within 10 minutes
I am considering using the following settings for the Secondary (warm standby) Database:
# Use the log shipping wizard to backup the primary database and automatically restore it into the secondary database
# Using a sub folder within the "Network Path to the backup folder which is located on the warm standby server" which I set in the Primary Database settings
# Delete files older than 48hrs
# Schedule the copy for every 5 minutes
# Put the Secondary Database in a "standby mode" (so I can quickly execute certain sql scripts against it)
# Select "Disconnect users in the database when restoring backups"
# 0 minutes delay on the restoring of the backups
# Alert if no restore occurs within 20 minutes
# Schedule the restore for every 5 minutes
I also have a few questions:
1. As I'm scheduling the backup/copy/restore for every 5 minutes will SQL Server be aware if the backup on the Primary database takes slightly longer - will it adjust the copy/restore accordingly (basically, I won't loose any data will I)?
2. What happens if I don't select "Disconnect users in the database when restoring backups"?
3. I'm testing this on my local PC at the moment with two instances of SQL Server 2005 and I have noticed that my "backup" folder contains a full .BAK of the database (I assume this is the initial backup/copy/restore of the database) and a transaction log backup .TRN file for the database every 5 minutes. The "Copy" folder also also contains the same transaction log backup .TRN file for the database every 5 minutes, so I'm basically holding two copies of the same file. Is this because I should backup the .TRN file on the Primary database server, and then have the "Copy" folder on the Secondary database server?
4. Should I still keep the existing backup procedure for the database which is a full database backup once a day at 5:00am and a transaction log backup every 15 minutes, or should I use the warm standby as my backup?
5. I may still need to restore the Primary database to a given point in time, but nothing greater than 48hrs. Can I use the initial .BAK file, and then the .TRN files to roll the logs forward to a given point in time?
6. Should I use a "Monitor Service Instance", and can this be the warm standby? How do I access the stats from this monitor service?
Any help with the questions would be greatly appreciated, and please be as critical as possible with my proposed log shipping solution.
Thanks in advance.
www.sqlAssociates.co.uk
August 17, 2007 at 6:05 am
Hi Chris,
let me explain each of your questions as far as I could............
1. It is not a problem if the backup sometimes takes more than 5 mins........if the backup is not completed within 5mins and if the copy job starts it cannot copy the file coz it would be in use by the backup process as a result the job would fail for some time unless the backup completes........once backup is completed it will successfull again......no problems with this sterp.....
2. If you do not select "discconnect users option" if there were users accessing the db @ the time of restoring the backup file/tran log file it will definitely fail.......ALWAYS while restoring there should not be any users accessing the database.....hence you should check this option while configuring log shipping..........
3. Since you have configurred Log shipping with 2 instances in the same server, it will possess duplicate copies.......in real time scenario you will have, the full backup and tran log backup in a shared folder in the source server and the copy job present in destination server will copy these to destination servers folder and restore it from there......
4. No need to take full backup when you have configurred log shipping. In case if the log shipping fails due to some LSN mismatch @ that time youcan take a full backup in primary and restore it in secondary server and reconfigure log shipping........
5. Yes you can make use of the initial backup but if you need to roll forward all the T-Logs thats quite tedious instead you can take a weekly full backup on the source server and daily differential backup........
6. Its always advisable that you use a different server as a monitor server apart from primary and secondary servers else you can also use the secondary server as monitor server. It will provide vital information such as which was the last T-log copied and which T-Log was restoresd recently.....these info will help you to troubleshoot any failures in log shipping
REFER this link,
http://sql-articles.com/articles/lship/lship.htm
Finally I would like to advise you if you could go for database mirroring with High Availability mode as it would provide AUTOMATIC failover while compared to log shippings MANUAL failover.............
Feel free to ask any questions if you have..........
Rgds
Deepak
[font="Verdana"]- Deepak[/font]
August 18, 2007 at 5:59 am
what you should always consider is that log shipping, transactional replication and mirroring do not provide the same thing. You have to consider the reasons you want to have a standly solution and what it should cover you for/from.
For instance mirroring and replication don't cover you for malicious or accidental data loss. Transactional replication needs the tartget server to be licensed, the others don't unless you want to use them for reporting.
I think microsoft may disagree with your statement of replication not keeping up, although your setup does not lend its self to replication. If your log backups take too long to backup or restore then get a faster disk sub system - I hope you're not using raid 5 ? Your network bandwidth may be a limiting issue but I've used 3 min log bakups at distances of over 100 miles, but if you're using a 256kb link then it's not very pratical, however i'd not expect mirroring or replication to do quite so well.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 20, 2007 at 5:26 am
Hi
Just one thing... I hope you have a very good connection (internet\intranet\other) between you primary and secondary servers...
"Keep Trying"
August 20, 2007 at 5:31 am
Hi,
How good would the connection need to be? There are 50 databases with a total size of 140GB?
Thanks.
www.sqlAssociates.co.uk
August 20, 2007 at 6:40 am
well you just need a dedicated pipe, pipes run at similar speed to your network, therfore a 10mb pipe will run at 10% of 100mb ethernet or 100th of a gigabit. Depends on how much you can afford, but 140gb is pretty small, and once you've done your initial whatever, it should only be changes you need to move up the pipe. If you want to run your business from the remote site then your pipe would need to be fast ( and very expensive )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply