Using Distributed Replay to load test your SQL Server–Part 2

After we have configured the Distributed Replay as explained in the previous post, we can use the Distributed Replay environment to create the load on the SQL Server.

Data Collection from Source Server

We need to collect some data from the main production server and then we can use the collected data to create load on the new SQL Server. To do this, follow the steps below.

1. Create “C:\DRDemo” folder on the controller (which is ServerN1 in our test.)
2. Open SQL Server 2012 Profiler Trace utility on ServerN1
3. Select File –> New Trace –> Connect to ServerN1. Note that we are using ServerN1 to simulate the workload as well for this test. In the real world we would be capturing the trace from a server which is currently in production.
4. Select the TSQL – Replay template within “Use the template” drop down menu and run the trace.
5. Run “C:\DRDemo\StartWorkload.cmd” to bring some load to SQL Server 2008 R2 system. The system is not production so the script is used to create some load on the production.
6. After the script is completed, save the trace file within “C:\DRDemo”. For the example it is “C:\DRDemo\ServerN1_SQL2008R2_Trace.trc”.
7. Now the trace from source server is ready.

Preprocess the source Trace file

The source trace file will be used to prepare files which are used by distributed clients to create load on the new SQL Server. Run the command below using command prompt on the controller (ServerN1) to prepare the files for the distributed clients.

c:\DRDemo>dreplay preprocess -i "C:\DRDemo\ServerN1_SQL2008R2_Trace.trc" -d "C:\DRPreProcess"

The screenshot above shows that an error occurred. The important part is “…and that the console user has the proper permissions to access the controller service”
The login account is sqladmin on the server. It can be checked using “whoamI” command using the command prompt on ServerN1. The system event log on ServerN1 says that

Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {6DF8CB71-153B-4C66-8FC4-E59301B8011B} and APPID {961AD749-64E9-4BD5-BCC8-ECE8BA0E241F} to the user AYSQLTEST\sqladmin SID (S-1-5-21-2826735731-136765897-3671058344-1125) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

To fix this issue, we need to the steps below on ServerN1:

1. ServerN1 - Run and type dcomcnfg and Component Services will be opened.
2. Find DReplayController (Console Root –> Component Services –> Computers –> My Computer – >DCOM Config -> DReplayController)
3. Open the properties of DReplayController and select Security tab
4. Edit “Launch and Activation Permissions” and grant “sqladmin” domain user account “Local Activation” and “Remote Activation” permissions.
5. Edit “Access Permissions” and grant “sqladmin” domain user account “Local Access” and “Remote Access”.
6. Restart controller and client services like below
   NET STOP "SQL Server Distributed Replay Controller"
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Controller"
   NET START "SQL Server Distributed Replay Client"
7. Run the command again:

dreplay preprocess -i "C:\DRDemo\ServerN1_SQL2008R2_Trace.trc" -d "C:\DRPreProcess"

BTW, these steps might seem familiar – we had used them in the first post for the service account itself. Once the permission issue is fixed, the preprocess command generated 2 files within “C:\DRPreProcess” folder.

Replay against SQL Server 2012 using clients

Preprocess phase has been completed. Controller will take 2 files “ReplayEvents.irf”, “TraceStats.xml” and copy them to the clients (ServerN1,ServerN3) and replay them on the clients against target server which is SQL Server 2012 called ServerN2\SQL2012. Here is the command to do so:

dreplay replay -s ServerN2\sql2012rc0 -w ServerN1,ServerN3 -f 10 -o -d "C:\DRPreProcess"

The -o parameter will save the trace output within “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir”
At the same time open a profiler trace and connect to target server which is ServerN2\SQL2012 collect profiler trace. Then you can compare the trace file from source server and trace file from target server and check some query performance. You can refer to this article for more details.
That’s it! Hopefully we have covered the basics of Distributed Replay and how you can use it to reproduce a workload on a test system. Please leave your feedback and questions in the comments area below!

Using Distributed Replay to load test your SQL Server–Part

Have you decided to migrate SQL Server to a newer version? Would you like to test the load on the new SQL Server? How can you create a load on the new SQL Server? Do you need to develop a multi threaded application to simulate load on SQL Server? Using a new feature of SQL Server 2012, called Distributed Replay, it is so simple to simulate load on a SQL Server for testing purposes.
Before proceeding, it may be useful to review what Distributed Replay is all about.

Installation

There should be one distributed replay controller and between 1 and 16 distributed replay clients. Distributed Replay Controller and Distributed Replay Client can be installed separately or together on any server. If you decide to install Distributed Replay on a Server, in addition to this Management Tools should be installed to the same SQL Server because dreplay.exe file (Distributed Replay Administration Tool) comes with the client tools as highlighted below.

Test Setup

We are using the following computers for our test.

• ServerN1: this will be our controller; it has the following components installed:
• Distributed Replay Client
• Distributed Replay Controller
• Management Tools
• SQL Server 2008 R2 Database Engine Services
• ServerN2: this will be our target system. It has SQL Server 2012 Database Engine Services installed.
• ServerN3: this is a Distributed Replay Client

The controller name is set as ServerN1 for ServerN1 and ServerN3
For your information, here are the installation folders for each server:
ServerN1: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient and
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController
ServerN2: Nothing installed related to Distributed Replay because those features has not been selected.
ServerN3: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient

Service Accounts

Separate service accounts can be configured for “Distributed Replay Client” and “Distributed Replay Controller”. For our example “sqlservice” domain user account has been used.
After setting new service accounts "SQL Server Distributed Replay Controller" and "SQL Server Distributed Replay Client" services has been restarted using the command prompt on ServerN1 and ServerN3 like below.
ServerN1:

NET STOP "SQL Server Distributed Replay Controller"
NET STOP "SQL Server Distributed Replay Client"
NET START "SQL Server Distributed Replay Controller"
NET START "SQL Server Distributed Replay Client"

ServerN3:

NET STOP "SQL Server Distributed Replay Client"
NET START "SQL Server Distributed Replay Client"

After the restart, check the last log file of the Distributed Replay Client within the folder “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\Log”. You may find that an error “Failed to connect controller with error code 0x80070005” is reported. Error code 0x80070005 means “Access is denied”.

Remediation: ServerN1

1. ServerN1 - Start -> Run and type dcomcnfg and Component Services will be opened.
2. Find DReplayController (Console Root –> Component Services –> Computers –> My Computer –> DCOM Config -> DReplayController)
3. Open the properties of DReplayController and select Security tab
4. Edit “Launch and Activation Permissions” and grant “sqlservice” domain user account “Local Activation” and “Remote Activation” permissions.
5. Edit “Access Permissions” and grant “sqlservice” domain user account “Local Access” and “Remote Access”.
6. Add “sqlservice” domain user account within “Distributed COM Users” group.
7. Restart controller and client services like below
   NET STOP "SQL Server Distributed Replay Controller"
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Controller"
   NET START "SQL Server Distributed Replay Client"
8. Check the Distributed Replay Client log file and see the message “Registered with controller ServerN1”

ServerN3

1. Restart client service like below
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Client"
2. Check the Distributed Replay Client log file and see the message can be seen like “Failed to connect controller with error code 0x800706BA”. ServerN3 client may not connect to controller which is running on ServerN1.

Firewall Exceptions

As per this link, we need to set the firewall exceptions correctly. Apply the steps below on each remote Distributed Replay Client:

1. Open Windows Firewall with Advanced Security
2. Click Inbound Rules – Right click and select New Rule
3. Rule Type: Select Program and click next
4. Program: Select “This program path” and browse “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.exe” and click next
5. Action: Select “Allow the connection” and click next
6. Profile: Keep all selected such as Domain, Private and Public (usually Domain should be enough) and click next
7. Name: Type “Allow Distributed Replay Client” and click finish

Apply the steps below on each Distributed Replay Controller:

1. Open Windows Firewall with Advanced Security
2. Click Inbound Rules – Right click and select New Rule
3. Rule Type: Select Program and click next
4. Program: Select “This program path” and browse “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\DReplayController.exe” and click next
5. Action: Select “Allow the connection” and click next
6. Profile: Keep all selected such as Domain, Private and Public (usually Domain should be enough) and click next
7. Name: Type “Allow Distributed Replay Controller” and click finish

Apply the steps below on ServerN3

1. Restart client service like below
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Client"
2. Check the Distributed Replay Client log file and see the message “Registered with controller ServerN1”. ServerN3 client will connect to controller properly.

Conclusion

At this stage, our Distributed Replay clients are setup and working correctly. In the next part of this article, we will show you how to use the DREPLAY utility to replay a workload on the server.

Edited and posted by Arvind Shyamsundar, MSPFE Editor.

SQL 2012 - SQL Server Distributed Replay

SQL Server Distributed Replay

Benefits of Distributed Replay

---

Similar to SQL Server Profiler, you can use Distributed Replay to replay a captured trace against an upgraded test environment. Unlike SQL Server Profiler, Distributed Replay is not limited to replaying the workload from a single computer.
Distributed Replay offers a more scalable solution than SQL Server Profiler. With Distributed Replay, you can replay a workload from multiple computers and better simulate a mission-critical workload.
The Microsoft SQL Server Distributed Replay feature can use multiple computers to replay trace data and simulate a mission-critical workload. Use Distributed Replay for application compatibility testing, performance testing, or capacity planning.

When to Use Distributed Replay

---

SQL Server Profiler and Distributed Replay provide some overlap in functionality.
You may use SQL Server Profiler to replay a captured trace against an upgraded test environment. You can also analyze the replay results to look for potential functional and performance incompatibilities. However, SQL Server Profiler can only replay a workload from a single computer. When replaying an intensive OLTP application that has many active concurrent connections or high throughput, SQL Server Profiler can become a resource bottleneck.
Distributed Replay offers a more scalable solution than SQL Server Profiler. Use Distributed Replay to replay a workload from multiple computers and better simulate a mission-critical workload.

Distributed Replay Concepts

---

The following components make up the Distributed Replay environment:

• Distributed Replay administration tool: A console application, DReplay.exe, used to communicate with the distributed replay controller. Use the administration tool to control the distributed replay.
• Distributed Replay controller: A computer running the Windows service named SQL Server Distributed Replay controller. The Distributed Replay controller orchestrates the actions of the distributed replay clients. There can only be one controller instance in each Distributed Replay environment.
• Distributed Replay clients: One or more computers (physical or virtual) running the Windows service named SQL Server Distributed Replay client. The Distributed Replay clients work together to simulate workloads against an instance of SQL Server. There can be one or more clients in each Distributed Replay environment.
• Target server: An instance of SQL Server that the Distributed Replay clients can use to replay trace data. We recommend that the target server be located in a test environment.

The Distributed Replay administration tool, controller, and client can be installed on different computers or the same computer. There can be only one instance of the Distributed Replay controller or client service that is running on the same computer.
The following figure shows the SQL Server Distributed Replay physical architecture:

 

SQL Server 2012 - User-Defined Server Roles

SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)
Fixed server roles are provided for convenience and backward compatibility. Assign more specific permissions whenever possible.
SQL Server provides nine fixed server roles. The permissions that are granted to the fixed server roles cannot be changed. Beginning with SQL Server 2012, you can create user-defined server roles and add server-level permissions to the user-defined server roles.
You can add server-level principals (SQL Server logins, Windows accounts, and Windows groups) into server-level roles. Each member of a fixed server role can add other logins to that same role. Members of user-defined server roles cannot add other server principals to the role.

Syntax

CREATE SERVER ROLE role_name [ AUTHORIZATION server_principal

Permissions

---

Requires CREATE SERVER ROLE permission or membership in the sysadmin fixed server role.

Also requires IMPERSONATE on the server_principal for logins, ALTER permission for server roles used as the server_principal, or membership in a Windows group that is used as the server_principal.

This will fire the Audit Server Principal Management event with the object type set to server role and event type to add.

When you use the AUTHORIZATION option to assign server role ownership, the following permissions are also required:

• To assign ownership of a server role to another login, requires IMPERSONATE permission on that login.
• To assign ownership of a server role to another server role, requires membership in the recipient server role or ALTER permission on that server role.

Examples

---

A. Creating a server role that is owned by a login

The following example creates the server role buyers that is owned by login BenMiller.

USE master; CREATE SERVER ROLE buyers AUTHORIZATION BenMiller; GO

B. Creating a server role that is owned by a fixed server role

The following example creates the server role auditors that is owned the securityadmin fixed server role.

USE master; CREATE SERVER ROLE auditors AUTHORIZATION securityadmin; GO

To drop a server role

The following example drops the server role purchasing.







 



DROP SERVER ROLE purchasing;
GO

D. To view role membership

To view role membership, use the Server Role (Members) page in SQL Server Management Studio or execute the following query:



 



SELECT SRM.role_principal_id, SP.name AS Role_Name, 
SRM.member_principal_id, SP2.name  AS Member_Name
FROM sys.server_role_members AS SRM
JOIN sys.server_principals AS SP
    ON SRM.Role_principal_id = SP.principal_id
JOIN sys.server_principals AS SP2 
    ON SRM.member_principal_id = SP2.principal_id
ORDER BY  SP.name,  SP2.name

E. To view role membership

To determine whether a server role owns another server role, execute the following query:







SELECT SP1.name AS RoleOwner, SP2.name AS Server_Role
FROM sys.server_principals AS SP1
JOIN sys.server_principals AS SP2
    ON SP1.principal_id = SP2.owning_principal_id 
ORDER BY SP1.name ;

 

SQL 2012 - Columnstore Indexes

Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries. Typical data warehousing workloads involve summarizing large amounts of data. The techniques typically used in data warehousing and decision support systems to improve performance are pre-computed summary tables, indexed views, OLAP cubes, and so on. Although these can greatly speed up query processing, these techniques can be inflexible, difficult to maintain, and must be designed specifically for each query problem.
For example, consider a fact table F1 with dimension key columns dk1 and dk2. Let M be an aggregate function such as SUM. Instead of calculating M over column dk1 every time that a query referring to M(dk1) is run, a summary table F2(dk1, M) can be created and used so the result can be pre-computed and the query can execute faster. However, if a new query referring to M(dk2) is needed, a new summary table F3(dk2, M) with this information has to be created. When the number of columns in a table increases and with many possible functions, this approach becomes difficult to maintain and does not easily cover all the queries that are needed.
This overhead can be significant for the user. By using SQL Server columnstore indexes, users can reduce the overhead of the other solutions. Columnstore indexes also enable queries to compute the results quickly so that pre-computation is not required.
The key characteristics of SQL Server column store technology are as follows:

• Columnar data format – Unlike the traditional row based organization of data (called rowstore format), in columnar database systems such as SQL Server with columnstore indexes, data is grouped and stored one column at a time. SQL Server query processing can take advantage of the new data layout and significantly improve query execution time.
• Faster query results – Columnstore indexes can produce faster results for the following reasons:
  
  • Only the columns needed must be read. Therefore, less data is read from disk to memory and later moved from memory to processor cache.
  • Columns are heavily compressed. This reduces the number of bytes that must be read and moved.
  • Most queries do not touch all columns of the table. Therefore, many columns will never be brought into memory. This, combined with excellent compression, improves buffer pool usage, which reduces total I/O.
  • Advanced query execution technology processes chunks of columns called batches in a streamlined manner, reducing CPU usage.
• Key columns – There is no concept of key columns in a columnstore index so the limitation on the number of key columns in an index (16) does not apply to columnstore indexes.
• Clustered index key – If a base table is a clustered index, all columns in the clustering key must be present in the nonclustered columnstore index. If a column in the clustering key is not listed in the create index statement, it will be added to the columnstore index automatically.
• Partitioning – Columnstore indexes works with table partitioning. No change to the table partitioning syntax is required. A columnstore index on a partitioned table must be partition-aligned with the base table. Therefore a nonclustered columnstore index can only be created on a partitioned table if the partitioning column is one of the columns in the columnstore index.
• Record Size – The index key record size limitation of 900 bytes also does not apply to columnstore indexes.
• Query processing – Along with the columnstore index, SQL Server introduces batch processing to take advantage of the columnar orientation of the data. The columnstore structure and batch processing both contribute to performance gains, but investigating performance issues might be more complex than if only one factor were involved.
• Table cannot be updated – For SQL Server 2012, a table with a columnstore index cannot be updated.

 

Syntax of ColumnStore Index


CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) }
           | filegroup_name
           | "default"
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

<column_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
}
  

Examples:

Creating a simple nonclustered index

CREATE TABLE SimpleTable
(ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);
GO

Creating a simple nonclustered index using all options

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON,
    MAXDOP = 2)
ON "default"
GO

More about columnstore Indexes....

Indexes can be created on a temporary table. When the table is dropped or the session ends, the indexes are dropped.
The common business data types can be included in a columnstore index. The following data types can be included in a columnstore index.

• char and varchar
• nchar and nvarchar (except varchar(max) and nvarchar(max))
• decimal (and numeric) (Except with precision greater than 18 digits.)
• int, bigint, smallint, and tinyint
• float (and real)
• bit
• money and smallmoney
• All date and time data types (except datetimeoffset with scale greater than 2)

The following data types cannot be included in a columnstore index.

• binary and varbinary
• ntext, text, and image
• varchar(max) and nvarchar(max)
• uniqueidentifier
• rowversion (and timestamp)
• sql_variant
• decimal (and numeric) with precision greater than 18 digits
• datetimeoffset with scale greater than 2
• CLR types (hierarchyid and spatial types)
• xml

Basic Restrictions
A columnstore index:

• Cannot have more than 1024 columns.
• Cannot be clustered. Only nonclustered columnstore indexes are available.
• Cannot be a unique index.
• Cannot be created on a view or indexed view.
• Cannot include a sparse column.
• Cannot act as a primary key or a foreign key.
• Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead. (You can use ALTER INDEX to disable and rebuild a columnstore index.)
• Cannot be created by with the INCLUDE keyword.
• Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting would eliminate many of the performance benefits.

Columnstore indexes cannot be combined with the following features:

• Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)
• Replication
• Change tracking
• Change data capture
• Filestream

SQL Server 2012 videos

SQL 2012 Videos from Youtube.

 

Introduction to SQL 2012

 

 

Introduction to Always On Features of SQL 2012

 

 

How to Install SQL 2012

 

 

 

SQL Server 2012 Express Edition Download Information

Express edition

SQL Server Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications.

Download Link

Easy to get started. Free to use.

Take advantage of the same powerful database engine as the other versions of SQL Server in a version great for redistribution and embedding, free! SQL Server Express includes 10GB of storage per database, easy backup and restore functionality and compatibility with all editions of SQL Server and Windows Azure SQL Database.

 

Things You Can Do

 

+ Simplify Development Skills

• Leverage Existing Skills

  • Get started today developing on SQL Server with SQL Server Data Tools. Take advantage of existing Transact-SQL skills, and incorporate technologies, including the Microsoft ADO.NET Entity Framework and LINQ. Develop applications faster through deep integration with Visual Studio, Visual Web Developer, and SQL Server Management Studio.

  Improve Developer Productivity

  • Increase developer productivity and simplify deployment through integration with Visual Studio and Visual Web Developer. Take advantage of advanced capabilities, including rich table and query designers, full integration with Server Explorer, and drag-and-drop IDE support for rapidly building data-bound applications.

  Tune the Design Process

  • Manage SQL Server 2012 Express databases with SQL Server Management Studio Express. Connect to local SQL Server 2012 Express databases and manage objects with full Object Explorer integration. Write, execute, and test queries by using visual query plans that provide hints to tune queries and access management and maintenance options.

+ Develop Sophisticated Application

• Leverage Existing Skills

• Get started today developing on SQL Server with SQL Server Data Tools. Take advantage of existing Transact-SQL skills, and incorporate technologies, including the Microsoft ADO.NET Entity Framework and LINQ. Develop applications faster through deep integration with Visual Studio, Visual Web Developer, and SQL Server Management Studio.

• Improve Developer Productivity

• Increase developer productivity and simplify deployment through integration with Visual Studio and Visual Web Developer. Take advantage of advanced capabilities, including rich table and query designers, full integration with Server Explorer, and drag-and-drop IDE support for rapidly building data-bound applications.

• Tune the Design Process

• Manage SQL Server 2012 Express databases with SQL Server Management Studio Express. Connect to local SQL Server 2012 Express databases and manage objects with full Object Explorer integration. Write, execute, and test queries by using visual query plans that provide hints to tune queries and access management and maintenance options.

+ Extend Any Data, Anywhere

• Access Data Anywhere

• Manage data infrastructures with policy-based management and apply policies to all instances. Reduce maintenance time by defining common policies for operations, such as query optimizations, naming conventions, backup, restore, and index management. Apply policies to multiple servers for consistent management across the enterprise.

• Extend Any Data

• Take advantage of SQL Server Express’s rich interoperability, by using the SQL Server driver for PHP, the Microsoft JDBC driver for Java implementation or the SQL Server ODBC Driver for Linux.

• Synchronize Your Data

• Keep data synchronized with subscriber support for SQL Server transactional and merge replication. Take advantage of advanced conflict resolution capabilities to build robust business solutions, and synchronize data automatically with support for change tracking and the Microsoft Sync Framework.

+ New in SQL Server Express 2012: SQL Server Express LocalDB

• SQL Server Express LocalDB

• LocalDB is a new lightweight deployment option for SQL Server Express Edition with fewer prerequisites that installs very quickly. This makes it ideal for developers who need an embedded SQL Server database for desktop applications or tools. LocalDB has all of the same programmability features as SQL Server Express, but runs in-process with applications and not as a service.

• Integrated

• LocalDB is designed to be bundled with and function as an integrated part of tools or desktop applications that need a lightweight local database runtime. This includes tools like SQL Server Data Tools, or custom developed desktop applications, or tools that need a lightweight single-user database runtime that supports full Transact-SQL functionality.

• Simple

• Apps that use LocalDB simply open a file. Once a file is opened, you get SQL Server functionality when working with that file, including things like ACID transaction support. The app that opens the file has exclusive use. It’s not intended for multi-user scenarios or to be used as a server. (If you need that, you should install SQL Server Express.)

• Easy Acquisition

• LocalDB is easy to download, install, and manage. It installs as a small shared component instead of a service.

Please leave a comment.

What's New in SQL Server 2012?

SQL Server 2012, the latest version, adds new high availability and disaster recovery solutions through AlwaysOn clusters and availability groups, xVelocity in-memory storage for extremely fast query performance, rapid data exploration and scalable business intelligence through Power View and tabular modeling in Analysis Services, and new data management capability with Data Quality Services.

1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.

2. Windows Server Core Support -- If you don't know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

3. Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

4. User-Defined Server Roles -- DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

5. Enhanced Auditing Features -- Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

6. BI Semantic Model -- This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

7. Sequence Objects -- For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

8. Enhanced PowerShell Support -- Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

9. Distributed Replay -- Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

10. PowerView -- You may have heard of this under the name "Project Crescent" it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.

11. SQL Azure Enhancements -- These don't really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also Azure data sync allows a better hybrid model of cloud and on-premise solutions

12. Big Data Support -- I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

SQL 2012 is a big step forward for Microsoft -- the company is positioning itself to be a leader in availability and in the growing area of big data. As a database professional, I look forward to using SQL 2012 to bring new solutions to my clients.