Microsoft SQL Server 12 is a transitional upgrade of database software: scalability, integration with MS Windows Azure, and Big Data. Organizational enterprise will have to evaluate and address a number of operational requirements to improve availability, scalability, and return on investment.
The Resource Governor manages performance on different workloads. Hardware resources can be scaled out specific to the attributes of the data and application.
Virtualization technologies also can be applied.
Preparation will be required for integrating MS SQL Server and MS Windows Azure with connector software to Hadoop.
MS SQL Server 2012
Performance and scale enhancements have been built into the MS SQL Server 2012 database engine for large workloads and multi-tenant environments. The trend in both private and public and multi-tenant environments is server consolidation.
MQ SQL Server 2012 includes:
|User-Defined Server Roles
||A database administrator can create a role, which has read/write access on every database on the server or any other custom server wide role.
|AlwaysOn Availability Groups
||Extends database mirroring and failovers to multiple databases in groups instead of individually. Secondary copies will be readable and can be used for database backups.
||A read-only index designed for use with data warehouse queries. Data is grouped and stored in a flat, compressed column index; this results in more efficient I/O and memory utilization on large queries.
||Audit is available in all editions of SQL Server. Users can define audit specifications for writing custom events into the audit log. Filtering can be used for choosing which events to write to the log.
MS SQL Server - Resource and Performance Management
The Resource Governor is used to monitor and control the way different workloads utilize CPU and memory resources on SQL Server instances in a hosting or private cloud environment:
dynamic management views, multiple workloads, resource pools, and
resource capacity limits.
Resource Governor provides the capability to monitor and manage CPU and memory utilization in multi-tenant environments. Resource limits can be reconfigured in real-time. In an environment where multiple distinct workloads are present on the same server, Resource Governor enables workloads to be differentiated and shared resources to be allocated as they are requested based on the limits that have been specified.
A resource pool in the Resource Governor represents the physical resources of the server; each resource pool can contain one or more workload groups. Resource Governor can manage up to 64 resource pools.
Resource Governor enables a capacity limit to be set on CPU resource usage. It allows for the assignment and monitoring of resources and managing the enforcement of restrictions on resource usage.
Scalability is the capability of an application to efficiently utilize additional resources in order to perform for more useful work. There is scaleup and scaleout. Scaleup is the most common way for databases to scale. As resources become constrained, a new server with additional processors and memory is purchased. No significant changes are required of the database.
Scaleout expands to multiple servers rather than a single, bigger server. In some cases, the redundancy offered by a scaleout solution also is useful from an availability perspective.
Scaling up a SQL Server environment across multiple systems involves partitioned databases, federation, and applying installation-specific resources.
Maximize SQL Server performance components: memory, disk storage, network adapters, and CPU.
Implement 10 Gigabit Ethernet adapters; there should be one network adapter for every major use.
Maximize performance by utilizing multiple network adapters to provide multiple paths to the network.
Analyze and optimize server processor performance and capacity by adding additional processors or upgrading a processor.
Match workloads with the available hardware resources.
MS SQL Server 12 also provides software based scaling out techniques. Scalable shared databases can be created on a SAN: Storage Area Network and have up to eight MS SQL Server instances run on
multiple servers, attach to the database, and handle queries. Scalable shared databases work only if the database is attached as a read-only database. Each SQL Server instance maintains its own database locks in memory and no instance will
have knowledge of the other instances' locks.
MS SQL Server can employ linked servers and distributed queries for querying remote database objects as if they were local. A scaled out database can be presented as a single large database to an application.
SQL queries table names will include the name of the linked server where the data is located. This makes linked servers a viable scaleout option when changing the application is difficult to do. Synonyms can be used
to expose a four-part name that includes the server name as a single name. Queries which address a local table can address a remote table without changing the query.
DPVs: Distributed Partitioned Views are used by SQL Server to support transparent scaling out of partitioned data. The data in a table is partitioned based on a partitioning key among tables in distributed databases.
MS Azure and SQL Server
Microsoft Windows Azure SQL Database is a cloud-based relational database service built on SQL Server technologies and which is hosted in Microsoft data centers on hardware that is owned and
maintained by Microsoft. MS Windows Azure SQL Database exposes a TDS: tabular data stream interface for Transact-SQL-based database access. This allows database applications to use an Azure SQL database the same
way as MS SQL Server.
The maximum size of an Azure database has been increased to 150 gigabytes. MS Windows Azure data synchronization is a hybrid model of cloud and on-premise solutions. There is a backup for the Windows Azure datastore.
The MS Windows Azure SQL Reporting platform is a cloud-based reporting service built on SQL Server Reporting Services technologies. Host operational reports can use existing data in the Windows Azure SQL database. Operational reports are generated from the RDL: Report Definition Language. The Visual Studio Report Viewer AJAX control can be embedded in on-premise web applications or cloud-based Windows Azure applications. It is used for adding rich reporting to custom applications. A web browser can be used for viewing reports hosted in SQL Reporting. The Reporting Services URL access syntax is used to render a specific report and specify a report format - Excel or PDF.
MS Windows Azure SQL Database is a PaaS: Platform as a service. There are major differences in administration between Windows Azure SQL Database and MS SQL Server; Azure abstracts the logical
administration from the physical administration. Aside from the client personnel managing the base level administration tasks such as logins, users, and roles, Microsoft Corporation administers the physical hardware: hard drives, servers, and storage. This approach provides a cloud-based multi-tenant database service that offers high-availability, scalability, security, and self-healing.
Windows Azure SQL Database automatically replicates all data to provide high availability. It also manages load balancing and transparent fail-over. Analysis Services, Replication, and Service Broker are not currently provided as services on the Azure platform. Since Windows Azure SQL Database performs the physical administration, any statements and options that attempt to directly manipulate physical
resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It also is not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.
Many SQL Server Transact-SQL statements have parameters that provide for the specification of file groups or physical file paths. These types of parameters are not supported in Windows Azure SQL Database because they have dependencies on the physical configuration. Windows Azure SQL Database does not support all of the features and data types found in SQL Server.
Guidelines and Best Practices
There will be trade-offs and decisions associated with resource sharing and scaleout strategies. Some scaleout strategies work without requiring any changes to the application; some require minor changes to queries and stored procedures, and others may require a significant rewriting of code. Scaleout can be implemented by partitioning data among multiple databases in order that a database server handles a portion of the data; it can have a significant impact on performance.
Data interdependence and coupling is when parts of the database are used by different applications; the database can be divided and allocated along application boundaries. Each application will then have processing dedicated to the data it uses.
Scaling out data that must be updated, but has a relatively low update frequency should be handled by replication. Instead of several database engines accessing a single copy of the database, there will be multiple copies of the database
and peer to peer replication. This option provides scaleout, while allowing updates to the data. Replication is used to propagate changes to all copies of the data.
Data that is updated very frequently often is difficult to replicate very efficiently, the overhead resulting from replicating updates limits the scalability of the replicated copies.
Windows Azure Virtual Machines provide the capability to extend enterprise networks and migrate existing applications to Windows Azure. Virtualization is used to improve performance and run multiple workloads on a single host computer. Databases that require less than an entire physical server can be moved into a
MS SQL Server can be used to operate a virtual-machine guest application and separate databases across multiple SQL Server instances, each hosted in different virtual machines. MS SQL Server provides flexibility for implementing live migration technologies for moving virtual machines from host to host and rearranging the virtual machines to utilize available hardware based on