Best Practices for Upgrading to SQL Server 2014
Recently, we joined the folks at SQL Server Pro in hosting a webinar on Best Practices for Upgrading to SQL Server 2014. The topic sparked a great conversation – you can listen to the recording, and we’ve captured the high points of the discussion and attendee questions here.
Michael Otey, senior technical director at SQL Server Pro, took the audience through the benefits 2014 brings, the technical requirements for supporting the updated version, and some of the challenges in adopting AlwaysOn. Along the way, the SQL Server Pro audience asked some great questions.
Is it safe to upgrade from SQL Server 2008 to 2014?
Otey responded that although it was safe, you should be aware of deprecated functionality, such as no longer supporting T-SQL. And you have to check compatibility levels – each new release supports only two compatibility levels back, so make sure your apps can support the upgrade.
Are there any improvements in SQL Server 2014 over SQL Server 2012?
Otey noted that support for additional secondaries – from 4 to 8 – is the most prominent change. But you still need to program read-intent strings into your app to use those secondaries – ScaleArc can help with that task, as we’ll highlight later.
SQL Server 2014 also introduces Hekaton, Microsoft’s in-memory database, to speed performance. Turns out simple caching can improve performance more dramatically – again, you typically would need to build caching into your app.
ScaleArc’s CTO Varun Singh called out another difference between 2012 and 2014. In 2012, during failover, SQL Server drops all client connections to all servers. In 2014, SQL Server drops only the client connections going to the primary server that has failed and to the secondary that’s going to be promoted to the new primary. In both cases, SQL Server will succeed in enabling database failover, but the operation proves very disruptive to the apps. Run-time apps like those built in Java or those that hold info in state, such as .NET apps, typically have to be restarted.
Otey introduced ScaleArc’s database load balancing software as one approach to mitigate against application restarts or changes as well as some of the other limitations of SQL Server. As our CTO began illustrating how ScaleArc can help simplify the upgrade to 2014, audience questions really started flowing in.
Is connectivity from an app to ScaleArc transparent, or does it take a different driver?
ScaleArc drops in with no changes needed at the app or database. You simply change the connection strings in your app to point to ScaleArc instead of the database.
Can one ScaleArc instance handle traffic from multiple applications to multiple primaries and databases?
Yes – ScaleArc is designed with multi-tenancy and can support this configuration.
Where does ScaleArc run? Is it a dedicated server?
ScaleArc runs on bare metal, on a VM, or in the cloud. Customers can deploy it on servers supporting other functions, but they don’t typically deploy it on either the database or app servers it’s supporting.
How does ScaleArc handle failover?
Because the ScaleArc software provides connection management and pooling, it does not drop client connections during failover. It also queues inbound queries during database failover, while the secondary is being promoted, and then sends those queries to the new primary once it’s ready to accept traffic. As a result, applications see delay but not errors, so ScaleArc makes the database failover transparent to the app.
How is the ScaleArc service built in a way that it does not become the single point of failure?
ScaleArc is designed to be deployed in high availability (HA) mode, with two instances front-ending each cluster of a database.
With an n-node ScaleArc configuration, how do the nodes communicate with one another to ensure that transaction affinity is maintained?
ScaleArc never splits transactions across servers – those are always directed to a single primary. We automatically support read/write split, so you don’t have to reprogram your apps to send reads to the secondaries.
The questions kept coming right until the top of the hour – we really enjoyed the engaging conversation, and you can check out this white paper for more info on how to overcome the top challenges in upgrading to SQL Server 2014.
comments powered by Disqus