Benefits of SQL Server 2012

By Lisa Gecko

SQL Server 2012 was released earlier this year and has many new improvements in database management, data integration, business intelligence and integration with cloud-based computing.

Significant updates include performance improvements, high availability and organizational security. These included the AlwaysOn feature – a new high availability and disaster recovery solution which enables faster application failover during downtime. Also upgraded is the Column Store Index: A new indexing feature available to cater to faster query performance while dealing with huge number of records. This aids in performing analysis against data warehouses with dimensional modeling without implementing an OLAP solution. This feature is based on the Vertipaq technology that was introduced in PowerPivot for Excel. Scaling up has been improved with the use of 15K partitions for large databases. There is now even faster performance for full-text search capabilities with provision to search and index the document metadata. SQL 2012 also contains authentication for organizational security in order to avoid security issues while migrating databases between servers (on-premise or in a cloud). Finally, there is the new Distributed Replay feature, which can be used to test production workloads on test environments for changes in underlying schemas or hardware.

In addition to the performance improvements, there are also features that provide useful insights into organization data with appropriate tools which can cater to both business users as well as IT professionals. This makes your data more reliable and consistent to provide accurate information. One example is the Business Intelligence Semantic Model (BISM) for scalable self-service BI. This modeling technique was used in PowerPivot in SQL Server 2008 R2 but now it is being implemented as a universal approach for BI tools like SSRS, SSAS and Sharepoint. It supports the tabular model alongside the already existing UDM technique. So now we can have SSAS cubes using the tabular approach to cater to Personal BI users. And there is Power View, which is an ad-hoc reporting option available in Sharepoint with Silverlight features using which the user can quickly create visually enhanced reports. These reports are created on top of BISM models. This is integrated with PowerPivot enhancements to create effective tabular models which will be more aligned to business scenarios. Powerpivot now provides options to create KPIs, hierarchies, Measures, perspectives along with a diagram view of the underlying model. The diagrammatic representation is similar to Data Source Viewer option available while creating Analysis Services cubes. In conjunction with these features, there is a new statistical semantic search which can be used to extract useful information from unstructured documents that are stored in SQL Server databases. It also makes use of the features of full-text search capability of SQL Server and adds features to find key phrases in a document, find related documents based on the key phrases. Reporting services is now also provided as a shared service inside Sharepoint so all the management and administrative activities can be handled using Sharepoint configuration tools. And Data Quality Services is a new component in SQL Server which can be used in conjunction with Master Data Services to implement MDM solution. It analyzes your data and builds knowledge base which can be used as a reference for data cleansing, matching and profiling activities. Additionally, DQS features can be embedded inside your package and executed while integrating information from various sources. To further improve business intelligence, a Master Data Services is now available as an add-in in Excel to perform functions such as loading master data or validating the existing data and publishing it to the centrally located master data store.

Further features included with SQL Server 2012 simplify the process of implementing databases in a cloud as well as expedite the movement of databases between on-site and cloud servers. To empower this, contained databases has been introduced in SQL Server 2012 to specifically address the issues faced when moving databases between in-house and cloud servers. SQL Server Data Tools is a development platform for creating database solutions for variety of target platforms including 2005 and 2008 versions and also for SQL Azure. It has options for online and offline development wherein the changes can be applied directly to the server instance. Also the database projects can be realized as a data-tier application (DAC) which is a concept introduced in SQL Server 2008 R2 for easy deployment on cloud-based servers.