Best Practices for Migrating to SQL Server 2014

executive summary

Best Practices for Migrating to SQL Server 2014

• • • • •

Microsoft SQL Server 2014 offers many new features that make it a compelling upgrade. Compatibility and hardware requirements should be understood before committing to upgrade. Two upgrade paths—in-place upgrade and data migration install—are available for SQL Server. New features, such as AlwaysOn, must be supported with application changes or by third-party software to apply. ScaleArc offers businesses a faster time-to-value for SQL Server upgrades with automatic support for many 2014 features.

November 18, 2014 Michael Otey, Senior Technical Director, Windows IT Pro & SQL Server Pro Varun Singh, Founder & CTO, ScaleArc

in partnership with

executive s u m m a ry

Best Practices for Migrating to SQL Server 2014 Overview Upgrading a business-critical back-end solution like SQL Server requires careful planning, and it is not just about the project plan. Businesses need to weigh the benefits of the upgrade against the time and risks as well as any costs involved in procuring new hardware. While new features often provide compelling reasons to upgrade—for example, AlwaysOn and the in-memory OLTP engine can be compelling for SQL Server 2014 upgrades— they can also require additional consideration and support configurations. ScaleArc’s database load-balancing solution can help organizations realize value from these upgrades faster than if they had to make application or other changes to complete the upgrade. The solution mitigates some of the inefficiencies in SQL Server 2014, offering automated support for read/write splits, geo-aware load balancing, replication lag monitoring, and auto-failover within or between data centers that is transparent to the app.

Context Michael Otey introduced the key benefits of upgrading to SQL Server 2014 and discussed upgrade paths and considerations. Varun Singh provided insight on how ScaleArc can help businesses achieve faster value from the upgrade.

Key Takeaways Microsoft SQL Server 2014 offers many new features that make it a compelling upgrade. New features in Microsoft SQL Server 2014 are likely to compel businesses to upgrade. These features include:

• Increased resource limits. This RAM

increase is particularly evident in the Standard Edition, where support doubled from 64 GB to 128 GB.

• In-memory OLTP engine. Certain tables

and stored procedures can be moved into RAM to improve performance.

• AlwaysOn. Introduced with SQL Server

2012, this high-availability and disasterrecovery solution improves upon what database mirroring and failover clustering offer.

• Azure integration. Support for Microsoft’s

cloud offering is available with this release.

• Updateable columnstore index. Improving upon the memory-optimized columnstore index feature introduced in SQL Server 2012, this feature offers the same query performance improvements, but the columnstore is now a pure columnar store, so indexing is no longer required.

• Buffer pool extensions. In some cases,

SSDs can be used to expand the buffer pool when systems run out of memory.

Page 2



• Resource Governor for I/O. In addition to

governing and managing CPI and memory, the Resource Governor now manages I/O requests.

• Backup enhancements. With this release, backups can be encrypted. Azure can also be used as a target for backups.

• Managed backups. Small and medium-

sized businesses, in particular, may find this new managed backup capability beneficial, as it offers a simple “set it and forget it” model of backing up, eliminating the need for a backup expert.

Compatibility and hardware requirements should be understood before committing to upgrade. Before executing an upgrade, businesses need to check with ISVs or in-house application developers to make sure all software is compatible with SQL Server 2014. They also need to prepare for any changes and deprecations that might occur in migrating to the new release. Since SQL Server is the back end for missioncritical applications, IT organizations should baseline the current environment, using tools like Profiler and Perform. Comparing baselines made before and after the upgrade allows IT to know that everything is running as expected in the new environment. Backups provide a safety net if anything goes wrong during the upgrade. Good documentation of configuration settings for SQL Server and the individual databases are also necessary for a successful upgrade.

While most databases are still on-premise— either physical or virtual—businesses may consider moving them to the cloud. Whether using IaaS (Infrastructure as a Service) or SQL Azure, businesses can often benefit from increased scalability and lower capital costs than when hosting on-premise. Even when staying on-premise, SQL Server 2014 has minimal hardware requirements, as shown below.

SQL Server 2014 was designed to run over a wide range of hardware, supporting anything from desktop with SQL Server Express to full-blown enterprise applications supporting large businesses and enterprises. Michael Otey Component

Requirement

Processor Speed

–– 32-bit x86 implementation: 1.0 GHz –– 64-bit x64 implementation: minimum of 1.4 GHz, recommended is 2.0 GHz

RAM

Recommended minimum for Standard Edition and above is 4 GB

Free disk space

Minimum of 6 GB

Although the majority of implementations are 64-bit, SQL Server 2014 still supports 32-bit implementations, since many legacy applications are still in use.

Page 3



SQL Server 2014 Enterprise, BI, and Standard Editions

32-bit implementation

64-bit implementation

–– Windows Server 2012 R2

–– Windows Server 2012 R2

–– Windows Server 2012

–– Windows Server 2012

–– Windows Server 2008 R2 SP1

–– Windows Server 2008 R2 SP1

–– Windows Server 2008 SP2 64-bit & 32-bit SQL Server 2014 Standard Edition

–– Windows Server 2008 SP2

–– Windows 8.1 64-bit & 32-bit

–– Windows 8.1

–– Windows 8 64-bit & 32-bit

–– Windows 8

–– Windows 7 SP1 64-bit & 32-bit

–– Windows 7 SP1

SQL Server also supports Windows Server Core with Windows Server 2008 R2 SP1 and higher. This allows the database to run on a minimal hardware platform, reducing the attack surface and making the system more efficient.

Two upgrade paths—in-place upgrade and data migration install—are available for SQL Server. The SQL Server upgrade path selected—in-place upgrade or data migration install—is often based on the DBA’s preference. Both paths are available in the SQL Server Installation Center.

Installation

In-Place Upgrade

Data Migration Install

Install a new version over the older version; SQL Server can be upgraded to SQL Server 2014 using this method from:

Install a new instance.

–– SQL Server 2012 SP1 –– SQL Server 2008 R2 SP2 –– SQL Server 2008 SP3 –– SQL Server 2005 SP4 OS

Uses the existing operating system.

Typically uses a new operating system.

Registry and patches

Keeps existing.

Requires new.

Speed

Faster upgrade method.

Slower upgrade method.

SQL Server configuration

Keeps the current configuration.

Requires a reconfiguration on the new system.

Code and data files

Keeps existing.

Requires a migration.

Page 4


Best Practices for Migrating to SQL Server 2014 There is downtime in both upgrade paths, although in-place downtime is more significant since the database will be down during the entire install, rather than just during the cutover, which is the case with the data migration install. Although the data migration install path is slower, some DBAs prefer it because they are able to control which patches are applied and they consider it less prone to data corruption that can occur with an in-place upgrade.

New features, such as AlwaysOn, must be supported with application changes or by third-party software to apply. As part of the upgrade, IT organizations should also consider which new features they wish to use so they can set up the system appropriately. With SQL Server 2014, many businesses are moving to AlwaysOn for improved database availability. While database mirroring was limited to two databases, which were both either synchronous or asynchronous, AlwaysOn allows for up to eight secondary replicas, which can be mix of synchronous and asynchronous replication.

You can combine multiple databases, and the failover is pretty fast. It’s faster than failover clustering.

Although AlwaysOn offers better databaselevel protection than database mirroring and failover, there are challenges that need to be taken into consideration.

• Application changes may be necessary.

AlwaysOn supports an active configuration, where online databases are used for read/ write, and replicas are used for read-only to offload processing and for running backups. Supporting this read/write split requires adding read-intent strings into applications, and identifying questions and stored procedures to route to read-intent strings.

• Replication requires awareness. The

replication functionality has its own set of challenges. When asynchronous replication is used, it is possible to serve stale data, resulting in a data loss. Read-intent strings are server specific, and AlwaysOn load balancing is limited. Also, if the server defined in the string is unavailable, the system can’t redirect to an available readonly replica.

• Auto failover has limitations. It creates application errors, and failover between data centers must be manual.

DBAs should also keep in mind that support for T-SQL is deprecated in SQL Server 2014. Non-ANSI outer join syntax is no longer supported. Additionally, compatibility levels are only to the prior two versions; SQL Server 2014 does not support SQL Server 2005.

Michael Otey

Page 5


Best Practices for Migrating to SQL Server 2014 ScaleArc offers businesses a faster time-to-value for SQL Server upgrades with automatic support for many 2014 features. ScaleArc is a database load-balancing solution that sits transparently between the web or applications and the database servers. This software provides an abstraction layer that allows applications to address the entire SQL cluster without knowing how that cluster is located or configured in terms of roles and responsibilities for individual servers.

With no ability to queue inbound queries during failover, SQL Server may complete the failover but dropped connections and hung applications will force a restart. Varun Singh

When paired with AlwaysOn, ScaleArc helps organizations get value from SQL Server upgrades faster. Businesses can right-size environments and spread the load effectively across multiple servers. As a result, organizations don’t need to over-provision the primary server, which can also lead to a reduction in licensing costs. Geo-aware load balancing allows businesses to run active-active configurations across multiple data centers. The software will keep reads local—within the same data center— and send writes to the primary in the appropriate data center. ScaleArc enables all this functionality without changes at the application layer. ScaleArc allows businesses to set replication threshholds, so that data older than a certain number of seconds isn’t served from that outdated read server.

The ScaleArc solution also supports transparent scale out, helping businesses understand the kinds of workloads running on the databases and seamlessly spread the load across multiple servers. As soon as new nodes are added, ScaleArc automatically distributes the load across that new resource to improve performance.

Automatic failover is handled in concert with AlwaysOn. ScaleArc’s queuing technology ensures that queries coming in from the application during the failover window are queued until the secondary server is promoted to primary and ready to take traffic.

Page 6



Every single query that is offloaded from your primary is improving the amount of resources you have available on the primary. You can actually do more writes as a result of that. Varun Singh

Additional Information

• For more information on ScaleArc, visit: http://www.scalearc.com.

• Additional SQL Server Resources: --SQL 2012 Upgrade Technical Guide --Use Upgrade Advisor to Prepare for Upgrades

--Upgrade to SQL Server 2014 --Migrating to SQL Server 2014

ScaleArc allows IT organizations to perform maintenance any time, enabling zero-downtime patching and other services. It can also help with cloud migrations by simplifying the migration, providing high-availability solutions with auto-failover, delivering high performance, and solving future scaling issues.

Page 7


Best Practices for Migrating to SQL Server 2014 Biographies Michael Otey

Senior Technical Director, Windows IT Pro & SQL Server Pro

Michael Otey, senior technical director for Windows IT Pro and SQL Server Pro, is president of TECA, a software-development and consulting company in Portland, Oregon. Michael has covered the topic of virtualization extensively for Windows IT Pro, having written several features articles showing how to take advantage of virtualization in the enterprise as well as reviewing all of the major virtualization products.

Varun Singh

Chief Technology Officer, ScaleArc

As Chief Technology Officer, Varun Singh leads ScaleArc’s product development and technology strategy. Varun also served as ScaleArc’s CEO from March 2009 to Sept 2013. Varun is a technologist with broad experience in several fields, from running Linux-based BBS systems in the pre-Internet days to writing about technology and open source software at PC Quest, CHIP, Digit, and Network Computing, to creating India’s top online technology brands such as TechTree and Tech2, to hosting technology shows on CNBC, TV18, CNN-IBN, and ET Now. He led large web and application development teams as CTO at Network 18’s web division and built the highly scalable technology behind large websites like IN.com, MoneyControl.com, and various other portals.

Page 8

© 2014 Penton Media, Inc. All rights reserved.