Database Mirroring with multiple websites

Hi, it's real strange that there are currently no questions in the whole of the SQL Server section. That has to change!

Anyway, here's a scenario. There's a website we are developing, which we expect to have a high load. the site is being developed with ASP.NET 2 & SQL Server 2005 and will probably be complete some time early next year. However, we've already started thinking about deployment issues as these will greatly affect the development.

Because it will be accessed from a few places around the world, for performance, security and resilience, the suggestion is to:

deploy the ASP.NET website on difference servers around the world (3-5) deploy at least (3-5) SQL Servers (each ASP.NET website mirror would be attached to a local sqlserver). put all the servers into the same active directory. This will enable us to run the SQL Server in Windows Authentication mode and therefore reduce possible authentication attacks

Issue #2 has given us a few headaches. The aim was for each ASP.NET to be attached to a SQL Server - for performance sake. It is also critical to keep the data in the two servers synchronised. Initially, (SQL 2k) we thought of using log shipping. However now, we are considering database mirroring to achieve this (SQL 2k5). I've been doing some research to understand the process. It seems to me now as contained in the two links below that we cannot use database mirroring and at the same time maintain a link between the local ASPNET and its local database server because if the local database server is in fact a mirror, it will be inaccessible:

http://www.databasejournal.com/features/mssql/article.php/3440511
http://www.sql-server-performance.com/sql_server_high_availability.asp

There is a mention in these articles that we could use a database snapshot to get around this problem. This would mean however, that we would be unable to write to the local database (if it is in mirror mode), which is a requirement of the application (basically, we would want any database server to be able to write).

We do not think log-shipping would satisfy the requirement either though, because it is important to have guaranteed (SAFE mode) commitment of data to the database where an update or insert is performed.

Are there any solutions that anyone can think of around this (hopefully by using SQL 2K5)?

Thanks

Oz

You are correct. If you need to keep all the servers in "update" mode neither the log shipping nor database mirroring are good solutions. You should be using merge replication for your task. There is plenty information about it already in SQL Server 2000. One possible source to start with is at http://www.databasejournal.com/features/mssql/article.php/1438231.sql