Modern SQL – Microsoft SQL Server 2012, 2014, and 2016 – with AlwaysOn failover, synchronous replication technology, and the ability to read from secondary replicas, offer compelling new functionality, yet organizations are discovering the upgrade path can be fraught with obstacles. From hundreds of application development hours required to support the new features to the additional complexity brought on by AlwaysOn – as well as higher licensing and storage costs – enjoying the benefits of Modern SQL can be not only elusive but also costly and inefficient unless planned and executed correctly.
To get more in-depth information about these five most common challenges and their exceptionally practical solutions download the whitepaper in its entirety here:
Here are the five most common challenges to leverage SQL Server 2016’s best new features.
1.Challenge: Load balancing and read/write split
AlwaysOn allows your secondary servers to process read traffic instead of just sitting idle. You gain significant benefits since you’re improving hardware utilization and serving more traffic with better response times. But to take advantage of this capability, you must modify applications to support the readintent string as part of the SQL connection parameters for that traffic to connect to a secondary. Just connecting to a secondary server isn’t enough, either – you will also need to let your application know which queries can be sent to a secondary server, which can be very complicated and time consuming.
2.Challenge: Replication aware load balancing
AlwaysOn constantly replicates data from primary to secondary servers giving users easy access to consume information from another source. But how quickly is that information changing? With the inherent replication lag, organizations must determine how big a lag is acceptable for their business and mitigate potential consequences of consuming data that isn’t fresh.
3.Challenge: Core-based licensing costs
Changes in Modern SQL’s licensing model – moving from per-socket to per- core fees – combined with modern CPU architectures that support an ever- increasing number of cores per socket, mean licensing costs can be significantly higher for SQL Server 2012/2014/2016. The new licensing also calls for a minimum of four core licenses per physical processor.
4.Challenge: Lack of visibility and control at a cluster level
Without a real-time and comprehensive view into what’s happening across your system, you can’t accurately troubleshoot or assess application performance. While SQL Server has historically lacked an extensive diagnostics and real-time visibility component, this shortcoming becomes compounded in Modern SQL’s multi-server environment.
5.Challenge: Downtime and Failover
Modern SQL limits automated failover to servers sitting within a single data center, despite having the ability to scale beyond a single datacenter. This limitation curtails the architectural advantages of operating within a distributed environment.
Big rewards come with big risks. Upgrades are costly in terms of licensing and storage not to mention the hours your app dev resources will need to support those new features. It’s not easy. Nothing worthwhile ever is. But don’t assume you’re going to need to modify application level code or database layers. Database load balancing software allows your team to use AlwaysOn clustering as a helpful tool in maximizing the value of your SQL Server upgrade.