July 28, 2008 at 2:34 pm
Environment:
SQL 2005 E and Windows 2003 EE (latest SP's)
Production database is located at a colocation facility
Secondary log shipping server and QA database server are located at the corporate office
The colocation facility and corporate office have a T1 connection
QA database copy needs to be read/write (we do not need to preserve the changed data)
Database size is ~1TB
Background:
With SQL 2000 we used log shipping to refresh our QA DB server. Here are the basic steps we used: stop SQL on the secondary log shipping server, copy the MDF, start SQL on secondary log shipping server, restore the MDF on the QA database server. We did this process weekly. This process is not supported but it worked and it did not break log shipping. We upgraded to SQL 2005 and this process stopped working (log shipping would break after we stopping SQL on the secondary log shipping server).
Question:
Are there any native SQL 2005 features that will allow us to refresh our QA database server on a weekly schedule? - log shipping and mirroring possibly? If there is not a native SQL 2005 solution, can anyone recommend an economical third party solution?
Terry - System Admin (not a DBA)
July 29, 2008 at 1:15 pm
I can vouch for log shipping in 2005. It is fully supported, and it does work because I just implemented it using a primary and 2 secondaries. However, I don't think it will help you if you need read/write capabilities. The secondaries will support read only because by writing to it you defeat the original purpose.
Dennis
July 29, 2008 at 2:26 pm
If you want to overwrite, then you can schedule a job that will back up the production database, copy it to the other server, and restore it there, with overwrite. All of that can be managed through either SSIS or T-SQL and xp_cmdshell. (I recommend SSIS because of the security issues that xp_cmdshell opens up.)
That will, of course, completely overwrite your QA database. If that's what you want, go for it.
If you need to control the process a bit more, I highly recommend ApexSQL's Diff product. RedGate has Compare and Data Compare, but Apex's does both in one product, so it's more economical. http://www.apexsql.com
I use the Apex product for code and data migrations between Dev/Test/QA/Production.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 4:34 pm
Thanks Dennis. I have no doubt that log shipping is support. What is not supported is the process of: 1) stop SQL on the secondary log shipping server, 2) copy the MDF, 3) start SQL on secondary log shipping server, 4) restore the MDF on the QA database server – is not supported. This was the unsupported process we were using with SQL 2000 to refresh the QA environment.
July 29, 2008 at 4:37 pm
Thanks GSquared. Our database is approximately 1TB in size and the production and QA DB servers are on two different LAN’s (connected with a T1). Copying 1TB over a T1 is not an option. Thanks.
July 30, 2008 at 10:20 am
Did you look at the compare/diff products I suggested? Those might do what you need. Might not, but probably worth a look.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2008 at 11:00 am
I forgot to mention: We are looking at ApexSQL's Diff product. Thanks again for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply