Top 10 New Features in SQL 2012
Our new SQL 2012 just released. I get asked all the time why should I upgrade to the latest. There are 100’s of reasons with this one, I tried to pick the best 10. More info here: http://social.technet.microsoft.com/wiki/contents/articles/3783.what-s-new-in-sql-server-2012.aspx
1) AlwaysOn SQL Server Failover Cluster Instances
- Multi-subnet failover clusters: A SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets. These subnets can be in the same location or in geographically dispersed sites. Clustering across geographically dispersed sites is sometimes referred to as Stretch clusters. As there is no shared storage that all the nodes can access, data should be replicated between the data storage on the multiple subnets. With data replication, there is more than one copy of the data available. Therefore, a multi-subnet failover cluster provides a disaster recovery solution in addition to high availability. For more information, see SQL Server Multi-Subnet Clustering.
- Flexible failover policy for cluster health detection: In a SQL Server failover cluster instance, only one node can own the cluster resource group at a given time. The client requests are served through this node for that failover cluster instance. In the case of a failure, the group ownership is moved to another node in the failover cluster. This process is called failover. The improved failure detection introduced in SQL Server 2012, and addition of failure condition level property allows you to configure a more flexible failover policy. For more information, see Failover Policy for Failover Cluster Instances.
- Indirect checkpoints: The indirect checkpoints feature provides a database-specific alternative to automatic checkpoints, which are configured by a server property. Indirect checkpoints implements a new checkpointing algorithm for the Database Engine. This algorithm provides a more accurate guarantee of database recovery time in the event of a crash or a failover than is provided by automatic checkpoints. To ensure that database recovery does not exceed allowable downtime for a given database, you can specify the maximum allowable downtime for that database.
Starting with SQL Server 2012, Windows PowerShell is no longer installed by SQL Server Setup. Windows PowerShell 2.0 is a pre-requisite for installing SQL Server 2012. If PowerShell 2.0 is not installed or enabled on your computer, you can enable it by following the instructions on the Windows Management Framework page. For more information about SQL Server PowerShell, see SQL Server PowerShell.
SQL Server 2012 now uses the new Windows PowerShell 2.0 feature called modules for loading the SQL Server components into a PowerShell environment. Users import the sqlps module into PowerShell, and the module then loads the SQL Server snap-ins. For more information, see Run Windows PowerShell from SQL Server Management Studio.
The sqlps Utility is no longer a PowerShell 1.0 mini-shell; it now starts PowerShell 2.0 and imports the sqlps module. This improves SQL Server interoperability by making it easier for PowerShell scripts to also load the snap-ins for other products. The sqlps utility is also added to the list of deprecated features starting in SQL Server 2012.
The SQL Server PowerShell provider includes two new cmdlets: backup-sqldatabase and restore-sqldatabase. For more information, use the get-help cmdlet after loading in the sqlps module.
3) Data-tier Applications
- The data-tier application (DAC) upgrade has been changed to an in-place process that alters the existing database to match the schema defined in the new version of the DAC. This replaces the side-by-side upgrade process, which created a new database with the new schema definitions. The Upgrade a Data-Tier Application wizard has been updated to perform an in-place upgrade. The Upgrade method of the DacStore type is now deprecated, and replaced with a new IncrementalUpgrade method. Upgrades are also supported for DACs deployed to SQL Azure. For more information, see Upgrade a Data-tier Application.
- In addition to just extracting a schema definition as a new DAC package file, you can now export both the schema definition and data from a database as a DAC export file. You can then import the file to create a new database with the same schema and data. For more information, see Export a Data-tier Application and Import a BACPAC File to Create a New User Database.
- Data-tier applications now support many more objects than in SQL Server 2008 R2. For more information, see DAC Support For SQL Server Objects and Versions.
4) Server Modes for Analysis Services Instances: Multidimensional, Tabular, and SharePoint
This release adds a server mode concept to an Analysis Services installation. An instance is always installed in one of three modes that determines the memory management and storage engines used to query and process data. Server modes include Multidimensional and Data Mining, SharePoint, and Tabular. For more information, see Determine the Server Mode of an Analysis Services Instance
5) Integration Services Performance
Reduced Memory Usage by the Merge and Merge Join Transformations
Microsoft has made the Integration Services Merge and Merge Join transformations more robust and reliable. This is achieved by reducing the risk that these components will consume excessive memory when the multiple inputs produce data at uneven rates. This improvement helps packages that use the Merge or Merge Join transformations to use memory more efficiently.
Microsoft has also provided new properties and methods for developers of custom data flow components to implement a similar solution in their own components. This improvement makes it more feasible to develop a robust custom data flow component that supports multiple inputs. For more information, see Developing Data Flow Components with Multiple Inputs.
6) Answering the Need with (DQS) Data Quality Services
Data quality is not defined in absolute terms. It depends upon whether data is appropriate for the purpose for which it is intended. DQS identifies potentially incorrect data, and provides you with an assessment of the likelihood that the data is in fact incorrect. DQS provides you with a semantic understanding of the data so you can decide its appropriateness. DQS enables you to resolve issues involving incompleteness, lack of conformity, inconsistency, inaccuracy, invalidity, and data duplication.
DQS provides the following features to resolve data quality issues.
- Data Cleansing: the modification, removal, or enrichment of data that is incorrect or incomplete, using both computer-assisted and interactive processes. For more information, see Data Cleansing.
- Matching: the identification of semantic duplicates in a rules-based process that enables you to determine what constitutes a match and perform de-duplication. For more information, see Data Matching.
- Reference Data Services: verification of the quality of your data using the services of a reference data provider. You can use reference data services from Windows Azure Marketplace DataMarket to easily cleanse, validate, match, and enrich data. For more information, see Reference Data Services in DQS.
- Profiling: the analysis of a data source to provide insight into the quality of the data at every stage in the knowledge discovery, domain management, matching, and data cleansing processes. Profiling is a powerful tool in a DQS data quality solution. You can create a data quality solution in which profiling is just as important as knowledge management, matching, or data cleansing. For more information, see Data Profiling and Notifications in DQS.
- Monitoring: the tracking and determination of the state of data quality activities. Monitoring enables you to verify that your data quality solution is doing what it was designed to do. For more information, see DQS Administration.
- Knowledge Base: Data Quality Services is a knowledge-driven solution that analyzes data based upon knowledge that you build with DQS. This enables you to create data quality processes that continually enhances the knowledge about your data and in so doing, continually improves the quality of your data.
7) Replication Support for AlwaysOn Availability Groups
Replication supports the following features on Availability groups:
- A publication database can be part of an availability group. The publisher instances must share a common distributor. Transaction, merge, and snapshot replication are supported.
In an AlwaysOn Availability Group an AlwaysOn secondary cannot be a publisher. Republishing is not supported when replication is combined with AlwaysOn.
Peer-To-Peer (P2P), bi-directional, reciprocal transactional publications, and Oracle Publishing are not supported.
- A database that is enabled for Change Data Capture (CDC) can be part of an availability group.
- A database enabled for Change Tracking (CT) can be part of an availability group.
Four new stored procedures provide replication support for AlwaysOn.
- sp_redirect_publisher (Transact-SQL)
- sp_get_redirected_publisher (Transact-SQL)
- sp_validate_redirected_publisher (Transact-SQL)
- sp_validate_replica_hosts_as_publishers (Transact-SQL)
For more information about replication with AlwaysOn, see Configure Replication for AlwaysOn Availability Groups (SQL Server), Maintaining an AlwaysOn Publication Database (SQL Server), and Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server).
8) Power View
Power View, a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition, is an interactive data exploration, visualization, and presentation experience. It provides drag-and-drop ad hoc reporting for business users such as data analysts, business decision makers, and information workers. Power View reports are in a new file format, RDLX.
Power View expands on the self-service BI capabilities delivered with PowerPivot for Excel and PowerPivot for SharePoint by enabling customers to visualize and interact with modeled data in a meaningful way, using interactive visualizations, animations, and smart querying. It is a browser-based Silverlight application launched from within SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.
Based on Tabular Models
With Power View, customers start from an SQL Server 2012 Analysis Services (SSAS) tabular model to build their reports. Tabular models use metadata to present an underlying data source to end users, with predefined relationships and behaviors, in terms they understand. For more information about tabular models, see What's New (Analysis Services) and Tabular Modeling (SSAS Tabular).
Coexists with Report Builder
Power View does not replace Report Builder, the report authoring tool for richly designed operational reports. Power View addresses the need for Web-based, ad hoc reporting. It co-exists with the latest version of Report Builder, which also ships in SQL Server 2012.
For more information, see the following:
- Power View (SSRS)
- Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012 (http://go.microsoft.com/fwlink/?LinkId=219108)
- Tutorial: Create a Sample Report in Power View (http://go.microsoft.com/fwlink/?LinkId=221204)
- 9) What's New (Master Data Services)
Use Excel to Manage Master Data
You can now manage your master data in the Master Data Services Add-in for Excel. You can use this add-in to load a filtered set of data from your Master Data Services database, work with the data in Excel, and then publish the data back to the database. If you are an administrator, you can also use the add-in to create new entities and attributes. It is easy to share shortcut query files, which contain information about the server, the model, version, entity, and any applied filters. You can send the shortcut query file to another user via Microsoft Outlook. You can refresh data in the Excel worksheet with data from the server, refreshing either the entire Excel worksheet or a contiguous selection of MDS-managed cells in the worksheet. For more information, see Master Data Services Add-in for Microsoft Excel.
Match Data before Loading
Before adding more data to MDS, you can now confirm that you are not adding duplicate records. The MDS Add-in for Excel uses SQL Server Data Quality Services (DQS) to compare two sources of data: the data from MDS and the data from another system or spreadsheet. DQS provides suggestions for updating your data, along with the percent of confidence that the changes are correct. For more information, see Data Quality Matching in the MDS Add-in for Excel.
Load Data into MDS Using Entity-Based Staging
Loading data into MDS has become easier. You can now load all members and attribute values for an entity at one time. Previously you had to load members and attributes in separate batches. See Importing Data (Master Data Services).
10)SQL Server Install
- Server Core Installation: Starting with SQL Server 2012, we can install SQL Server on Windows Server 2008 R2 Server Core SP1. For more information, see Install SQL Server 2012 on Server Core.
- SQL Server Data Tools (Formerly called Business Intelligence Development Studio): Starting with SQL Server 2012, you can install SQL Server Data Tools (SSDT) which provides an IDE for building solutions for the Business Intelligence components: Analysis Services, Reporting Services, and Integration Services.
SSDT also includes “Database Projects”, which provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. Database developers can use the enhanced Server Object Explorer in Visual Studio to easily create or edit database objects and data, or execute queries.
- SQL Server multi-subnet clustering: You can now configure a SQL Server failover cluster using clustered nodes on different subnets. For more information, see SQL Server Multi-Subnet Clustering.
- SMB file share is a supported storage option: System databases (Master, Model, MSDB, and TempDB), and Database Engine user databases can be installed on a file share on an SMB file server. This applies to both SQL Server stand-alone and SQL Server failover cluster installations. For more information, see Install SQL Server with SMB fileshare as a storage option.
