Microsoft Cloud Authors: Kevin Benedict, Pat Romanski, Liz McMillan, Lori MacVittie, Elizabeth White

Related Topics: Microsoft Cloud, Microservices Expo, Machine Learning , Agile Computing

Microsoft Cloud: Article

Top Ten Reasons to Use SQL 2012

I get asked all the time why should I upgrade to the latest version

Our new SQL 2012 just released. I get asked all the time why should I upgrade to the latest. There are hundreds of reasons with this one, I tried to pick the best 10. More info here.

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.

2. Windows PowerShell
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.

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:

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.

More Stories By John Weston

John Weston is a Cloud IT Pro Evangelist for Microsoft.http://aka.ms/syscntr During the last ten years he has spoken to thousands of IT Professionals across the country. Before joining Microsoft full time, he spent six years as an MCT, training people at a local college and getting new Microsoft hires up to speed. He holds more certifications than he can remember, but the list definitely includes MCITP, MCSE, MCDBA, MCT, and CCNA. Before becoming and MCT, Weston co-owned a software development and consulting firm that was a Microsoft Partner based in Dallas. His primary blog includes http://blogs.technet.com/b/jweston.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

IoT & Smart Cities Stories
DXWorldEXPO LLC announced today that Telecom Reseller has been named "Media Sponsor" of CloudEXPO | DXWorldEXPO 2018 New York, which will take place on November 11-13, 2018 in New York City, NY. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
To Really Work for Enterprises, MultiCloud Adoption Requires Far Better and Inclusive Cloud Monitoring and Cost Management … But How? Overwhelmingly, even as enterprises have adopted cloud computing and are expanding to multi-cloud computing, IT leaders remain concerned about how to monitor, manage and control costs across hybrid and multi-cloud deployments. It’s clear that traditional IT monitoring and management approaches, designed after all for on-premises data centers, are falling short in ...
The deluge of IoT sensor data collected from connected devices and the powerful AI required to make that data actionable are giving rise to a hybrid ecosystem in which cloud, on-prem and edge processes become interweaved. Attendees will learn how emerging composable infrastructure solutions deliver the adaptive architecture needed to manage this new data reality. Machine learning algorithms can better anticipate data storms and automate resources to support surges, including fully scalable GPU-c...
A valuable conference experience generates new contacts, sales leads, potential strategic partners and potential investors; helps gather competitive intelligence and even provides inspiration for new products and services. Conference Guru works with conference organizers to pass great deals to great conferences, helping you discover new conferences and increase your return on investment.
Poor data quality and analytics drive down business value. In fact, Gartner estimated that the average financial impact of poor data quality on organizations is $9.7 million per year. But bad data is much more than a cost center. By eroding trust in information, analytics and the business decisions based on these, it is a serious impediment to digital transformation.
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
SYS-CON Events announced today that Silicon India has been named “Media Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Published in Silicon Valley, Silicon India magazine is the premiere platform for CIOs to discuss their innovative enterprise solutions and allows IT vendors to learn about new solutions that can help grow their business.
DXWorldEXPO LLC announced today that "IoT Now" was named media sponsor of CloudEXPO | DXWorldEXPO 2018 New York, which will take place on November 11-13, 2018 in New York City, NY. IoT Now explores the evolving opportunities and challenges facing CSPs, and it passes on some lessons learned from those who have taken the first steps in next-gen IoT services.
SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5–7, 2018, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buye...