Simplest way to maintain a development SQL 2005 Database

Zambia

Hi

I was hoping someone has already figured this out, but here is the query.

I have two databases both on SQL Server 2005 Standard Edition

Principal Database running on SERVER A
Subscriber Database running on SERVER B

Both servers are on a network and can see each other.

I have no access to a third database for use as a witness.

I want SERVER B to be a copy of SERVER A ,this needs to happen every 24 hours.
In essence it’s a way of keeping a Development copy of a database.

Question
What is the simplest/fastest way to set this up bearing in mind that SERVER A is not load intensive and is quite small?

My initial thought was to perform the following

1: To take the nightly back up of Principal Database on SERVER A ran at 02.00 am
2: Run a restore database job at 03.00 Am on the subscriber database by connecting to Server A and using the Principal.bak file from principals nightly back up.

However my efforts are being thwarted by some network connections, via sql.

Anyone a better set up?

aidan_walsh

Replication?

Zambia

aidan_walsh wrote: »

Replication?

Yes the answer probably is in there somewhere , but its not exactly leaping of the pages.

fasty

Zambia232 wrote: »

Yes the answer probably is in there somewhere , but its not exactly leaping of the pages.

Not even the link that says "Configuring and Maintaining Replication"? There's not much to it. Read the documentation!

Zambia

fasty wrote: »

Not even the link that says "Configuring and Maintaining Replication"? There's not much to it. Read the documentation!

I think your missing the piont of this I was more looking at different peoples perspective on the quickest way to perform this.

I am not looking for anyone to paste code here

kayos

Replication is a bit of over kill if your talking about just a dev environment, but is still easy enough do.

If I remember rightly there is a nice Copy DB function in DTS (or SSIS these days). That should be easy enough for you do setup.

What network problems are you having? Is it when you try to access the a network location to backup to or restore from? To be honest its ages since I have dealt with this side of SQL admin but I do remember when I did restoring from or backuping up to Network resources could be a pain. I've done what your trying before (i.e. Backup, copy, restore) before via a simple cmd script. The exact code escapes me

Have two shares
- ServerA c:\backups as \\ServerA\Backups
- ServerB c:\backups as \\ServerB\Backups

Use OSQL/ISQL/SQLCMD to Backup ServerA Prod DB to c:\backups
Copy/XCopy the backup from \\ServerA\Backups to \\ServerB\Backups
Use OSQL/ISQL/SQLCMD to Restore the backup from c:\backups to ServerB

The one thing I would wonder about is how you will manage to get any schema changes done in Dev if you keep on overwriting them nightly .

ob

The quickest and easiest way to manage this is through replication.

amen

or log ship

Zambia

Yes well the connect issues where really with remote connections and that was fixed quick enough.

However in the end a little more complex to set up than my original plan , snapshot replication on a nightly basis takes place.

In answer to your question kayos, its more for developers using that subscription DB in case they lock it, break it or overwrite somnething on live to try out their apps. I dont want them devloping against the live. If the db has to change then I back it up and play with another one*

*(Lies.... most changes done on live)

I still intend to chase a simpler way , so any further answers are welcome.

Vorrtexx

Database Mirroring was introduced into SQL Server 2005, maybe it can help?

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

stevenmu

I don't know about snapshot replication but I know merge replication can place some restrictions on schema changes and things like that. It may not apply to snapshot replication because of how it works, but I'd still be a little wary of the extra complications of replication. To me the simplest way to do this would be an SSIS package running on the dev server, accessing the live server with a read-only account (just in case ) and doing a db copy. Quick, simple and painless.

amen

overwrite somnething on live to try out their apps

if I caught a developer doing this there would be serious repercussions.

I would suggest that you should have a dev and a test db.
Get your developers developing on Dev when the task is completed check into your source code control (including DDL, SQL etc) and then release to test.

When it works on test ship to Live

generally you shouldn't be trying things on Live

A lot of companies won't let developers mess with live!!