Replication Architecture for a Web Based Business Application


  • I’ve been tasked to create horizontal scaling for our web based business application.

    Here’s what I got for my development environment to roll into production when complete.

    1. 5 IP Addresses.
    2. 10 Medium Sized Servers, 1 Large Server, 11 Servers Total.
    3. VS 2012, SQL Server 2012, IIS 6.2, Windows Server 2012R2.

    The database and all the code is clean/indexed, we’re just at that point where we need to spread the load horizontally. We decided to keep things simple and not install (other than network) load balancing strategies and want to keep things simple and at the transaction level and our disk folks are already doing a good job.

    The application is mostly stored procedures with very little .Net C# code, basically .Net is just used for the I/O between IIS and SQL Server.  The database is heavily segmented into separate databases.  In other words there is a database for accounting, security, customers, employees, inventory, etc. or a total of about 50.  When I first saw it I got scared but the performance is great with the varying disk options per database and it’s nice not having to back-up data or databases every day that don’t need to be backed up. We don’t even use a fully qualified name, in stored procedures we just use [database].[schema].[object].  Here’s our thinking:

    1. 1 IP Address and 1 IIS Server for home page requests.
    2. 4 IP Addresses and 4 IIS Servers to serve subsequent page requests.  A routine has already been created to load balance the requests.  In other words the HTML is created dynamically and one client might be sent to URL and another client to or two different servers for the same request pointing to separate database subscribers.
    3. The 1 large server for the SQL Server publisher of all databases.
    4. 5 servers for the SQL Server subscribers.
    5. For future growth per stack 1 IP Address, 1 IIS Server, 1 Subscriber Server, a tweak to the routine that farms out URL’s, and a new subscription push or pull.

    Here’s the problem and it’s a big one.  All the stored procedures although none of them do any heavy inserts, updates, or deletes all do at least a few logging requests, changing a customer name, etc. I spent a couple hours browsing and reading but I couldn’t find a solid answer but here’s my question.  If a request comes in to IIS Server 2 that runs a stored procedure on Subscriber Server 2 how is data updated on the publisher?  I understand subscribers are for read only (good thing). 
    The only workaround would be to create a linked server back to the publisher for each subscriber and change the t-sql syntax to accommodate the linked server requirements.  Is that right?  If you’re familiar with this issue please let me know.    

    NOTE: -
    Updatable Subscriptions for Transactional Replication - "This feature
    will be removed in a future version of Microsoft SQL Server. Avoid using this
    feature in new development work
    , and plan to modify applications that
    currently use this feature.".

    • Edited by Mark Ebling Monday, October 21, 2013 5:22 AM
    Sunday, October 20, 2013 9:18 AM

All replies

  • You can use this. It is still in 2012 and will likely be there in 2014.

    If you have tight time dependencies between the downstream server and the upstream server use service broker for pretty well instant replication, but this is not really scalable and difficult to deploy and monitor unless you have a strong skill set with it.

    Transactional replication will work from the downstream servers to the upstream server, just publish the tables as part of a central subscriber model.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    Monday, October 21, 2013 11:58 AM
  • We are planning to upgrade to 2014 after it's full release and that's what prompted my question or post.  I got stuck when I read that Microsoft was deprecating updates to subscribers.  Nothing was documented as an alternative.

    I'm not sure what you're saying.  Are you saying that we should ignore the TechNet and Books Online documentation?  The note is also in 2014 documentation.  Please see the article link in my initial post.  Can you see why I'm concerned?

    Can you see if there is an official announcement from Microsoft regarding a real time replication solution.  I'm confused as to what's going on. 


    Monday, October 21, 2013 12:18 PM