Blog

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:

  1. Writes and real-time reads
  2. Reads that can tolerate data older than 15 seconds (no writes allowed with this connection string)
  3. 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.

Honey badger don't care about your reports.

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:

  1. 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.
  2. Reads older than 15 seconds – all queries must be peer-reviewed.
  3. 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.

↑ Back to top
  1. Well done sir! A post that can include the honey badger while talking about connection strings is a piece of literary genius.

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

  3. Pingback: Something for the Weekend – SQL Server Links 28/10/11

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

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

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

  7. Pingback: My 3 Favorite Connection String Tips | IScrapBook<Qujck>

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php