replicated SQL with web application

[talion83]

I am researching a bit into how exactly a replicated SQL environment would work with web applications. My company has several internally developed web apps with SQL back ends and we are looking at spreading out the SQL load a bit.

So if right now I have SQL_Server which a web application is reading from - it uses a simple connection which goes directly to that server. If I replicate it out to SQL_Server01 and SQL_Server02, what methods are available so that the application can target the different SQL servers?

Would it be a matter of using something like a DNS round robin? Or would we need to hard code the other SQL Connections into the application layer?

Building a bit off of that - I am assuming if I wanted to customize which server held which parts of information, I would have to customize that in the application as well.

For example instead of saying SQL_Server01 and SQL_Server02 hold the same information instead SQL_Server01 is only replicating Database1 and SQL_Server02 only Database2. My understanding is the only way to accomplish this would be to tell the application that for 'this' information check 'here' - or put in if the query fails at one spot then to check the other. But I wanted to see if there was any other way to accomplish this goal.


Thanks.

 

[KB]

.Net doesn't really have any builtin ways to target multiple servers unless you are using failover servers, which it doesn't sound like you are.

How you target multiple servers would depend on your Data Access Layer. Assuming you are using low level SqlClient you would have to hardcode the server names and customize your connection string based on the data you are trying to get. I would not recommend you allow updates via this method. Some high traffic sites do use this method for multiple read-only servers, but have a single update source.

Using DNS round robin isn't likely to help, since its sounds like you only have one web server and multiple sql servers. Your server would fetch the ip address of one server and hold on to it for many hours before its clears that ip from its cache. This would leave the other sql server with nothign to do.

before you take this step of multiple servers I think you should make sure you are optimizing the caching capabilities of your web server and ASP.net pages. It is amazing what a little caching can do.

string server = "SQL_Server01";
//if fetching productlist change to "SQL_Server02"


       myConnectionString = "Initial Catalog=Northwind;Data Source=" + server + ";Integrated Security=SSPI;";

    SqlConnection myConnection = new SqlConnection(myConnectionString);

 

[talion83]

Sorry I did not mention that - the SQL replication would be read only for those targets, only the Provider would be writing updates.

If it were multiple web servers would the DNS method work? Or would we still be better off assigning say 1 web server to 1 SQL server?

We are playing with a number of ideas right now in how to distribute our Application load and make our users/branch offices less reliant on our Corporate office which houses the Datacenter.

So we are playing with thoughts of having each region have a replicated SQL server and IIS server so they can serve their own needs without looping back to corporate - so that if for some reason they lose connection to us they can continue on without. Another is taking that same idea even further and doing it for each office. And another is looking into the cloud - but we are still a bit hesitant with anything 'public'.

 

[talion83]

A slight update to that - while the thought right now is having a single SQL Provider we might also look at doing it in reverse where each region/office does the local writes and synchronizes back to the Corporate for data warehousing.

As I said, a lot of thoughts on the table right now - which is why I am trying to look into what we would need to do on the Application side vs what we can do on the server side.

The best case scenario is the least amount of work on the Application and allowing the SQL servers to handle most of it.

 

[beginner99]

Agree with KB. First look into caching. Actually before that determine that the database really is limiting speed and not something else (like bad code). Creating many new database connections can be quite time consuming.

Also maybe the database server could profit from more RAM (or better configuration).

Caching:

- http caching (proxy, client=browser)
- cache data (memcached or alternatives)

 

[WannaFly]

In my opinion, If you really are looking into SQL Replication - you need a DBA that knows it. And from the little you've posted here it sounds like SQL replication is a good solution for your problem. Really, with a replicated SQL server and webserver at each site - the application becomes unaware, it doesn't need to know of any other server.

You say to "spread out the SQL load a bit" - are you currently experiencing a performance problem? If so, you need to figure out why. It could be disk, network, memory, CPU, bad design, bad indexes etc. Don't just blindly assume its overloaded.

 

[talion83]

I apologize, I probably shouldn't have mentioned the line about spreading out the SQL load. We aren't having issues per se with the SQL server, that comment was more because currently we have a LOT of completely separate DB's on that server which aren't related to each other (some are web, some are for a standard application, some are for data warehousing, some are for running rules against).

So the thought is to turn that server more truly into a data warehouse in which it is still storing all of the information but that other servers are handling more of the actual 'user' work - while this server is really just targeted for backups and reports.

Wannafly - that last part is what I wanted to check on, how does the Application become unaware of where it is connecting to? Is the idea to use a connection string with a name then each location resolves that DNS address to whatever its local server is?

So if the Connection String goes to 'SQLServer' but technically the local server is 'SQLSERVER_01' we would have a DNS or Host entry indicating that SQLServer = SQLServer_01?

 

[beginner99]

I apologize, I probably shouldn't have mentioned the line about spreading out the SQL load. We aren't having issues per se with the SQL server, that comment was more because currently we have a LOT of completely separate DB's on that server which aren't related to each other (some are web, some are for a standard application, some are for data warehousing, some are for running rules against).

So the thought is to turn that server more truly into a data warehouse in which it is still storing all of the information but that other servers are handling more of the actual 'user' work - while this server is really just targeted for backups and reports.

Wannafly - that last part is what I wanted to check on, how does the Application become unaware of where it is connecting to? Is the idea to use a connection string with a name then each location resolves that DNS address to whatever its local server is?

So if the Connection String goes to 'SQLServer' but technically the local server is 'SQLSERVER_01' we would have a DNS or Host entry indicating that SQLServer = SQLServer_01?

Why would you buy more servers if the one you have can handle the workload? For better separation, virtualize them (which offers additional advantages) or maybe I just don't get what you want to achieve.

 

[talion83]

SQL is virtualized. There are a lot of things we are trying to accomplish and looking into. One part of it is segmenting our applications out. Instead of having a few SQL servers handling everything we want to begin having multiple dedicated SQL servers/instances for particular tasks. With one/several doing data warehousing.

ie: Web applications - 1 SQL, legacy applications - 1 SQL, scripts/rules - 1 sql. Then have those replicate back to one for other functions such as reporting.

We also want the regions/branches to be less dependent on our main data center. We also don't need to have a full colocation setup for each region - we just need certain specific items available to them. Such as several of the legacy systems which are only used by Corporate.

So instead of looking into setting up multiple colocations and fully replicating everything - then only having those secondary locations used IF the main datacenter goes down (which is maybe twice a year) we want to look into how we can replicate data AND still use all of those servers.

As far as the servers themselves - since purchasing servers specifically for Virtualization we now have several not-that-old servers which are turned off doing nothing - Dell 2850's and 2950's. They are more than capable of handling these workloads on them.

We are not currently having performance issues though we do expect our database sizes to increase by nearly 6 times in the next year, so we are trying to make sure that whatever environment we decide on will easily scale to handle that.

 

[WannaFly]

talion: Unless the applications are custom designed in house, there probably is no way to have it target multiple servers. You can use DNS to redirect to backup copies/sites if your main server goes down, then you'd be resolving the name to a different IP address. Essentially, clustering. But this has to be configured within SQL and can get _very_ complex.


SQL2012 AlwaysOn might be good for you. It has readable secondaries that you can use for reporting.

Kind of off topic, but: Make sure you have sound business reasoning to make the changes you are proposing, don't move databases and install new servers just because you have them sitting around. Have concrete reasoning, backed preferably by statistics and evidence that show the changes will help your core business processes. I say this because from the little you've posted, it seems like you want to separate them mostly just because there are too many databases on one machine.

 

[talion83]

The Web applications are developed in house - but we want to keep them as generic as possible. The hope is that this update could be done with the least amount of coding changes as possible.

And no, we are not looking into this just because we have extra equipment. And it isn't anything set in stone, just looking into what sort of different options/approaches we can take and what the overall cost of them is. We are also evaluating how much it would cost to reduce our bandwidth at our Corporate office but increase it for each Region (if we went with a Region approach). Plus what the options are with backups. And what would happen if equipment went offline at a location.

It isn't something being done for tomorrow, but as our company grows and changes we want to make sure that the infrastructure grows and changes in an appropriate manner. And as we do develop the websites in house and are currently transitioning them from older classic ASP to .Net this is also the time to make work flow changes (several have already been transitioned, hence my comment above about the least amount of recoding).

 

[beginner99]

I'm not an expert so take anything I say with that in mind but I have the feeling you are making up problems that don't really exist. I agree with WannaFly.

As long as you don't have any latency issues or transferring very huge amounts of data I see no reason the de-centralize. It would make the system and maintenance (like back-up) a lot more complex.

 

[KentState]

Federated databases is what you are talking about, not replication. Like WannaFly stated, 2012 has AlwaysOn which can be used to give multiple read targets for reporting or backups. SQL Server was not and is still not a distributed database so this functionality is severely lacking. Something like Oracles Grid is what you are after.

With federated databases, you build vies on top of linked servers that break up the data. MS has a couple articles that explain their implementation. http://msdn.microsoft.com/en-us/library/ms190381(v=sql.105).aspx