Connection strings – boring topic, right? Well, it turns out there’s a few things that can save your behind.
Tip 1: Build your application with 3 connection strings.
If you want to scale, write your application from the start with 3 different connection strings:
- Writes and real-time reads
- Reads that can tolerate data older than 15 seconds (no writes allowed with this connection string)
- Reads that can tolerate data several hours old (like reports, and no writes allowed with this string)
In the beginning, all three connection strings will have the same content – they’ll all point to your production database server. When you need to scale, though, the production DBA can use different techniques to scale out each of those tiers.
1. Writes and real-time reads – this is pretty tough for DBAs to scale, so we need to keep the number of queries here to a minimum. Our only option right now for writing in two SQL Servers at once for the same database is to use replication. I like replication in theory, but it isn’t a set-it-and-forget-it option when you’re trying to scale more load than one SQL Server can handle, plus do active development. I only recommend this option for clients with full time DBA teams that are on call around the clock. Fortunately, you probably won’t have to pursue this option because most of your load will be using the other two connection strings anyway.
2. Reads that can tolerate data older than 15 seconds – here we start to have more options to scale out. When we don’t have to worry about merging data from multiple sources, we can use easier-to-manage tools like transactional replication or SQL Server 2012’s Availability Groups. This is where the bulk of your queries should go – but I mean bulk in terms of execution count, not in terms of load generated. Think of this as the default connection string for your application.
3. Reads that can tolerate data several hours old – users love reports. As your application grows, you won’t have time to make all of the report queries clean. Users will build really crazy reports with nasty, ugly queries that burn up all kinds of CPU and IO. There will come a point when management will have to make a decision: do they allow reports to slow down production in order to get near-real-time data, or do they shove these ugly queries over to a more delayed data source? Let this be a political decision, not a technical decision: if you’ve got a separate connection string already for queries that can be hours old, then we’ve got all kinds of ways to scale those reads out. My favorite technique for this is log shipping because I can log ship to an unlimited number of read-only database servers. I do have to kick running queries out when I restore, but we’ve got tricks for that too.
To be really successful, dev managers need to police the use of these 3 connection strings – even long before we implement different SQL Servers:
- Writes and real-time reads – all code that uses this connection string must be signed off by a senior developer and a DBA. Must go through change control.
- Reads older than 15 seconds – all queries must be peer-reviewed.
- Reads several hours old – free-for-all. Anybody can write craptastic queries here, like with off-the-shelf reporting tools.
Treat that #1 connection string like a valuable resource, and your database apps will scale like crazy.
Tip 2: Get connection string examples at ConnectionStrings.com.
Much like DownForEveryoneOrJustMe.com, this is one of those simple sites that just does what it says on the label. Scroll down the page on the SQL Server 2008 examples and you might be stunned at just how many cool things you can do, including my next favorite tip.
Tip 3: Set up a backup server with the Failover Partner parameter.
SQL Server Database Mirroring lets us set up a primary server and a secondary server. All logged transactions are sent to the secondary server, either synchronously or asynchronously. If we’re really serious about availability, we can even set up a witness server that will automatically fail over the database to the secondary server. Your applications don’t have to change a thing – as long as you specify Failover_Partner in the connection string like this:
Data Source=ProductionSQLServerName;Failover Partner=ProductionSQLMirrorName;Initial Catalog=myDataBase;Integrated Security=True;
When your application tries to connect to the ProductionSQLServerName and times out, it’ll automatically try the failover partner. If it’s up, it’ll keep right on working as normal. You don’t have to get up in the middle of the night to change anything.
Here’s the cool part: you don’t need to use database mirroring to use Failover Partner. Whether you’re using database mirroring, replication, log shipping, or duct tape, much like the honey badger, your applications don’t care. They’ll just try to connect to the Failover Partner name whenever the primary server is down.
If you wanna get really fancy, you can even move the failover partner around from server to server using aliases. Aliases are SQL Server’s equivalent of a hosts file: your client machines check the registry first to see if a SQL Server name is actually an alias that points somewhere else. Normally aliases are set up using SQL Server Configuration Manager, but they’re just registry settings, and that means you can roll aliases out via Active Directory Group Policy. (No, I don’t expect you to know how to do that, but it’s just another tool in the arsenal of big companies that need to scale.)
When you add this Failover Partner trick, just make sure you don’t leave the production database writable on both the production server AND the failover partner at any one time. Don’t use a dev server as the failover partner, for example. If your production server goes down for a simple reboot, your applications will start connecting to the dev server, write their transactions in dev. I know you only want your production data in one database, not two, but remember – your application is the honey badger. He don’t care.
27 Comments. Leave new
Well done sir! A post that can include the honey badger while talking about connection strings is a piece of literary genius.
HAHAHA, thanks, sir. I don’t know why I haven’t used the honey badger earlier. I giggle every time I even hear the words honey badger.
Interesting post. It sounds like you really have 3 different applications because it seems like you’d have to be careful that you don’t allow anything that uses connection strings 2 and 3 to then use connection string 1 to modify data. Am I way off on this?
Jack – you can use multiple connection strings inside the same app. Don’t think of it as 3 apps – think of it as 3 databases used by the same app.
Right. I was just thinking how you have to make sure that areas of the app always use the right connection string. You wouldn’t want to query from the reporting connection string and then make a change using the read/write connection without verifying that the data has changed.
A recent project I worked on had 2 connection strings even using the same database/server. One read only for static lookup data used by the application and one for read/write.
Having multiple connection strings for a single application is a time-tested technique when you start doing things like SODA to vertically partition a database and application. Good post, Brent.
Hi Brent,
What about spliting the read and the writes to two different connection strings?
This ways you acheive several accomplishments:
1. you can set different permissions to the read and the write and improve general security to the database layer.
2. I want to make sure an application that works against a replicated database does not un-intendedly writes there. (Something that won’t bother me once I migrate to AlwaysOn in SQL Server 2012)
Oded
Assuming that a website uses multiple connection strings and possibly the same database is hit from 2-3 other websites on a shared hosting environment, could it result in a loss of performance? Can you provide a recommended max number of connections per database?
Amador – It could – your mileage may vary. This technique is designed for databases that need high scalability and plan to separate those databases out into multiple tiers as I describe in the post, though. If you’re still pointing all of the connection strings at the same database, odds are you’re not running into scale issues yet. Connection limits would depend on the hardware.
I’m pretty new with SQL and trying to setup my first Log Shipping setup. In all my searches and attempts to get this to work, doesn’t look like #3 “…Failover Partner= ” works with Log Shipping.
Fred – what’s the version/edition you’re on, what’s the build number, and what’s the error you get?
SQL Server 2008 R2 (RTM) – 10.50.1600.1
I’m struggling with connection strings and how to format them (I think), no SQL errors.
Current working web.config connection string:
I change our connection string by adding “Failover Partner=ProductionSQLMirrorName;” after the “server=” part or using the formatting from your post, attempted a few different combinations of connection strings with no luck. I have been reading the connectionstrings.com (which almost every random forum post links to to answer peoples questions relating to my problem :), but doesn’t have enough information for my limited understanding of the whole process and all the variables involved.
As an alternative I’m trying to set a SQL connection with ODBC on the web/app server, thinking we could quickly change the server in the ODBC connection and avoid updating dozens of web/app configs.
Any of these connection string adjustments break the web page I’m testing on with ASP.NET errors.
I’m just trying to introduce a basic level of DR into our single SQL server setup, with quick(ish) manual failover capability.
OK, great. Whatever you do, don’t give me the error message you’re getting. That would be entirely too helpful and would get me to track down the problem quickly, thereby making you look bored again, and you might get laid off.
(Trying a little reverse psychology since asking for the error didn’t work.)
Also, when you say single SQL Server setup, you do already have the other server set up with log shipping, right?
Sorry thought you meant a specific SQL server error of some sort.
When using connection string:
I get an error loading a part of our webpage:
Server Error in ‘/’ Application.
——————————————————————————–
Could not find a part of the path ‘C:\IISSites\…\defaultpage.html’.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IO.DirectoryNotFoundException: Could not find a part of the path ‘C:\IISSites\…\defaultpage.html’.
Which may be a bug in our application from it not being able to get to the database.
With string:
I get:
System.ArgumentException: Keyword not supported: ‘dsn’.
And:
gives me:
Unrecognized attribute ‘providerName’. Note that attribute names are case-sensitive.
I have read multiple places that ODBC is not recommended, but it may work fine for us, not a lot of connections.
I do have the 2nd SQL server setup with log shipping. Previous to this adventure I only had a single server.
OK, that’s something completely different, and I can’t really help you with that. It’s not a SQL Server error.
Fred is right. Log Shipping does not work with FailoverPArtner. SqlClient has ridiculous check and fails to connect to another database as it tries to make sure that database uses Mirroring
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
“If you specify a failover partner but the failover partner server is not configured for database mirroring and the primary server (specified with the Server keyword) is not available, then the connection will fail.”
In my case, my connection string is directed to the read-only copy. But since that server may be rebooting (or restoring files), I wanted to use the failover partner setting to connect to the original log shipped database. But this current sqlclient is too dumb. It simply fails.
Hi Brent:
Are you sure that the statement “you don’t need to use database mirroring to use Failover Partner.” is accurate? I’m unable to get it to to work with no mirror. Have two SQL instances (SQL 2016) each hosting an (identical) copy of a read-only database.
Here is the connection string:
Server=MyServer\A;integrated security=TRUE;database=MyDB;Failover Partner=MyServer\B;Network Library=dbmssocn
Test script using System.Data.SqlClient in PowerShell. MyServer\A is stopped; MyServer\B is running. Trying to open a SQLConnection produces this error:
“Server MyServer\B, database MyDB is not configured for database mirroring.”
Further, there is this blog that seems to indicate that ‘Failover Partner’ is tied to database mirroring.
https://blogs.msdn.microsoft.com/spike/2010/12/08/clarification-on-the-failover-partner-in-the-connectionstring-in-database-mirror-setup/
Can you confirm if you have another approach that works without mirroring?
Thank you!
— Dana
I get the same error as you do Dana, I was hoping to be able to configure our applications using the “Failover_Partner” keyword as we have to break mirroring for our DR tests.
I just ran into this problem my self with mirroring properties in the System.Data.SQLConnection class. It’s not a problem of log shipping or anything being intrinsically incorrect with what Brent has said technically (like, it really SHOULD work). Instead, what happens is the behavior of the .NET class will attempt to validate the presence of the mirror but only if it tries to perform the failover.
Explicitly:
– Your connection string in the SQLConnection object must be using the FAILOVER keyword.
– The object for whatever reason must decide to use actually try the failover server.
So when you instance the object, and open the connection, as long the server specified as the primary always connects, you’ll never see a problem. But as soon as something happens where it decides it has to attempt the server specified in FAILOVER, it also does something to verify that server is in fact configured as a mirror. I’m not sure how off hand, i.e. if it’s checking sys.database_mirroring or what, but it does something. If cannot find whatever it’s looking for, even if it would have worked if it would just connect, the object will throw an error and refuse to connect and throw a message like “Server [yourserver], database [yourDB] is not configured for database mirroring”.
I ran into this when I was using SQL Aliases to avoid having to update connection strings right away when migrating an instance from a mirror to a cluster, and just tried aliasing both mirror partners to the instance hosted on the cluster. Everything worked except sporadically we’d get an error about mirroring which we eventually backtracked to happening only on the occasion that the connection was attempting the failover server.
So, it might a be a good thing to throw an addendum to the article that notes that your success with using this tactic may be strongly impacted by the connection technology in use, or at least be aware of the conditions when you may have problems.
Hi Brent, a question around witness server in Database mirroring for automatic failover. Does the witness server has do be a dedicated server for witness purposes? or can I use an existing production server and install a new SQL Express instance to be used as a witness? My existing production server already has two SQL Server instances used with different applications.
Thanks
Fab
Fab – you can use an existing production server, and you don’t have to install a new instance to do it. The less instances, the better. 😀
Many thanks Brent for answering my question. That’s very helpful.
Dear Brent Ozar,
Thank you for the most valuable information about 3 connection string, but one main question i need to ask you, do i need create 3 different database user for these connection strings ?
Thanks in Advance!!!
Abhilash – no, you can use the same one if you want.
I’d like to add the use of DNS CNAMEs for those connection strings. That way if you ever start migrating from a single DB server to a multi DB server configuration, you can quickly migrate connections over to a secondary server without making code/connection string changes.
Marc – totally agreed, I love that too!
As of now, Microsoft appear to have hacked connectionstrings.com
It’s a sad day.