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.
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.