Infrastructure at your Service

Stéphane Savorgnano

Microsoft TechEd Madrid: Windows Server 2012 R2, High Availability, SQL Server Data Tools

Let’s talk about my third day at TechEd 2013.
I started with a session about:

Windows Server 2012 R2 and High Availability

The goal of High Availability is to deal with possible points of failure in your infrastructure.
There are multiple ways to manage HA on the market:

  • Server
    • Server redundancy deployments
    • Run server inside a clustered VM
    • Backup
    • Monitor via a SCOM Management Pack
  • Database
    • SQL Server 2012 AlwaysOn
    • Mirroring, Replication, Backup
    • Run SQL Server inside clustered VM
    • Using disk replication
    • Monitor with SCOM MP
  • And lots of other ones (Orchestrator, Management Server…)

For better High Availability, you should also use multi-site for your nodes and follow those principles:

  • Nodes in different physical location to survive the crash of an entire datacenter
  • Stretch sites over a large distance
  • Storage should be at both sites with replication
  • Automatic failover is recommended
  • Synchronized cluster

It was an interesting refresh followed by an otherone no less interesting about:

New features of Windows Server 2012 R2

Appliance Installation: What has changed?

  • Easy setup of 4-node SQL and Hyper-V clusters
  • Fast deployment from power-on to HA in 30 minutes with minimal steps guided by a worflow
  • Configuration of 4-nodes from one node in a single console
  • Extension using WPF framework to enable unique OEM tasks, branding and customization

SKU (Stock Keeping Unit) Selection: how to choose the right version

If you select a free solution you will not have a guest OS license, it is the case with Hyper-V Server 2012 R2.

So if you need those licenses you will have to choose from either Windows Server 2012 R2 Standard Edition or Datacenter Edition for more licenses.

b2ap3_thumbnail_blog1_20130627-114513_1.jpg

Windows Server 2012 R2 can now also have Shared VHDX for guest clusters which was not possible with version 2012.

b2ap3_thumbnail_blog2_20130627-114514_1.jpg

The whole notion of quorum has changer a bit in Windows Server 2012 R2.
Quorum change

  • Removed legacy concepts of Quorum modes (node majority, node and disk majority, node and file share witness majority)
  • We can define which nodes have a quorum vote (configurable from 1 vote to 0 vote)
  • Disk: always configure a witness disk with Windows Server 2012 R2, clustering will find the best moment to use it
  • Witness vote dynamically/automatically adjusted based on cluster membership with dynamic quorum

All those new settings have one goal: to keep the cluster alive even if just one node is still alive or if a site does not have the quorum majority. We will have to test all those new features to have a better view of the different advantages.

SQL Server Data Tools for Visual Studio 2012

I also went to an interesting session about SQL Server Data Tools for Visual Studio 2012.

SQL SERVER DATA TOOLS (SSDT) is completely integrated into Visual Studio 2012 and is used to maintaining full cycle management of your databases. It works across all versions of SQL Server.

This tool makes it possible to manage your SQL Server from Visual Studio 2012. To do that, just open Visual Studio 2012, select New Projet, Template then SQL Server and choose SQL Server Database Project.
If SSDT is not installed on your computer you will not be able to go further, so if you want to test it, install it :roll:

You will now have to import a database, connect to an instance of SQL Server, select a database and click import. When the database has been imported you can browse all over your database structures with tables, stored procedures….

You are able to open each table, modifying tables, indexes and save your modification. To deploy those modifications you have to build your solution, publish it and then the modifications will be reflected on your SQL Server database.

The speaker also demonstrated Data-Tier Application:

A Data-Tier Application (DAC) is a logical database management entity that defines all SQL Server objects – such as tables, views, and instance objects – associated with a user’s database. It is a self-contained unit of SQL Server database deployment that enables data-tier developers and DBAs to package SQL Server objects into a portable artifact called a DAC package, or .dacpac file.

You will be able to create a datapac file from Visual Studio 2012 after you created an SQL Server Database project on the Solution Explorer.

Data-tier application is a good way to transport database within a single dacpac file which is fully compressed.
For a database of 512MB the dacpac file will be 17MB big.

The publish action incrementally updates a database schema to match the schema of a source .dacpac file. If the database does not exist on the server, the publish operation creates it.

To create this new database on an SQL Server instance, just open Management Studio, right click on Database and select “Deploy a data-tier application”, select your dacpad file. Deploying your dacpac file will create a new database based on this dacpac file.

So dacpac is a good solution to manage database modifications between a team of programmers working on the same project and enhancing the same database.

Personally, I have not yet installed SSDT on my SQL Server 2012, but I will do it to test those functionalities.

 

Leave a Reply


5 × one =

Stéphane Savorgnano
Stéphane Savorgnano

Consultant