Introducing Microsoft SQL Server 2014: Technical Overview, 1st Edition (2014)
PART I. Database administration
PART I. Database administration
CHAPTER 1 SQL Server 2014 editions and engine enhancements
CHAPTER 2 In-Memory OLTP investments
CHAPTER 3 High-availability, hybrid-cloud, and backup enhancements
CHAPTER 1. SQL Server 2014 editions and engine enhancements
SQL Server 2014, the latest complete information platform from Microsoft, embodies the new era of Microsoft’s Cloud OS, which provides organizations and customers with one consistent platform for infrastructure, apps and data that span customer data centers, hosting service-provider data centers, and the Microsoft public cloud. The benefits that customers experience with a consistent platform include common development, management, data, identity, and virtualization, no matter where an application is being run.
SQL Server 2014 also offers organizations the opportunity to efficiently protect, unlock, and scale their data across the desktop, mobile devices, data centers, and a private, public, or hybrid cloud. Furthermore, SQL Server 2014 builds on the success of SQL Server 2012 by making a strong impact on organizations worldwide with significant new capabilities that are tightly aligned to the trends transforming the IT industry, including Microsoft’s Cloud OS. SQL Server provides mission-critical performance for the most demanding database applications while delivering the highest forms of security, scalability, high availability, and support. SQL Server 2014’s mission is to deliver faster insights into big data, small data—all data—and, most importantly, deliver business intelligence in a consumable manner through familiar tools such as Microsoft Excel.
Finally, SQL Server 2014 enables new unique hybrid-cloud solutions based on the Cloud OS. These solutions can positively affect an organization’s bottom line and allow an organization to create innovative solutions for its database applications. Figure 1-1 shows the mission statement for SQL Server 2014, based on three pillars.
FIGURE 1-1 SQL Server 2014, a cloud-ready information platform.
This chapter examines the new features, capabilities, and editions of SQL Server 2014 from a database administrator’s perspective. It also describes hardware and software requirements and installation strategies.
SQL Server 2014 enhancements for database administrators
The organizations of today are looking for a trusted, cost-effective, and scalable database platform that offers mission-critical confidence, breakthrough insights, and flexible cloud-based offerings. These organizations regularly face changing business conditions in the global economy and numerous challenges to remaining competitive, such as gaining valuable business insights, using the right information at the right time, and working successfully within budget constraints.
In addition, organizations must be fluid because new and important trends regularly change the way software is developed and deployed. Some of these trends focus on in-memory databases that can improve performance and efficiency, a capability that has most likely been influenced by the dramatic drop in the cost of memory. Another important trend sweeping over IT is the adoption of cloud computing by customers who require an infrastructure that provides a generational leap in agility, elasticity, and scalability across a set of shared resources with more automation of cloud services and self-service provisioning. Last but not least is the data-explosion trend, where discussions about storage capacity naturally involve the word zettabytes. Customers want to gain new insights about their businesses faster, so they are analyzing larger data sets, both internal and external. These new insights are helping customers obtain a competitive edge, but they are also leading to massive volumes of data.
Microsoft has made major investments in SQL Server 2014 as a whole. The new features and capabilities that should interest database administrators are introduced in this chapter, including availability, backup and restore, scalability, performance, security, manageability, programmability, and the platform for the hybrid cloud. Other chapters in this book offer a deeper explanation of the major technology investments.
Availability enhancements
A tremendous number of high-availability enhancements were added to SQL Server 2012, which increased both the confidence that organizations have in their databases and the maximum uptime for those databases. Microsoft built on this work with new high-availability enhancements in SQL Server 2014.
AlwaysOn Availability Groups
In SQL Server 2012, AlwaysOn Availability Groups was the most highly anticipated feature related to the Database Engine for DBAs. This high-availability and disaster-recovery capability provided protection by allowing one or more databases to fail over as a single unit. Shared storage was not needed, and replicas could be leveraged to offload backups and reporting workloads from the primary.
Enhancements in SQL Server 2014 have given databases even better data redundancy, protection, and availability. First, the maximum number of secondary replicas has increased from four to eight. This change allows organizations to further offload read-only operations such as reporting and backups to additional secondary replicas. The additional secondary replicas can also be placed in more data centers for higher levels of protection and disaster recovery. Moreover, with SQL Server 2014, the secondary replicas have been enhanced and can be used for read-only operations even in the case of network failures or loss of quorum between replicas. Second, whether a manual or an automatic failover is performed, the operation no longer needs to fail over databases one at a time. Numerous databases can now fail over simultaneously, which increases availability.
Finally, SQL Server 2014 introduces the Add Azure Replica wizard for organizations looking to create a secondary replica that is stored in Windows Azure, Microsoft’s public cloud. (See Figure 1-2.) Placing a secondary replica in Windows Azure is a great way for an organization to achieve additional disaster-recovery protection in the unlikely event that all data centers hosting its secondary replicas become unavailable.
FIGURE 1-2 A global organization using AlwaysOn Availability Groups, including Windows Azure, to achieve high availability and disaster recovery.
In Figure 1-2, company X is an organization with a global presence. It achieves both high availability and disaster recovery for mission-critical databases by using AlwaysOn Availability Groups. Secondary replicas are placed in data centers around the world, including in Windows Azure, and are being used to offload reporting and backups.
AlwaysOn Failover Cluster Instances (FCI)
AlwaysOn Failover Cluster Instances (FCIs) is a feature that provides superior instance-level protection by using Windows Server Failover Clustering and shared storage. Traditionally, each SQL Server failover cluster instance required at least one logical unit number (LUN) because the LUN was the unit of failover. This requirement imposed a significant limitation because when a database administrator ran out of drive letters or mount points, the administrator also lost the opportunity to host any more failover cluster instances. This anomaly has been addressed in SQL Server 2014 with the use of the Cluster Shared Volumes (CSVs) feature, as shown in Figure 1-3, which requires fewer LUNs. As a note, this feature is included in Windows 2012 and later releases.
FIGURE 1-3 Using the Cluster Shared Volume feature associated with Windows Server 2012 R2 for storing SQL Server 2014 virtual machines.
CSVs reduce the number of LUNs (disks) required for SQL Server clustered instances because many failover cluster instances can use a single CSV LUN and can fail over without causing the other FCIs on the same LUN to also fail over. Finally, new dynamic management views introduced in SQL Server 2014 help administrators troubleshoot and return information pertaining to FCIs.
Backup and restore enhancements
Regardless of how many database replicas an organization has within its enterprise, there is still a need to protect data with backups. Hence, Microsoft continued its investments in backup and recovery to protect data with SQL Server 2014. The new enhancements include the following:
SQL Server Managed Backups to Windows Azure Backups in SQL Server 2014 natively support the Windows Azure Blob storage service for simplifying backups to the cloud. Hybridcloud backups reduce capital expenditures (CAPEX) and operational expenditures (OPEX) and improve disaster recovery for an organization’s backups because the backups stored in the Windows Azure cloud are automatically replicated to multiple data centers around the world. The process to exploit this new enhancement is fairly straightforward. First, create a Windows Azure storage account and a blob container, and then generate a SQL Server credential that will be used to store security information and access the Windows Azure storage account. Finally, create a backup that will use the Windows Azure Blob storage service.
SQL Server backups to URLs SQL Server backups have been updated to use URLs as one of the destination options when backups are performed with SQL Server Management Studio. Backups are stored in Windows Azure because the Windows Azure Blob storage service is used. Previously, only Transact-SQL, PowerShell, and SQL Server Management Objects (SMO) were supported when using SQL Server 2012 SP1 CU2 and later. A database backup to a URL destination is depicted in Figure 1-4.
Encryption for backups For years, DBAs have been asking for the ability to natively encrypt data while creating a backup. This task can now be performed in SQL Server 2014 by specifying an encryption algorithm and an encryptor—a certificate or an asymmetric key—to secure the encryption key. The industry standard encryption algorithms that are supported include AES 128, AES 192, AES 256, and Triple DES. Encrypted backups are supported in Windows Azure storage or on-premises.
FIGURE 1-4 Backing up a database to an Azure storage container by using a URL prefix.
Scalability and performance enhancements
The SQL Server product group made sizable investments to improve scalability and performance associated with the SQL Server Database Engine. Some of the main enhancements allow organizations to improve their SQL Server workloads, especially when using Windows Server 2012 or later releases:
In-Memory OLTP In-Memory OLTP (project code name Hekaton) is considered the most important feature release and investment in SQL Server 2014. This new feature is fully integrated into the Database Engine component. Databases were originally designed to reside on disk because of the high costs associated with procuring memory. This situation has since changed, due mainly to the significant drop in price for memory. It is now possible for most OLTP databases to fit into memory, which reduces I/O expense, and in turn increases transaction speed performance. To date, organizations testing In-Memory OLTP have reported promising numbers: transaction speeds improved up to 30 times that of their past performance. As with any new capability, mileage will vary, and the best performance gains have been achieved when the business logic resides in the database and not in the applications.
Computing resources At the heart of the Microsoft Cloud OS vision, Windows Server 2012 R2 offers a tremendous amount of computing resources to provide scale for large, mission-critical databases not only in a physical environment but also in a virtual environment. Windows Server 2012 R2 supports up to 2,048 logical processers for a Hyper-V host, which can handle the largest database applications. In a virtual environment you can use up to 64 virtual CPUs, up to 1 terabyte (TB) of memory, and up to 64 TB of virtual disk capability for each Hyper-V virtual guest. In addition, you now have up to 64 nodes in a SQL Server cluster and up to 8,000 virtual machines within a Hyper-V cluster. Enterprise scale on this magnitude has never been achieved before.
Scale networking Windows Server 2012 R2 introduced many new capabilities in the area of virtual networking that bolster the SQL Server experience. Network virtualization provides a needed layer of abstraction, which allows SQL Server workloads to be moved from one data center to another. NIC teaming, which was introduced with Windows Server 2012, still exists and can be used to provide fault tolerance by enabling multiple network interfaces to work together as a team. Finally, SMB Multichannel and Network Quality of Service (QoS) can be used in conjunction with SQL Server to improve database application availability over physical and virtual networks by ensuring that multiple paths are available to application shares and that sufficient available bandwidth is reserved for the application.
Scale storage Windows Server 2012 introduced Storage Spaces, a feature that was enhanced in Windows Server 2012 R2. Storage spaces allow database administrators to take advantage of sophisticated virtualization enhancements to the storage stack that can distribute or tier SQL Server workloads across storage pools. For example, high-capacity spinning disks can be used to store less frequently used data, while high-speed solid-state disks can be used to store more frequently used data. Windows Server 2012 R2 recognizes the tiers and optimizes them by placing hot data in the fastest tier and less-utilized data in lower tiers, improving performance without increasing costs.
Resource Governor enhancements In previous versions of SQL Server, it was possible to pool CPU and memory to manage SQL Server workloads and system-resource consumption. In SQL Server 2014, I/O has been added to Resource Governor, which lets I/O be pooled and tiered following an organization’s criteria. This ensures greater scale and performance predictability for your SQL Server workloads, especially when running applications in private clouds and environments managed by hosting organizations.
Buffer pool extension Buffer pool extension enables integration of a nonvolatile randomaccess-memory extension with the Database Engine buffer pool to significantly improve I/O throughput. Solid-state drives (SSDs) would be considered nonvolatile random access memory, which would be added to the SQL Server system to improve query performance. Benefits that can be achieved when you use buffer pool extension include increased random I/O throughput, reduced I/O latency, increased transaction throughput, improved read performance with a larger buffer pool, and a caching architecture that can take advantage of present and future low-cost memory drives.
Sysprep enhancements SQL Server 2014 supports new functionality in Sysprep, which allows you to fully support deployments of clustered SQL Server instances. This capability reduces deployment time for SQL Server failover clusters and is great for building private or public clouds.
Columnstore enhancements Columnstore indexes are used to accelerate query performance for data warehousing that primarily performs bulk loads. In the previous version of SQL Server, tables that had columnstore indexes could not be updated. In the past, you had to drop the index, perform the update, and then rebuild the index, or use partition switching or two tables—one with a columnstore index and another for updating—and then use UNION ALL queries to return data. As you can imagine, this could be challenging from an administrative perspective. With SQL Server 2014, in-memory columnstore has been modified to support updateable operations such as inserts, updates, and deletes.
Security enhancements
Approximately a decade has passed since Microsoft initiated its trustworthy computing initiative. Compared with other major database players in the industry, SQL Server has had the best track record since then, with the least number of vulnerabilities and exposures. Based on results from an April 2013 study conducted by the National Institute of Standards and Technology (NIST), shown in Figure 1-5, SQL Server led the way five years in a row as the least-vulnerable database among the top data platforms tracked. Moreover, it is currently the most-utilized database in the world, with 42 percent of market share. (Source: ITIC 2013: “SQL Server Delivers Industry-Leading Security.”)
FIGURE 1-5 Common vulnerabilities and exposures reported to NIST from January 2006 to January 2013.
With SQL Server 2014, the product continues to expand its solid foundation to deliver enhanced security and compliance within the database platform. By splitting the database administrator role from the system administrator role and allowing organizations to further customize the rights of each DBA or system administrator, greater compliance and security can be achieved. Here is a snapshot of some of the enhanced enterprise-ready security capabilities and controls that enable organizations to meet strict compliance policies and regulations:
Redefined engineering security process
CC certification at high assurance level
Enhanced separation of duty
Transparent data encryption (TDE)
Encryption key management
Support for Windows Server Core
A new set of explicit server-level and database-level permissions for securables have been introduced in SQL Server 2014 to further enhance access and security:
CONNECT ANY DATABASE A server-level permission that grants a login the ability to connect to all databases that currently exist and to any new databases that might be created in the future.
IMPERSONATE ANY LOGIN Another new server-level permission, IMPERSONATE ANY LOGIN allows a middle-tier process to impersonate the account of clients connecting to it as it connects to databases.
SELECT ALL USER SECURABLES When granted, this new server-level permission allows a login to view data in all databases that the user can connect to.
ALTER ANY DATABASE EVENT SESSION Unlike the other permissions listed here, ALTER ANY DATABASE EVENT SESSION is a database-level permission. It is typically used to give a role the ability to read metadata associated with a database for monitoring purposes. A perfect example would be Microsoft System Center Operations Manager agents used to proactively monitor a SQL Server database.
Platform for hybrid cloud
SQL Server 2014 empowers a diverse set of hybrid-cloud opportunities that can reduce both capital and operational expenditures for an organization. These include backing up to the cloud, extending high availability to the cloud, hybrid application development, and improved on-premises disaster recovery. Let’s review the different types of hybrid-cloud solutions that organizations can take advantage of:
Deploy a database to SQL Server in a Windows Azure Virtual Machine A new wizard in SQL Server 2014 allows a DBA to use SQL Server Management Studio to seamlessly deploy and transition a database from an on-premises deployment to a Windows Azure Virtual Machine. Windows Azure Virtual Machines are an Infrastructure-as-a-Service (IaaS) offering included in Windows Azure. The implementation is based on a few simple steps:
1. Specify the source connection settings such as the SQL Server instance, database name, and temporary location for backup files.
2. Provide Windows Azure sign-in credentials that include a management certificate.
3. Enter information for the Windows Azure Virtual Machine or, if you plan on deploying your database to an existing Windows Azure Virtual Machine, provide the DNS name for the cloud service and the credentials for the virtual machine and SQL Server instance.
Deploy a database to Windows Azure SQL Database Another new feature in SQL Server 2014 allows a DBA to easily deploy an on-premises database to Windows Azure SQL Database. Windows Azure SQL Database is a relational-database service in the Windows Azure Platform-as-a-Service (PaaS) environment. The wizard used in this type of deployment can also be used to move databases from Windows Azure SQL Database to an on-premises SQL Server instance or to move databases from one instance of Windows Azure SQL Database to another. The deployment process is very straightforward. Only credentials associated with the Windows Azure SQL Database account are required; the wizard takes care of the rest.
Simplified cloud backup and cloud disaster recovery As mentioned earlier, SQL Server 2014 and Windows Azure are tightly integrated, providing organizations with the ability to implement hybrid scenarios such as backing up on-premises databases to the cloud. To achieve this goal, all that is required is a URL and a storage key. Then, a straightforward policy can be created and used to back up a single database or all databases within a SQL Server instance directly to Windows Azure storage. This process can be automatic or manual. Windows Azure storage provides additional benefits through out-of-the-box geo-replication. The use of geo-replication protects databases because the backups are stored in multiple Windows Azure data centers around the world, hence offering superior disaster recovery. Finally, the backups stored in Windows Azure can be restored on Windows Azure Virtual Machines (should a terrible disaster transpire, taking out an organization’s on-premises data center).
Better together: AlwaysOn and Windows Azure Many organizations need to account for offsite disaster recovery, but they do not have a secondary data center or their secondary data center is within close proximity to the primary data center. Therefore, a single disaster could potentially take out both the primary and the secondary data centers, causing a major outage. In these cases, hybrid cloud provides organizations better disaster-recovery scenarios, and these scenarios also lower RTO (recovery time objective) and increase RPO (recovery point objective). As mentioned earlier, the Add Azure Replica wizard in SQL Server 2014 can be used to create secondary AlwaysOn asynchronous replicas in Windows Azure Virtual Machines. Therefore, in the event of a disaster, a replica can be failed over to the Windows Azure public cloud because all transactions committed on-premises will be sent asynchronously to the Windows Azure replica.
Extend on-premises applications An organization can take advantage of hybrid scenarios to extend and scale on-premises applications. For example, a physical retailer could use its on-premises SQL Server and supporting infrastructure to continue to serve physical retail transactions and utilize cloud-based Windows Azure infrastructure services to support online sales. Another scenario is a pizza organization that needs to scale to support the irregular peak demands of its business, which occur only on Super Bowl Sunday. The pizza maker could transition the web tier of its online pizza ordering system to Windows Azure while maintaining the back-end database on-premises. Windows Azure would provide the automatic scale-out functionality to support the irregular demand without the need to overprovision on-premises infrastructure, which would be costly and seldom used throughout the year.
Enhancing backups with Windows Azure Blob storage Windows Azure Blob storage allows for a flexible and reliable backup option that does not require the overhead of traditional hardware management, including the hassle and additional costs associated with storing backups offsite. In addition, by leveraging a direct backup to Windows Azure Blob storage, you can bypass the 16-disk limit, which was a concern in the past.
As you can see, SQL Server 2014 delivers many new capabilities for building hybrid-cloud solutions that use Microsoft’s Cloud OS vision. This vision entails a consistent experience with a common set of tools across the entire application life cycle, no matter where you are running your data platform.
SQL Server 2014 editions
Similar to the previous version, SQL Server 2014 is available in three principal editions. All three editions have tighter alignment than their predecessors and were designed to meet the needs of almost any customer. Each edition comes in a 32-bit and 64-bit version. The principal editions, as shown in Figure 1-6, are the following:
Enterprise edition
Standard edition
Business Intelligence edition
Figure 1-6 The main editions of SQL Server 2014.
Enterprise edition
The Enterprise edition of SQL Server 2014 is the uppermost SKU and is considered the premium offering. It is designed to meet the highest demands of large-scale data centers and data warehouse solutions by providing mission-critical performance and availability for tier 1 applications and the ability to deploy private-cloud, highly virtualized environments and large, centralized, or external-facing business-intelligence solutions.
Note The Datacenter edition included in the previous version of SQL Server is now retired. However, all capabilities of the Datacenter edition are in the Enterprise edition of SQL Server 2014.
The Enterprise edition features include the following:
A maximum number of cores, subject to the operating system being used
Unlimited virtualization
AlwaysOn to achieve advanced high availability
Unlimited virtualization for organizations with software assurance
Support for the new columnstore indexing feature
Advanced auditing
Transparent data encryption
Compression and partitioning
In addition, all the features and capabilities of the Business Intelligence edition are available, including:
Reporting
Analytics
Multidimensional BI semantic model
Data quality services
Master data services
In-memory tabular BI semantic model
Self-service business intelligence
Standard edition
The Standard edition is a data-management platform tailored toward departmental databases and limited business-intelligence applications that are typically appropriate for medium-class solutions, smaller organizations, or departmental solutions. It does not include all the bells and whistles of the Enterprise and Business Intelligence editions, although it continues to offer best-in-class manageability and ease of use. Compared with the Enterprise and Business Intelligence editions, the Standard edition supports up to 16 cores and includes the following features:
Spatial support
FileTable
Policy-based management
Corporate business intelligence
Reporting
Analytics
Multidimensional BI semantic model
AlwaysOn 2-Node failover clustering to achieve basic high availability
Up to four processors, up to 64 GB of RAM, one virtual machine, and two failover clustering nodes
Business Intelligence edition
The Business Intelligence edition offers organizations the full suite of powerful BI capabilities, such as scalable reporting and analytics, Power View, and PowerPivot. It is tailored toward organizations that need corporate business intelligence and self-service capabilities but do not require the full online transactional processing (OLTP) performance and scalability found in the Enterprise edition. Here is a high-level list of what the Business Intelligence edition includes:
Up to a maximum of 16 cores for the Database Engine
Maximum number of cores for business-intelligence processing
All of the features found in the Standard edition
Corporate business intelligence
• Reporting
• Analytics
• Multidimensional BI semantic model
Self-service capabilities
• Alerting
• Power View
• PowerPivot for SharePoint Server
Enterprise data management
• Data quality services
• Master data services
In-memory tabular BI semantic model
Basic high availability can be achieved with AlwaysOn 2-Node failover clustering
Specialized editions
Above and beyond the three principal editions of SQL Server 2014, Microsoft continues to deliver specialized editions for organizations that have a unique set of requirements. Some examples include the following:
Developer The Developer edition includes all the features and functionality found in the Enterprise edition; however, it is meant strictly for the purpose of development, testing, and demonstration. Note that you can transition a SQL Server Developer installation directly to production (without reinstallation) by upgrading it to SQL Server 2014 Enterprise.
Web Available at a much more affordable price than the Enterprise and Standard editions, the SQL Server 2014 Web edition is focused on service providers hosting Internet-facing webservices environments. Unlike the Express edition, this edition doesn’t have database size restrictions, it supports four processors, and supports up to 64 GB of memory. SQL Server 2014 Web edition does not offer the same premium features found in the Enterprise and Standard editions, but it still remains the ideal platform for hosting websites and web applications.
Express This free edition is the best entry-level alternative for independent software vendors, nonprofessional developers, and hobbyists building client applications. Individuals learning about databases or learning how to build client applications will find that this edition meets all their needs. In a nutshell, this edition is limited to one processor and 1 GB of memory, and it can have a maximum database size of 10 GB. Also, Express is integrated with Microsoft Visual Studio.
Note To compare the different editions of SQL Server 2014 based on their key capabilities, review “Features Supported by the Editions of SQL Server 2014” at http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx and http://msdn.microsoft.com/en-us/library/ms144275(v=sql.120).aspx.
SQL Server 2014 licensing overview
The licensing models affiliated with SQL Server 2014 are simplified to better align to customer solutions and also optimized for virtualization and cloud deployments. Organizations should pay close attention to the information that follows to ensure that they understand the licensing model. With SQL Server 2014, the licensing for computing power is core-based and the Business Intelligence and Standard editions are available under the Server + Client Access License (CAL) model. In addition, organizations can save on cloud-based computing costs by licensing individual database virtual machines. Unfortunately, because each organization’s environment is unique, this section cannot provide an overview of how the licensing changes affect an organization’s environment. For more information on the licensing changes and how they influence your organization, please contact your Microsoft representative or partner..
Hardware and software requirements
The recommended hardware and software requirements for SQL Server 2014 vary depending on the component being installed, the database workload, and the type of processor class that will be used. Review Table 1-1 and Table 1-2 to understand the hardware and software requirements for SQL Server 2014.
Because SQL Server 2014 supports many processor types and operating systems, Table 1-1 covers the hardware requirements only for a typical SQL Server 2014 installation. Typical installations include SQL Server 2014 Standard or Enterprise running on Windows Server 2012 R2 operating systems. Readers needing information for other scenarios should reference “Hardware and Software Requirements for Installing SQL Server 2014” at http://msdn.microsoft.com/en-us/library/ms143506(v=SQL.120).aspx.
TABLE 1-1 Hardware requirements
TABLE 1-2 Software requirements
Note The server hardware has supported both 32-bit and 64-bit processors for several years, but Windows Server 2008 R2 and above is 64-bit only. Take this into serious consideration when planning SQL Server 2014 deployments.
Installation, upgrade, and migration strategies
Like its predecessors, SQL Server 2014 is available in both 32-bit and 64-bit editions. Both can be installed either with the SQL Server installation wizard through a command prompt or with Sysprep for automated deployments with minimal administrator intervention. SQL Server 2014 supports installation on the Server Core, which is an installation option of Windows Server 2008 R2 SP1 or later. Finally, database administrators also have the option to upgrade an existing installation of SQL Server or conduct a side-by-side migration when installing SQL Server 2014. The following sections elaborate on the different strategies.
In-place upgrade
An in-place upgrade is the upgrade of an existing SQL Server installation to SQL Server 2014. When an in-place upgrade is conducted, the SQL Server 2014 setup program replaces the previous SQL Server binaries on the existing machine with the SQL Server 2014 binaries. SQL Server data is automatically converted from the previous version to SQL Server 2014. This means data does not have to be copied or migrated. In the example in Figure 1-7, a database administrator is conducting an in-place upgrade on a SQL Server 2012 instance running on Server 1. When the upgrade is complete, Server 1 still exists, but the SQL Server 2012 instance and all of its data is upgraded to SQL Server 2014.
Note Organizations can perform an in-place upgrade to SQL Server 2014 if they are running SQL Server 2008 SP3 or later, SQL Server 2008 R2 SP2 or later, or SQL Server 2012 SP1 or later. Unfortunately, earlier versions of SQL Server, including SQL Server 2005, SQL Server 2000, SQL Server 7.0, and SQL Server 6.5 cannot be upgraded to SQL Server 2014.
FIGURE 1-7 An in-place upgrade from SQL Server 2012 to SQL Server 2014.
Review the information available through the following link for a detailed list of upgrades to SQL Server 2014 supported for earlier versions of SQL Server: http://msdn.microsoft.com/en-us/library/ms143393(SQL.120).aspx.
In-place upgrade pros and cons
The in-place upgrade strategy is usually easier and considered less risky than the side-by-side migration strategy. Upgrading is fairly fast, and additional hardware is not required. Because the names of the server and instances do not change during an upgrade process, applications still point to the old instances. As a result, this strategy is less time-consuming because no changes need to be made to application connection strings.
The disadvantage of an in-place upgrade is that it provides less granular control over the upgrade process. For example, when running multiple databases or components, a database administrator does not have the flexibility to choose individual items for upgrade. Instead, all databases and components are upgraded to SQL Server 2014 at the same time. In addition, the instance remains offline during the in-place upgrade, which means that if a mission-critical database or application or an important line-of-business application is running, a planned outage is required. Furthermore, if a disaster transpires during the upgrade, the rollback strategy can be a complex and time-consuming affair. A database administrator might have to install the operating system from scratch, install SQL Server, and then restore all the SQL Server data.
SQL Server 2014 high-level in-place strategy
The high-level in-place upgrade strategy for upgrading to SQL Server 2014 consists of the following steps:
1. Ensure that the instance of SQL Server to be upgraded meets the hardware and software requirements for SQL Server 2014.
2. Review the deprecated and discontinued features in SQL Server 2014. For more information, refer to “Deprecated Database Engine Features in SQL Server 2014” at http://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx.
3. Ensure that the version and edition of SQL Server that will be upgraded is supported. To review all the upgrade scenarios supported for SQL Server 2014, see “Supported Version and Edition Upgrades” at http://msdn.microsoft.com/en-us/library/ms143393(v=sql.120).aspx.
4. Run the SQL Server 2014 Upgrade Advisor. The Upgrade Advisor is a tool included with SQL Server 2014, or it can be downloaded directly from the Microsoft website. It analyzes the installed components on the SQL Server instance you plan to upgrade to ensure that the system supports SQL Server 2014. The Upgrade Advisor generates a report identifying anomalies that require fixing or attention before the upgrade can begin. The Upgrade Advisor analyzes any SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, or SQL Server 2005 components that are installed.
5. Install the SQL Server 2014 prerequisites.
6. Begin the upgrade to SQL Server 2014 by running setup.
Side-by-side migration
The term side-by-side migration describes the deployment of a brand-new SQL Server 2014 instance alongside a legacy SQL Server instance. When the SQL Server 2014 installation is complete, a database administrator migrates data from the legacy SQL Server database platform to the new SQL Server 2014 database platform. Side-by-side migration is depicted in Figure 1-8.
Note You can conduct a side-by-side migration to SQL Server 2014 by using the same server. The side-by-side method can also be used to upgrade to SQL Server 2014 on a single server.
FIGURE 1-8 Side-by-side migration from SQL Server 2012 to SQL Server 2014.
Side-by-side migration pros and cons
The greatest advantage of a side-by-side migration over an in-place upgrade is the opportunity to build out a new database infrastructure on SQL Server 2014 and avoid potential migration issues that can occur with an in-place upgrade. A side-by-side migration also provides more granular control over the upgrade process because an administrator can migrate databases and components independently of one another. In addition, the legacy instance remains online during the migration process. All of these advantages result in a more powerful server. Moreover, when two instances are running in parallel, additional testing and verification can be conducted. Performing a rollback is also easy if a problem arises during the migration.
However, there are disadvantages to the side-by-side strategy. Additional hardware or a virtual machine may be required. Applications might also need to be directed to the new SQL Server 2014 instance, and it might not be a best practice for very large databases because of the duplicate amount of storage required during the migration process.
SQL Server 2014 high-level side-by-side strategy
The high-level side-by-side migration strategy for upgrading to SQL Server 2014 consists of the following steps:
1. Ensure that the instance of SQL Server you plan to migrate meets the hardware and software requirements for SQL Server 2014.
2. Review the deprecated and discontinued features in SQL Server 2014 by referring to “Deprecated Database Engine Features in SQL Server 2014” at http://technet.microsoft.com/en-us/library/ms143729(v=sql.120).aspx.
3. Although a legacy instance that is no longer supported will not be upgraded to SQL Server 2014, it is still beneficial to run the SQL Server 2014 Upgrade Advisor to ensure that the data being migrated to SQL Server 2014 is supported and there is no possibility of a blocker preventing the migration.
4. Procure the hardware and install your operating system of choice. Windows Server 2012 is recommended.
5. Install the SQL Server 2014 prerequisites and desired components.
6. Migrate objects from the legacy SQL Server to the new SQL Server 2014 database platform.
7. Point applications to the new SQL Server 2014 database platform.
8. Decommission legacy servers after the migration is complete.