Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
Have you thought about migrating them to sql azure instead? –  Jim B May 18 '11 at 19:13
    
@Jim B SQL Azure has a size limit on databases of 50gb and it is much cheaper to do the SQL Standard Database on AWS then to use sql azure. –  ChickenFur May 18 '11 at 19:45
    
@Chickenfur while the database size is 50 Gb remember that you can partition the database across instances, and store blobs in azure storage so unless you have a 50GB table, there really is no size limit per se. I don't know about the pricing (seems to me that it should be cheaper, but I can't say that I understnad either pricing very well) also remember that you get access to the CDN –  Jim B May 18 '11 at 21:47
    
@JimB --- Hey thanks for the input, thats a good idea. And we can still do full text indexing using Lucene. code.msdn.microsoft.com/AzureDirectory I will check it out. –  ChickenFur May 19 '11 at 14:53
    
@JimB -- SQL Azure is a bit more expensive. <br>$99.99 per database up to 10GB per month <br>$199.98 per database up to 20GB per month <br>$299.97 per database up to 30GB per month <br>$399.96 per database up to 40GB per month <br>$499.95 per database up to 50GB per month –  ChickenFur May 19 '11 at 15:07

1 Answer 1

Have you looked at the multisite clustering features that come with the latest version of Windows Server?

These are shared nothing clusters. You don't need shared disk/SAN storage, but you need file replication software that syncs bits between the sites.

There is a detailed how-to here. I think that the article is worth a read and that it may be worth setting up a test. I'm not sure if this is supported under Amazon's EC2 scheme, which I don't know much about. Running driver-level replication software on a VM seems scary, but I've never tried it.

share|improve this answer
    
We actually just decided to change our product to use MYSQL and went with amazon RDS. Seems to be working great and its alot cheaper. –  ChickenFur Jul 11 '11 at 21:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.