January 12, 2011 at 12:53 am
Hi, I have 1 database which the size of minimum of 60 Gb in 1 physical server and I have another empty database in another physical server. What would it be the best backup and restore from the this VLDB - 1 database of 60 Gb in size in 1 physical server to 1 empty database in another physical server ? I have try Mirroring but it requires all the table to be first have keys and the tables in the database of 60 Gb in size most of them does not have any keys at all. I'm now considering Log Shipping or Replication or the normal Backup and Restore process.
I have try Log Shipping and it looks fine but would it effects the performance of the production database server if to be run it daily office hour or suggested to run after office hour as in the evening? Would Replication good too or not? I have try the normal Backup and Restore process and my main concern is would the transaction log increase in size if I run that process?
I have another issue where after I have run either the Log Shipping or Replication or the Backup and Restore process, I need to auto-delete data from the database of 60 Gb in size. Let's say the database of 60 Gb have data dated from January 2009 till December 2010, I would like to backup and restore to the empty database in 4 separate database which 1 have data dated from 1 Jan 2009 till Mar 2009, 2 have data dated from April 2009 till Jun 2009, 3 have data dated July 2009 till September 2009 and the 4 have data dated dated October 2009 till December 2009 which will make the database of the 60 Gb to have data dated 1 January 2010 till December 2010.
Is there any best method for backup and restore VLDB and auto-delete data from the backup database?
Thank you.
January 12, 2011 at 1:15 am
First of all, 60GB is not a VLDB, it's a regular DB for today's server.
The easiest way to make this DB available on another server is a simple backup/restore.
With a 60GB db you can even plan a daily job to do it automaticaly, try the backup compression to reduce the amount of data sent over the network.
If you need your server to be synchronized more often then you can either use log shipping or mirroring.
For a method to split your data based on the date, it's a business logic problem and depend on the structure of you DB (it might not even be possible to split it), you might want to have a look at partitioning (if you are running an enterpise version of SQL) and leave everything in one DB.
January 12, 2011 at 1:42 am
wcyip (1/12/2011)
What would it be the best backup and restore from the this VLDB - 1 database of 60 Gb in size in 1 physical server to 1 empty database in another physical server ?
60GB is far from a VLDB. It's a tiny database by any standards. Databases are considered VLDB past a couple of Terabytes
I have try Mirroring but it requires all the table to be first have keys and the tables in the database of 60 Gb in size most of them does not have any keys at all.
Mirroring does not require keys. Transactional replication requires that all tables have primary keys, all mirroring requires is that the database be in full recovery model.
If you just need the database onto another server, take a backup and restore it on the other server. Simple as that.
Log shipping and database mirroring are intended as high availability solutions - to make a second copy of the database available in the case that the primary is not. Those databases are read-only or unavailable entirely while the mirroring/log shipping is in progress.
Replication (snapshot or transactional) can be used to make a copy of the DB. Snapshot makes a copy of the DB as it was at a point in time, transactional keeps the selected tables up to date, but transactional replication requires primary keys on all replicated tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2011 at 2:06 am
Thanks Gail for the help. Would the transaction logs increase in size if I decides to run the Backup process on the DB?
January 12, 2011 at 2:36 am
Depends. What's the current recovery model and what's your current log backup frequency?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2011 at 2:52 am
If I would to use Full or Simple Recovery mode, would it makes any differences? the full backup incurs during Saturday and Sunday evening, the differential backup incurs during Monday till Friday evening and the transaction backup incurs every hour daily.
January 12, 2011 at 2:58 am
Um, yes. In simple recovery you can't take log backups.
Why full and diff? That's a bit overkill for a tiny database. The full backup should finish in minutes.
Now, what's the recovery model (I assume full)?
What's the point of the second database? Offline reporting? High Availability? Something else?
How up-to-date with the production database does it have to be?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2011 at 3:12 am
Yup, it is full recovery. The second db should be in high availability as production db is running 24x7 and every minutes day and night data will be coming in. Therefore, I need to find out which is the best model that won't effects the production db in terms of backups and restore into the second db for high availability. Since log shipping and mirroring will caused the production db to be read only then I have to take that option out. Besides than backup and restore, is there any other way that don't effects the production db?
January 12, 2011 at 3:17 am
If it's for high availability, database mirroring is probably best. It won't make the production database readonly (it would be pretty worthless if it did), the mirror database will be unavailable though, as it's just a hot standby until the principal fails.
I strongly suggest you do a lot of reading up on database mirroring before you try to implement it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply