Professional Microsoft SQL Server 2012 Administration

Read an Excerpt

Professional Microsoft SQL Server 2012 Administration

John Wiley & Sons

Chapter One

WHAT'S IN THIS CHAPTER

* New Important Features in SQL Server 2012

* How New Features Relate to Data Professionals Based on Their Role

* SQL Server Architecture Overview

* Editions of SQL Server and How They Affect the Data Professional

SQL Server 2012 offers a fresh look at how organizations and their developers, information workers, and executives use and integrate data. A tremendous number of new features and improvements focus on extending SQL Server more into SharePoint, improving self-service options, and increasing data visualization, development, monitoring and exploration capabilities. This chapter is not a deep dive into the architecture but provides enough information to give you an understanding of how SQL Server operates.

SQL SERVER 2012 ECOSYSTEM

This thing called SQL Server has become quite large over the past few releases. This first section provides a review of the overall SQL Server ecosystem, which is now referred to as less of a product and more of an ecosystem, because there are so many interactions with other products and features that drive increased performance, scale, and usability. Following are three major areas of focus for the release of SQL Server 2012:

* Performance: Features such as improved core support, columnstore indexes, compression enhancements, and Always On make this the most powerful, available release of SQL Server.

* Self Service: With new data exploration tools such as Power View, improvements in SQL Azure Business Intelligence (BI,), data quality and master data offerings, and PowerPivot for SharePoint enable users to be closer to the data at all times and to seek and deliver intelligence more rapidly than ever.

* Integration and collaboration: New integrations for reporting services, PowerPivot, and claims authentication in SharePoint 2010 provide a strong foundation for the significant focus on self-service in this release. The new BI semantic model approach extends into the cloud as well with reporting services now in SQL Azure and more features promised to come.

NEW IMPORTANT FEATURES IN 2012

There are a number of new things that you will be excited about, depending on your role and how you use SQL Server. This section touches on the features you should be checking out and getting your hands on. Many of these features are quick to get up and running, which is exciting for those readers who want to begin delivering impact right away.

Production DBA

Production DBAs are a company's insurance policy that the production database won't go down. If the database does go down, the company cashes in its insurance policy in exchange for a recovered database. The Production DBA also ensures that the server performs optimally and promotes database changes from development to quality assurance (QA) to production. New features include the following:

* AlwaysOn: Availability functionality including availability groups and the ability to file over databases in groups that mimic applications. This includes new readable secondary servers, a big enhancement.

* FileTable: Additional file-based data storage

* Extended Events: A new functionality built into SQL Server 2012 that provides lightweight and extensive tracing capability

* Improved functionality and stability in SQL Server Management Studio (now in Visual Studio 2010 shell)

* Distributed replay capabilities

* Improved debugging functionality including expression support and breakpoint validation.

* Columnstore indexes for optimizing large data volumes

* Improved statistics algorithm for very large databases

* Improved compression and partitioning capabilities

Development DBA

Since the release of SQL Server 2000, there has been a trend away from full-time Production DBAs, and the role has merged with that of the Development DBA. The trend may have slowed, though, with laws such as Sarbanes-Oxley, which require a separation of power between the person developing the change and the person implementing the change. In a large organization, a Production DBA may fall into the operations department, which consists of the network of administrators and Windowssupport administrators. In other instances, a Production DBA may be placed in a development group. This removes the separation of power that is sometimes needed for regulatory reasons.

Development DBAs play a traditional role in an organization. They wear more of a developer's hat and are the development staff's database experts and representatives. This administrator ensures that all stored procedures are optimally written and that the database is modeled correctly, both physically and logically. The development DBA also may be the person who writes the migration processes to upgrade the database from one release to the next. The Development DBA typically does not receive calls at 2:00 A.M like the Production DBA might for failed backups or similar problems. Things development DBAs should be excited about in this new release include the following:

* New TSQL and spatial functionality

* SQL Server data tools: A new TSQL development environment integrated with Visual Studio

* New DAX expression language that provides Excel-like usability with the power of multidimensional capabilities

* New tabular model for Analysis Services: Provides in-memory OLAP capabilities in a quick time to value format

The Development DBA typically reports to the development group and receives requests from a business analyst or another developer. In a traditional sense, Development DBAs should never have modification access to a production database. They should, however, have read-only access to the production database to debug in a time of escalation.

Business Intelligence DBA and Developer

The Business Intelligence (BI) DBA is a new role that has evolved due to the increased capabilities of SQL Server. In SQL Server 2012, BI grew to be an incredibly important feature set that many businesses could not live without. The BI DBA or developer is an expert at these features. This release is a treasure trove of new BI functionality including new enhancements to Reporting Services Integration, data exploration tools such as Power View, and a dramatic set of enhancements that make PowerPivot easier and more accessible than ever. Additionally, the new Tabular model in SSAS delivers the ability to create new PowerPivot-like "in memory" BI projects to SharePoint for mass user consumption.

Development BI DBAs specialize in the best practices, optimization, and use of the BI toolset. In a small organization, a Development BI DBA may create your SSIS packages to perform Extract Transform and Load (ETL) processes or reports for users. In a large organization, developers create the SSIS packages and SSRS reports. The Development BI DBA is consulted regarding the physical implementation of the SSIS packages and Analysis Services (SSAS) cubes. Development BI DBAs may be responsible for the following types of functions:

* Model\consult regarding Analysis Services cubes and solutions

* Create reports using Reporting Services

* Create\consult around ETL using Integration Services

* Develop deployment packages to be sent to the Production DBA

These responsibilities, coupled with these following new features make for an exciting time for the BI-oriented folks:

* Rapid data discovery with Power View and PowerPivot

* Managed Self-Service BI with SharePoint and BI Semantic Model

* Credible, consistent data with Data Quality Services and Master Data Management capabilities

* Robust DW solutions with Parallel Data Warehouse and Reference Architectures

SQL SERVER ARCHITECTURE

Many people just use SQL Server for its classic use: to store data. This release of SQL Server focuses on expanding the capabilities that were introduced in SQL Server 2008 R2, which was largely a self-service business intelligence and SharePoint feature release. The additional functionality in SQL Server 2012 not only enables but encourages users to go beyond simply storing data in SQL Server; this release can now be the center of an entire data strategy. New tools such as Power View and PowerPivot quickly integrate on top of SQL Server and can provide an easy user interface (UI) for SQL Server and other systems' data. This section covers the primary file types in SQL Server 2012, file management, SQL Client, and system databases. It also covers an overview of schemas, synonyms, and Dynamic Management Objects. Finally, it also goes into the new SQL Server 2012 data types.

Database Files and Transaction Log

The architecture of database and transaction log files remains relatively unchanged from prior releases. Database files serve two primary purposes depending on their type. Data files hold the data, indexes, and other data support structure within the database. Log files hold the data from committed transactions to ensure consistency in the database.

Database Files

A database may consist of multiple filegroups. Each filegroup must contain one or more physical data files. Filegroups ease administrative tasks for a collection of files. Data files are divided into 8KB data pages, which are part of 64KB extents. You can specify how full each data page should be with the fill factor option of the create/alter index T-SQL command. In SQL Server 2012 Enterprise Edition, you continue to have the capability to bring your database partially online if a single file is corrupt. In this instance, the DBA can bring the remaining files online for reading and writing, and users receive an error if they try to access the other parts of the database that are offline.

In SQL 2000 and before, the largest row you could write was 8060 bytes. The exceptions to this limit are text, ntext, image, varchar(max), varbinary(max), and nvarchar(max) columns, which may each be up to 2 gigabytes and are managed separately. Beginning with SQL 2005, the 8KB limit applies only to those columns of fixed length. The sum of fixed-length columns and pointers for other column types must still be less than 8060 bytes per row. However, each variable-length column may be up to 8KB in size allowing for a total row size of well over 8060 bytes. If your actual row size exceeds 8060 bytes, you may experience some performance degradation because the logical row must now be split across multiple physical 8060-byte rows.

Transaction Log

The purpose of the transaction log is to ensure that all committed transactions are persisted in the database and can be recovered, either through rollback or point in time recovery. The transaction log is a write-ahead log. As you make changes to a database in SQL Server, the data is written to the log, and then the pages that need to be changed are loaded into memory (specifically into the write buffer portion of the buffer pool). The pages are then dirtied by having the changes written to them. Upon checkpoint, the dirty pages are written to disk, making then now clean pages which no longer need to be part of the write buffer. This is why you may see your transaction log grow significantly in the middle of a long-running transaction even if your recovery model is set to simple. (Chapter 17, "Backup and Recovery" covers this in much more detail.)

SQL Native Client

The SQL Native Client is a data-access method that shipped with SQL Server 2005 and was enhanced in 2012 and is used by both OLE DB and ODBC for accessing SQL Server. The SQL Native Client simplifies access to SQL Server by combining the OLE DB and ODBC libraries into a single access method. The access type exposes these features in SQL Server:

* Database mirroring

* Always On readable secondary routing

* Multiple Active Result Sets (MARS)

* Snapshot isolation

* Query notification

* XML data type support

* User-defined data types (UDTs)

* Encryption

* Performing asynchronous operations

* Using large value types

* Performing bulk copy operations

* Table-value parameters

* Large CLR user-defined types

* Password expiration

In these features, you can use the feature in other data layers such as Microsoft Data Access Components (MDAC), but it takes more work. MDAC still exists, and you can use it if you don't need some of the new functionality of SQL Server 2008\2012. If you develop a COM-based application, you should use SQL Native Client; and if you develop a managed code application like in C#, you should consider using the .NET Framework Data Provider for SQL Server, which is robust and includes the SQL Server 2008\2012 features as well.

Standard System Databases

The system databases in SQL Server are crucial, and you should leave them alone most of the time. The only exceptions to that rule is the model database, which enables you to deploy a change such as a stored procedure to any new database created, and tempdb, which may need to be altered to help with scaling your workload. The following sections go through the standard system databases in detail.

If certain system databases are tampered with or corrupted, you run the risk that SQL Server will not start. The master database contains all the stored procedures and tables needed for SQL Server to remain online.

The Resource Database

SQL Server 2005 added the Resource database. This database contains all the read-only critical system tables, metadata, and stored procedures that SQL Server needs to run. It does not contain any information about your instance or your databases because it is written to only during an installation of a new service pack. The Resource database contains all the physical tables and stored procedures referenced logically by other databases. You can find the database by default in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn, and there is only one Resource database per instance.

The use of drive C: in the path assumes a standard setup. If your machine is set up differently, you may need to change the path to match your setup. In addition, the .MSSQLSERVER is the instance name. If your instance name is different, use your instance name in the path.

In SQL Server 2000, when you upgraded to a new service pack, you needed to run many long scripts to drop and re-create system objects. This process took a long time to run and created an environment that couldn't be rolled back to the previous release after the service pack. In SQL Server 2012, when you upgrade to a new service pack or hot fix, a copy of the Resource database overwrites the old database. This enables you to both quickly upgrade your SQL Server catalog and roll back a release.

The Resource database cannot be seen through Management Studio and should never be altered unless you're under instruction to do so by Microsoft Product Support Services (PSS). You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource. Typically, a DBA runs simple queries against it while connected to any database, instead of having to connect to the resource database directly. Microsoft provides some functions that enable this access. For example, if you were to run this query while connected to any database, it would return your Resource database's version and the last time it was upgraded:

SELECT serverproperty('resourceversion') ResourceDBVersion, serverproperty('resourcelastupdatedatetime') LastUpdateDate

Do not place the Resource database on an encrypted or compressed drive. Doing this may cause upgrade or performance issues.

The master Database

The master database contains the metadata about your databases (database configuration and file location), logins, and configuration information about the instance. You can see some of the metadata stored in master by running the following query, which returns information about the databases that exist on the server:

SELECT * FROM sys.databases

The main difference between the Resource and master databases is that the master database holds data specific to your instance, whereas the Resource database just holds the schema and stored procedures needed to run your instance but does not contain any data specific to your instance.

You should rarely create objects in the master database. If you create objects here, you may need to make frequent master db backups.

(Continues...)

---

Excerpted from Professional Microsoft SQL Server 2012 Administration by Adam Jorgensen Steven Wort Ross LoForte Brian Knight Copyright © 2012 by John Wiley & Sons, Ltd. Excerpted by permission of John Wiley & Sons. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Read More