SQL Server mirroring - how many sql mirrored servers can you have?

Question

I want to setup SQL Server mirror on amazon EC2 but I always have to add additional storage and worried when i do this my data might get erased?

1) how many principal servers and mirror servers can you have?

2) How does the sync work? is it always 1 direction? principle to mirror and never mirror to principle?

3) What if you take down the mirror server to add more storage then bring it back up will the new data from the principle be pushed onto the mirror server?

4) If you want to take down the principle server to add more storage, Can you change a mirror server into a principle server?

Answer 1

I have zero knowledge of Amazon EC2 but can answer from SQL Server perspective.

For a given principal, only one mirror is possible until SQL Server 2008 R2. This is changing in SQL Server DENALI but you have to wait for additional details.

Sync is always 1 directional except from principal to mirror with one caveat when database corruption happens on primary and SQL Server senses that the page is good on secondary, it can automatically do page restore and correct the corruption on the primary.

yes to #3 & #4 as well.

Answer 2

1. One each It isn't mirroring otherwise but some form of replication
2. Principal to mirror always. It isn't mirroring then but merge replication
3. Yes, with some Operating mode caveats
4. The actual role of a server is arbritrary

Answer 3

Some of the answers can be found here!

https://stackoverflow.com/questions/317438/mirroring-vs-log-shipping-in-sql-server-2005

Mirroring

• Database mirroring is limited to only two servers.
• Mirroring with a Witness Server allows for High Availability and automatic fail over.
• You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
• While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.
• Mirroring with SQL Server 2005 standard edition is not good for load balancing (see sentence above)

Log Shipping

• You can log ship to multiple servers.
• Log shipping is only as current as how often the job runs. If you ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
• You can leave the database in read only mode while it is being updated. Good for reporting servers.
• Good for disaster recovery