November 3, 2008 at 1:57 am
Hi there,
I would like to know if it is feasible to do database mirroring on a 2GB database? This is the only database we have in the company but I have created another database for test purposes on the same server and have restored backups on from the live database onto that one as well as on my own workstation which I also use for test purposes. Now I want to use the test database on the server to create database mirroring. I know that you should do this to another server but I do not have that luxury in our company otherwise I must use my workstation for that purpose. Please let me know what you think.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
November 3, 2008 at 2:16 am
Technically there is no problem as to why u cannot do Mirroring on the same database instance.
Secondly since it is your system and for test purpose u can go ahead and do it.
However on a production system the important question would be "SHOLDI do Mirroring on the same database instance" and the answer is NO.
November 3, 2008 at 4:01 am
1) Yes, It is feasible to do database mirroring on a 2 GB database.
2) If you don't have physical severs for testing, try Virtual Servers in a PC.
November 3, 2008 at 4:40 am
Technically there is no problem as to why u cannot do Mirroring on the same database instance.
Secondly since it is your system and for test purpose u can go ahead and do it.
However on a production system the important question would be "SHOLDI do Mirroring on the same database instance" and the answer is NO.
Suresh B. (11/3/2008)
1) Yes, It is feasible to do database mirroring on a 2 GB database.2) If you don't have physical severs for testing, try Virtual Servers in a PC.
Guys, thanks for the response. I will definitely have to do some research on Virtual Servers.;);). Thanks a span!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
November 3, 2008 at 5:55 am
Manie Verster (11/3/2008)
Hi there,I would like to know if it is feasible to do database mirroring on a 2GB database? This is the only database we have in the company but I have created another database for test purposes on the same server and have restored backups on from the live database onto that one as well as on my own workstation which I also use for test purposes. Now I want to use the test database on the server to create database mirroring. I know that you should do this to another server but I do not have that luxury in our company otherwise I must use my workstation for that purpose. Please let me know what you think.
yes there is no issues for the dB mirroring for 2 GB. but note that you are doing this for test database with the orginal one. remember that whatever changes you will do in your test database will also reflect in the mirrored database. this will increase the chances of data corruption. Mirroing should be the last option and not with the test database with the orginal one
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 3, 2008 at 7:51 am
Keep in mind that it's not the size of the db, it's the load, the number of transactions. Look at your log backup sizes and see if the bandwidth between the two servers supports that additional load of data.
corruption can transfer, though corruption isn't usually a problem unless hardware fails. Admittedly that's an issue, but it isn't a reason I see to avoid mirroring. You still need backups and good practices for recovery.
November 3, 2008 at 9:21 am
2GB is tiny for a database. I've mirrored a 600GB one without issues (on a LAN). If you're planning to do it over a WAN, watch out. Other thing to be sure of is that the mirrored server's logs are on a fast drive, especially with synchronous mirroring. If the log drives are slow, then the transferred log records can't be written to the log fast enough and the principal server will slow down.
krayknot (11/3/2008)
remember that whatever changes you will do in your test database will also reflect in the mirrored database. this will increase the chances of data corruption.
Corruption won't transfer. It's the log records that are transferred to the mirror server and applied. Corruption is normally (99%) cause by faulty hardware that damages a page on disk, or between SQL Server and the disk. Since the change to the page won't be logged (it didn't occur through SQL) it won't be transferred to the mirror. In fact, in 2008 having a mirror server will allow SQL to automatically fix certain types of corruption (torn pages/checksum errors)
Mirroing should be the last option and not with the test database with the orginal one
Why do you say mirroring should be the last option? What do you propose instead?
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
November 5, 2008 at 12:11 pm
GilaMonster (11/3/2008)
2GB is tiny for a database. I've mirrored a 600GB one without issues (on a LAN). If you're planning to do it over a WAN, watch out. Other thing to be sure of is that the mirrored server's logs are on a fast drive, especially with synchronous mirroring. If the log drives are slow, then the transferred log records can't be written to the log fast enough and the principal server will slow down.krayknot (11/3/2008)
remember that whatever changes you will do in your test database will also reflect in the mirrored database. this will increase the chances of data corruption.Corruption won't transfer. It's the log records that are transferred to the mirror server and applied. Corruption is normally (99%) cause by faulty hardware that damages a page on disk, or between SQL Server and the disk. Since the change to the page won't be logged (it didn't occur through SQL) it won't be transferred to the mirror. In fact, in 2008 having a mirror server will allow SQL to automatically fix certain types of corruption (torn pages/checksum errors)
Mirroing should be the last option and not with the test database with the orginal one
Why do you say mirroring should be the last option? What do you propose instead?
It is exactly because it is such a tiny database that I am wondering about the feasibility. I am not that familiar with mirroring so I am still very much in an investigating phase. The database I currently use as a test database will not be a test database anymore. I have transferred the test database to another computer that I use for developing new applications and testing. Maybe mirroring is not the best way to go. I'll explain some more what I want to do. I want to have a backup database that I want to keep synchronised with the main database in case disaster strike so I can have my users up and running before they know it. I want to automate the process so I thought to mirror the databases and keep the backup database hidden so no one can tamper with it. My backups is in order. I have maintenance plans in place to backup all databases (master, model etc) and the user database as well as the transaction logs.
Please don't laugh, I am a developer not a DBA. I think I am actually doing good as a developer doing database administration so far. Any other hints as to what I can do?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply