Top Ten Reasons to Use MS SQL Server 2012 Part 2

Directions Training is well known for its Microsoft SQL Server training to IT professionals. This MS SQL Server training post is in continuation from Part 1 Top Ten Reasons To Use Microsoft SQL Server 2012 (Reasons 1 – 5).

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.

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:

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.

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.

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.

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.
  • 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.
  • 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 Microsoft SQL Server training information visit Directions Training or follow us on Twitter @DirectionsTrain or call toll-free 1-855-575-8900.

Directions Twitter
Directions RSS
Directions LinkedIn
Directions Facebook
Directions Training