2

We have 2 database environments a "Production" Database and a "Reporting" Database located on different physical windows server 2003 boxes. The reporting Db is derived off the Production database and contains some modified tables schema's and additional "Pre Cooked" Data tables which would be too costly to otherwise process on the fly.

What I had originally created was a database script (MS SQL) on our Reporting Server to grab the Live Production database at 1AM and restore it in our Reporting environment by running all the scripts to get it up and going such as the tables restores, and modification of the data. The problem with this approach is that I quickly began to realize a) how long this approach takes (there is no option of putting a 'modifieddate' on any production records) and b) how a junior DB developer (me) should probably not dive into this stuff yet because he's more of a programmer :).

Would it be part of the 'best practice methodology' to create a windows service which basically runs the restore at 1AM and sends the email if the process failed? I'm not sure if writing a c# component is going to allow me the level of granularity that MS SQL Scripts allow. So by using a programming language to accomplish this task - is this nuts and should this all be done in the database?

How would you recommend I run the syncing of the two environments?

Let me know what you think!

Improve this question
4
  • 3
    You don't need to write a Windows service for this, just set up a Scheduled Task, or use SQL Server's "SQL Agent" functionality.
    – Dai
    Commented Mar 15, 2013 at 19:42
  • 1
    I totally agree with @Dai. You don't have to reinvent the wheel. Instead go with SQL Server Agent or Windows Schedule Task to do the job. Plus if you create an application then you might need to adjust it for other factors in future like login, database connection string etc, which will add overhead to you or someone else.
    – JackLock
    Commented Mar 15, 2013 at 19:52
  • The Reporting Database is on a different server than the Production Database. What I did previously was Restore the production database on the reporting server (Created a network share in the mssql script). This means its very hard for the 2 servers to "talk" with each other
    – user1438150
    Commented Mar 15, 2013 at 20:03
  • If both machines are on same domain then you can still use one task combining backup and restore steps. But if both machines are not on same domain then I think it will be easy to create two separate tasks one for backup and one for restore. Backup task will create backup at some network location + send email and restore task will look for that backup file, and if it exists then it will restore database + send email
    – JackLock
    Commented Mar 15, 2013 at 21:02

1 Answer 1

Reset to default
1

For those kind of tasks, you should use the tools you are most comfortable with.

A DB would of course run a scheduled database script or use SSIS. A C# dev creates a small C# tool and a sysadmin creates a PowerShell script.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.