We're migrating an existing IIS website + MS SQL 2005 database (on the same server) to a new test set-up. The existing set-up is too slow.

I want one ISS server and 2 X MS SQL server 2005. One live DB server for the website queries (inserts, updates) and another for backups, reports or stored procedures. So the live DB should be more aimed at performance. The other doesn't even need to be synced instantly. What is the best way in SQL server 2005 to set this up. Can somebody point me in the right direction and give me some pointers.


There are several options that come immediately to mind.

  • Snapshot replication
  • Transactional replication
  • SSIS job to ETL data
  • T-SQL through a linked server connection

How much total data? How much data is changing and how often? How soon does the changed data need to appear on the reporting system? Those are some questions to ask to determine what option is best.

  • Let's say we want the data from the live db transferred to the idle one every hour. The data itself in total is around 2 GB but the transaction log is around 25 GB. – Datapimp23 Apr 22 '10 at 19:47
  • Size of the transaction log could be large for a lot of reasons. See Paul Randal's blog posts on database recovery at sqlskills.com for more information. – K. Brian Kelley Apr 22 '10 at 21:04
  • the trans log shouldn't be that big compared to your data. What recovery mode are you in? (Full or Simple) Are you doing regular transaction log backups? – BradC Apr 22 '10 at 21:26


Ok, you'll definitely need to do regular backups on both databases. And your web database could potentially use stored procs for inserts/updates, too (depending on how you've designed your app).

Log shipping would probably be the easiest to set up and maintain (take transaction log backups on the primary DB, then restore them to the "reporting" db)

  • i agree but there is a learning curve on that if you havent done it before. – djangofan Apr 22 '10 at 21:31
  • 1
    sure, but I would argue that log shipping is FAR easier to learn than snapshot or transactional replication. Also far simpler than any custom logic to copy data. – BradC Apr 22 '10 at 21:51
  • +1 couldn't agree more. – SqlACID Apr 23 '10 at 0:52

If your front end is a single web server, I would tend to question the benefit of having two database servers.

That being said, database mirroring is another technology available to you in SQL 2005 SP1 that would solve this. It does require that your database is in the full recovery model, though. You can also use it to get some automated redundancy in the event that your primary fails.

Your Answer
