Our application creates a new sql database for every customer and we will be continually adding new customers and creating new databases every day.
These databases can range in size from 3 megabytes to over 1 terabyte.
We are using AWS to host our SQL servers and to start will have one sql server on the east coast and then one on the west coast.
We can't use database mirroring as we will have too many databases. And database mirroring will use up all the threads
We don't want to do a SQL Failover Cluster because we don't have a SAN on AWS.
We can't use s3 for storing the sql files as its not a file system the the sql server interacts with.
We could do iSCSI targets using ebs volumes but that creates a single point of failuer with the ebs volumes.
My thought is setting up TSQL replication using a publisher/distributor/subscriber model will allow us to flip over to our server on the west coast if the east coast one goes down.
As each new database is created we will have to add it dynamically to the subscribers. We can live with a 5-15 minute loss of data.
Is this a good idea? Is there a better way of doing it and hosting it on AWS?
We don't want to purchase or manage any hardware.
Thanks for any input!
