back-end SQL server 2005 databases for website

Hi,

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.

Thanks

From serverfault Datapimp23

• 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.

  Datapimp23 : 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.

  K. Brian Kelley : 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.

  BradC : 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?
  From K. Brian Kelley

• One live DB server for the website queries (inserts, updates) and another for backups, reports or stored procedures

  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)

  djangofan : i agree but there is a learning curve on that if you havent done it before.

  BradC : 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.

  SqlACID : +1 couldn't agree more.
  From BradC

• 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.

  From JohnW