0

I have a database which has 90% of read only tables. 10% of the tables has writable data. We need to scale the ASP.NET application.We need to add more users who will not be writing to the database. We are thinking of adding another server and routing the users who need read only access to that server. Is there a way to replicate just some tables to another database server. Since the 90% of data doesnt change, we don't want to setup any full database replication. Please advise.

John
  • 1

2 Answers2

4

I would like to object to the "we don't want to setup any full database replication", as there is a pretty nifty feature in SQL Server 2012: AlwaysOn.

With this, you can set up read-only (synchronous or asynchronous) slave servers, and use the new connection broker to automatically route read requests to one or more servers. It involves some work regarding connection requests (specifically adding a READ_ONLY in the connection string so that the broker can redirect properly).

It's an extremely easy method to scale out to up to 4 SQL servers (one primary, 3 read-only).

pauska
  • 19,620
  • 5
  • 57
  • 75
  • Thanks for responding. We have SQL Server 2008. The database is 200 GB and the data which is changing is not more than 1 GB so this is the reason we dont really want to replicate the whole database. – John Jun 23 '12 at 07:27
  • Uh, single point of failure? Your idea's all well and good until something happens, and the 199GB of static data has to go completely offline because you didn't bother to replicate it, because it doesn't change. :/ – HopelessN00b Jun 23 '12 at 07:41
  • Making it clustered will increase the complexity but it is always an option. It still doesn't solve the scalability issue. – John Jun 23 '12 at 08:03
  • @John yes, it is an option until the crashing server somehow makes the database unable to start. Why you think Mirroring was added? Because clustering DOES NOT ALWAYS WORK. Database file corrupt = no failover in clustering. Plus, what is your problem replicating a tiny 1gb changes per day? That is NOTHING. – TomTom Jun 24 '12 at 17:20
0

For what reason are you trying to scale the application? Are you sure that the application needs to scale out? Are you totally sure that the indexes are properly setup so that the reads are as fast as possible? Are you simply memory constrained? Are you CPU constrained? Are you IO constrained? Usually making a single server bigger is cheaper than the SQL Server licenses needed for a second server.

Assuming that your application is fully tuned...

If your users need read access to the data, they'll need read access to all the data. If you are 100% positive that they don't need read access to all the data, you can use SQL Server replication and use filters on the articles so that you only need to replicate some of the data.

If you are looking at going to SQL Server 2012 you can look at AlwaysOn, but this is an Enterprise Edition feature so the licenses will be pretty expensive.

mrdenny
  • 27,174
  • 4
  • 41
  • 69