Thursday, 8 March 2012

Microsoft Dynamics CRM 2011 - What's new in SQL Server 2012

Finally SQL Server 2012 RC0 is here. A release candidate is a version of a software program that is usually not in its final form. While the version is functional, it's not quite ready to offer for sale to the general public. SQL Server 2012 code named "Denali" is the next major release of Microsoft Database Server.

These are interesting times. Microsoft has a bigger bag of goodies this time, coming out with new versions of many of its products. New upcoming versions are Windows 8, IE 10, IIS 8, .NET framework 4.5, Visual Studio 11 and SQL Server 2012.

Microsoft Dynamics CRM 2011 is built on the foundation of .NET and Microsoft SQL Server, a trusted platform that continuously leads in industry-relevant TPC-E and TPC-H performance benchmarks. SQL Server 2012 "Denali" takes Microsoft Dynamics CRM 2011 to new levels of performance, scalability and business intelligence.

Microsoft SQL Server 2012 has many enhancements. My favourite 3 are:
  • Faster Fail-over, easier Administration with SQL Server AlwaysOn
  • The concept of Partially Contained Databases
  • In-Memory Column Store ("Apollo")
Following is a list of new features and enhancements in the SQL Server 2012 RC0 release:

  • Faster Fail-over, easier Administration with SQL Server AlwaysOn 
The new integrated high availability and disaster recovery solution provides redundancy. AlwaysOn Availability Groups is a new feature that greatly enhances the capabilities of Database Mirroring and helps ensure availability of application databases. Availability Groups provides an integrated set of options including automatic and manual fail-over of a group of databases, support for up to four secondaries, fast application fail-over and automatic page repair.

  • The concept of Contained Databases
A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. Users connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server. Including all the database settings in the database enables database owners to manage all the configuration settings for the database.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) includes a first step toward contained databases, introducing partially contained databases (also known as Partial-CDB). Partially Contained Databases provide some isolation from the instance of SQL Server but do not yet provide full containment.

  • In-Memory Column Store ("Apollo")
"Apollo" introduces column store technology to the database engine, making SQL Server the first of the major general-purpose database systems to have a true column store. Apollo brings together the VertiPaq technology that was developed in Analysis Services (and is the crucial foundation for PowerPivot) and a new query execution paradigm called batch processing to provide truly astonishing speed up for common data warehouse queries. In test scenarios experiments have shown approximately 100x improvements in star join and similar queries.

  • Paging feature
SQL Server 2012 has modified ORDER BY clause to implement paging through a result set. This is done using a combination of two new keywords OFFSET and FETCH.

TSQL Query with SQL Server 2012 Paging Keywords

  • Windows Server Core Support
SQL Server is supported on Windows Server Core. By running SQL Server on Windows Server Core the OS patching can be reduced significantly which greatly reduces planned downtime (50-60% fewer OS patch reboots).

  •  Full-Text Search Performance Overhaul
Full-Text Search in Denali brings magnitudes of performance and scale advances with significantly improved query execution and concurrent index update mechanisms. Full-text search now includes properties scoped searches without requiring developers to maintain file properties (such as Author Name, Title, etc.) separately in the database. Developers can now also benefit with improved NEAR operator that allows them to specify distance in-between as well as the order of words.

  • Table Partitioning up to 15,000
Support growing data warehousing with table partitioning that now scales to 15,000 partitions.

  • Schema for Windows Groups
Database schema can now be tied to Windows Group rather than individual users to increase database compliance. It eases administration of database schema, decreases the complexity of database schema management through individual Windows users, prevent errors of assigning schema to the wrong users when users changes groups, avoids unnecessary implicit schema creation, and greatly reduces the chance of query errors when wrong schema being used.

  • Contained Database Authentication
Contained Database Authentication increases compliance by allowing users to be authenticated directly into user databases without logins. User information for login (username and password) is not stored inside master database but user databases directly. It is very secure because users can only perform DML operations inside the user databases and not database instance level operations.

  • Distributed Replay
Simplify application testing and minimize errors with application changes, configuration changes, and upgrades using new Distributed Replay. This multi-threaded replay utility provides the ability to simulate production workload scenarios testing after upgrade or configuration changes ultimately leading to protected performance during changes.

  • PowerPivot Enhancements
Customers can browse through mounds of data in a familiar way at lightning-fast speeds in Excel and also leverage the new advanced analytic capabilities such as the ability to create KPIs, Rank, Perspectives, Hierarchies and sophisticated business logic.

  • BI Semantic Model
SQL Server code-named "Denali" Analysis Services introduces the BI Semantic Model, a single model for users with multiple ways of building business intelligence (BI) solutions. With the BI Sematic Model organizations can scale from small personal BI solutions to the largest organizational BI needs. BI Semantic Model is one model for all end user experiences – reporting, analytics, scorecards, dashboards, and custom applications and offers developers flexibility of modeling experiences, richness to build sophisticated business logic and scalability for your most demanding enterprise needs.

  • Data Quality Services
A completely new service to SQL Service, Data Quality Services (DQS) helps round out end to end data management for organizations. DQS provides knowledge-driven tools customers can use allow data stewards to create and maintain a Data Quality Knowledge Base which helps improve data quality and ease data management.

  • SQL Server Developer Tools Code Named "Juneau"
Juneau unifies the development experience for database development with a single powerful environment for database development across Database, BI and web and supports SQL Server and SQL Azure. Streamline application development with a single environment for developing DB, BI, and web solutions across on-premise and cloud environments. New Visual Studio based IDE comes standalone or integrated (source control, unit testing, etc.), is released with Denali, on the web, and with Visual Studio 11 and is designed for both database and BI developers.

  • PowerShell 2.0 Support
This improvement expands on the PowerShell support delivered in SQL Server 2008 by leveraging Windows PowerShell 2.0 allowing DBAs to take advantage of the latest PowerShell features and offers the flexibility to use SQLPS.exe for all SQL and related automation scenarios.

  • Greater interoperability
SQL Server Code Name "Denali" helps customers extend heterogeneous environments by connecting to SQL Server and SQL Azure applications with support for additional industry standard APIs across varied platforms.

  • SQL Server Express Local Database Runtime (LocalDB)
In Denali, SQL Server Express will feature a new version, SQL Server Express Local Database Runtime (LocalDB). LocalDB is a new lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites.

SQL Server Code Name "Denali" is a significant product release that will help us to build and support mission critical environments. New tools and enhancements enable breakthrough insight across all levels of the organization while cloud-ready technologies for application symmetry across server, private and public cloud help stay agile for the future.

I hope this blog about 'Microsoft Dynamics CRM 2011 - What's new in SQL Server 2012' was informative. Please feel free to leave your comments.

No comments:

Post a Comment