May 17, 2014
by James van den Berg Leave a comment

#SQL 2014 Guest Cluster with Shared VHDX on #Hyperv 2012 R2 Cluster for #WAPack

Our configuration and requirements before we begin the Guest SQL 2014 Cluster with Shared VHDX on a Hyper-V Cluster :

Microsoft Hyper-V 2012 R2 Cluster is running with Cluster Shared Volumes.
We made Two virtual Machines with Microsoft Windows Server 2012 R2 called SQL01 and SQL02
Networking Two NICS : One on the Production Switch and NIC two on the Heart Beat Switch.

Windows Server 2012 R2 Failover Cluster Manager

Create and enable a shared virtual hard disk

This step shows how to create and then share a virtual hard disk that is in the .vhdx file format. Repeat this step for each shared .vhdx file that you want to add. For example, you may want to add one or more shared disks that will act as data disks, and a separate shared disk that you can designate as the disk witness for the guest failover cluster.

To create a virtual hard disk to share

In Failover Cluster Manager, expand the cluster name, and then click Roles.
In the Roles pane, right-click the virtual machine on which you want to add a shared virtual hard disk, and then click Settings.
In the virtual machine settings, under Hardware, click SCSI Controller.
In the details pane, click Hard Drive, and then click Add.
In the Hard Drive details pane, under Virtual hard disk, click New.The New Virtual Hard Disk Wizard opens.
On the Before You Begin page, click Next.
On the Choose Disk Format page, accept the default format of VHDX, and then click Next.

Note

To share the virtual hard disk, the format must be .vhdx.
On the Choose Disk Type page, select Fixed size or Dynamically expanding, and then click Next.

Note

A differencing disk is not supported for a shared virtual hard disk.
On the Specify Name and Location page, do the following:
1. In the Name box, enter the name of the shared virtual hard disk.
2. In the Location box, enter the path of the shared storage location.For Scenario 1, where the shared storage is a CSV disk, enter the path:C:\ClusterStorage\VolumeX, where C:\ represents the system drive, and X represents the desired CSV volume number.
  For Scenario 2, where the shared storage is an SMB file share, specify the path:
  
  \\ServerName\ShareName, where ServerName represents the client access point for the Scale-Out File Server, and ShareName represents the name of the SMB file share.
3. Click Next.
On the Configure Disk page, accept the default option of Create a new blank virtual hard disk, specify the desired size, and then click Next.

Note
To share the virtual hard disk, the format must be .vhdx.

Note
A differencing disk is not supported for a shared virtual hard disk.

On the Completing the New Virtual Hard Disk Wizard page, review the configuration, and then click Finish.

Important
If the virtual machine is running, do not click Apply in the virtual machine settings before you continue to the next procedure. If you do click Apply on a running virtual machine, you will need to stop the virtual machine or remove and then add the virtual hard disk without clicking Apply.

To share the virtual hard disk (You already did in the first step, see picture above)

In the virtual machine settings, under SCSI Controller, expand the hard drive that you created in the previous procedure.
Click Advanced Features.

In the details pane, select the Enable virtual hard disk sharing check box.

Note
If the check box appears dimmed and is unavailable, you can do either of the following: Remove and then add the virtual hard disk to the running virtual machine. When you do, ensure that you do not click Apply when the New Virtual Hard Disk Wizard completes. Instead, immediately configure sharing in Advanced Features. Stop the virtual machine, and then select the Enable virtual hard disk sharing check box.

Note

If the check box appears dimmed and is unavailable, you can do either of the following:

Remove and then add the virtual hard disk to the running virtual machine. When you do, ensure that you do not click Apply when the New Virtual Hard Disk Wizard completes. Instead, immediately configure sharing in Advanced Features.
Stop the virtual machine, and then select the Enable virtual hard disk sharing check box.

Click Apply, and then click OK.
Add the virtual hard disk to each virtual machine that will use the shared .vhdx file. When you do, repeat this procedure to enable virtual hard disk sharing for each virtual machine that will use the disk.

Tip
To share a virtual hard disk by using Windows PowerShell, use the Add-VMHardDiskDrive cmdlet with the –ShareVirtualDisk parameter. You must run this command as an administrator on the Hyper-V host for each virtual machine that will use the shared .vhdx file. For example, the following command adds a shared virtual hard disk (Data1.vhdx) on volume 1 of CSV to a virtual machine that is named VM1.`Add-VMHardDiskDrive -VMName VM1 -Path C:\ClusterStorage\Volume1\Data1.vhdx -ShareVirtualDisk` The following command adds a shared virtual hard disk (Witness.vhdx) that is stored on an SMB file share (\\Server1\Share1) to a virtual machine that is named VM2.`Add-VMHardDiskDrive -VMName VM2 -Path \\Server1\Share1\Witness.vhdx -ShareVirtualDisk`

Tip

To share a virtual hard disk by using Windows PowerShell, use the Add-VMHardDiskDrive cmdlet with the –ShareVirtualDisk parameter. You must run this command as an administrator on the Hyper-V host for each virtual machine that will use the shared .vhdx file. For example, the following command adds a shared virtual hard disk (Data1.vhdx) on volume 1 of CSV to a virtual machine that is named VM1.Add-VMHardDiskDrive -VMName VM1 -Path C:\ClusterStorage\Volume1\Data1.vhdx -ShareVirtualDisk The following command adds a shared virtual hard disk (Witness.vhdx) that is stored on an SMB file share (\\Server1\Share1) to a virtual machine that is named VM2.Add-VMHardDiskDrive -VMName VM2 -Path \\Server1\Share1\Witness.vhdx -ShareVirtualDisk

For the Second Guest clusternode you will add the same VHDX files you created on the first Guest clusternode and share them also like on the first clusternode.

So now we have a Shared Quorum Disk and a Shared Data Disk

The next steps :

Bring the disks Online on the first Guest node with Disk Management
Install Failover Cluster on both nodes.
Make a two node Cluster
Install a Microsoft SQL 2014 Guest Cluster.

Shared VHDX disks are Online

Install Microsoft Failover Cluster.

Cluster02 is Online with both Shared VHDX disks for the Guest SQL 2014 Cluster

Install Microsoft SQL 2014 Server for Clustering.

Microsoft SQL 2014 Server is installed on the Guest Cluster on top of an Hyper-V 2012 R2 Cluster

Guest SQL 2014 Cluster is running on Hyper-V 2012 R2.

So now we are ready for installing Microsoft SPF and Windows Azure Pack for Windows Server 2012 R2 🙂

May 8, 2014
by James van den Berg Leave a comment

E-Book : Introducing Microsoft #SQL Server 2014 Technical Overview

SQL Server 2014, the latest complete information platform from Microsoft, embodies the new era of Microsoft’s Cloud OS, which provides organizations and customers with one consistent platform for infrastructure, apps and data that span customer data centers, hosting service-
provider data centers, and the Microsoft public cloud. The benefits that customers experience with a consistent platform include common development, management, data, identity, and virtualization, no matter where an application is being run.
SQL Server 2014 also offers organizations the opportunity to efficiently protect, unlock, and scale their data across the desktop, mobile devices, data centers, and a private, public, or hybrid cloud. Furthermore, SQL Server 2014 builds on the success of SQL Server 2012 by making a strong impact on organizations worldwide with significant new capabilities that are tightly aligned to the trends transforming the IT industry, including Microsoft’s Cloud OS. SQL Server provides mission-critical performance for the most demanding database applications while delivering the highest forms of security, scalability, high availability, and support. SQL Server 2014’s mission is to deliver faster insights into big data, small data—all data—and, most importantly, deliver business intelligence in a consumable manner through familiar tools such as Microsoft Excel.
Finally, SQL Server 2014 enables new unique hybrid-cloud solutions based on the Cloud OS. These solutions can positively affect an organization’s bottom line and allow an organization to create innovative solutions for its database applications.

You can download this Free Microsoft E-book on Introducing SQL Server 2014 Technical Overview here

May 1, 2014
by James van den Berg Leave a comment

System Center Management Pack for #SQL Server 2014 #sysctr

Instance Summary DashBoard

The System Center Management Pack for SQL Server 2014 enables the discovery and monitoring of SQL Server 2014 Database Engines, Databases, SQL Server Agents and other related components. This Management Pack is designed to run by Operations Manager 2007 R2 (except dashboards), Operations Manager 2012 or Operations Manager 2012 R2.
The monitoring provided by this management pack includes performance, availability, and configuration monitoring, as well as performance and events data collection. All monitoring workflows have predefined thresholds and complimentary knowledge base articles. You can integrate the monitoring of SQL Server 2014 components into your service-oriented monitoring scenarios.
In addition to health monitoring capabilities, this management pack includes dashboards, diagram views, state views, performance views, alert views and diagnostic tasks that enable near real-time diagnostics and remediation of detected issues.
Note: This management pack does not depend on SQL Server Library management pack. You do not need to import it to enable the monitoring of SQL Server 2014.
Note: This management pack is only for SQL Server 2014. Please use this link to download the Operations Manager Management Pack for SQL Server 2005/2008/2012.

Feature Summary
The following list gives an overview of features introduced by System Center Operations Manager Management Pack for SQL Server 2014. Please refer to the SQL Server 2014 Management Pack Guide for more details.

Discovery and monitoring of SQL Server 2014 roles like DB Engine, Integrations Services.
Discovery of SQL Server 2014 components: Databases, SQL Agent and SQL jobs.
The management pack introduces 100+ monitors and 380+ rules to provide a deep monitoring of SQL Server 2014. Please refer to the SQL Server 2014 Management Pack Guide for the full list of monitoring scenarios and workflow inventory.
Monitoring of SQL Server 2014 AlwaysOn:
- Health roll-up from availability database to availability replicas.
- Detailed knowledge for each related monitoring workflow.
Seamless integration with SQL Server 2014 policy based management (PBM):
- Rollup of policy execution health to the health of related SCOM entity.
Monitoring of SQL Server In-Memory OLTP.
Reports for long-term analysis of different problematic areas related to SQL Server 2014, such as SQL Server lock analysis, top deadlocked databases, SQL Server service pack levels, user connection activity. Also, generic reports from the Microsoft Generic Report Library can be used to review availability and performance of objects discovered by System Center Operations Manager Management Pack for SQL Server 2014.
Low-privilege monitoring is supported for both stand-alone installations and clustered environments, except PBM.

New features

Support of In-Memory OLTP Discovery and Monitoring
Generic Presentation Management Pack has been introduced. This management pack defines version-independent group-based views and dashboard.
Summary dashboard for SQL Server 2014 Instances with drill-down to SQL 2014 Database dashboard.
Summary dashboard for SQL Server 2014 Databases.
Space monitoring now supports databases hosted on Cluster Shared Volumes, SMB Shares and Azure BLOBs. Both drive letters and mount points are supported.
Monitoring of SQL Server 2014 Managed Backup.
New profile dedicated for tasks only: Microsoft SQL Server 2014 Task Run As Profile.

Improvements:

Dependency on SQL Server Library management pack has been eliminated.
Support of Localized performance counter for CPU and Logical Disk Metrics.
SQL Server Integration Services monitoring seed discovery.
SQL Server Integration Services moved to separate management pack.
SQL Server non-default port is now supported.
Optimized performance collection replaced with raw performance collection to guarantee accuracy of hourly and daily aggregated data.

Fixes:

AlwaysOn: Endpoint URL points to a non-computer name.
AlwaysOn: read intent is not supported, false alert message removed.

Other changes:

Monitoring of SQL Server Reporting Services has been deprecated. This component will be monitored by dedicated management pack.
Monitoring of SQL Server Analysis Services has been deprecated. This component will be monitored by dedicated management pack.

Monitoring of SQL Server Replication has been deprecated. This component will be monitored by dedicated management pack.

You can download the Microsoft System Center Management Pack for SQL Server 2014 here

April 4, 2014
by James van den Berg Leave a comment

Microsoft #SQL Server Backup to #MicrosoftAzureTool

Microsoft SQL Server Backup to Microsoft Azure Tool

Overview:
SQL Server 2012 SP1 CU2 and SQL Server 2014 have built in capability to back up to Microsoft Azure storage. The SQL Server Backup to Microsoft Azure tool provides the same functionality for previous versions of SQL Server. It can also be used to provide encryption and compression for your backups.
Using the 3-step wizard, you can specify a rule or set of rules that are applied to any SQL Server backup. One example of a rule could be to redirect all local backups to the specified Microsoft Azure storage. Another example of a rule would be to use compression or encryption for backups stored in a specific location.
Once you configure the rules, these rules are applied to SQL Server Backup files. If the rule is set to use a Microsoft Azure storage account, the tool redirects the backups to the specified Microsoft Azure storage account, but leaves a stub file in the local storage with metadata information to be used during restore.

Benefits:
• Support for backups to Microsoft Azure Storage for SQL Server versions that do not have the built-in capability. Using Microsoft Azure storage for your backups has several benefits, such as providing off-site storage for disaster recovery, accessibility regardless of location, etc. For more information, see SQL Server Backup and Restore with Microsoft Azure.
• Encryption support for SQL Server versions that do not have the built in capability. Currently only SQL Server 2014 has encryption support.
• Compression support for SQL Server versions that do not have the built in capability. Currently, SQL Server 2008 supports compression in Enterprise edition only, but SQL Server 2008 R2 and later, encryption is supported on Enterprise and Standard editions.
SQL Server and Operating Systems Support:
This tool is supported on SQL Server 2005 or later, and Operating System versions: Windows Server 2008 or later for Servers, and Windows 7 or later for Client Operating Systems.
Prerequisites:
• Microsoft Azure subscription and a Microsoft Azure Storage Account.
o You can log in to the Microsoft Azure Management Portal using your Microsoft account. If you do not have a Microsoft account, visit Microsoft Azure 3-Month free trial.
o To create a Microsoft Azure storage account, see How to Create a Microsoft Azure Storage Account.

• A Microsoft Azure Blob Storage Container: SQL Server uses the Microsoft Azure Blob storage service and stores the backups as blobs. A container is a grouping of blobs an all blobs must live in a container.
Installing the Tool:
The setup is simple and involves the following steps:
1. From the download page, download the MSI (x86/x64) to your local machine that has the SQL Server Instances installed. If your production machines do not have access outside of your organization, download to a local share and use the MSI to install the tool on your production machines.
2. Double click the MSI file to start the installation.
3. Read and accept the terms of the license agreement, and click Install to start the installation process.
When the installation completes, a service named Microsoft SQL Server Backup to Microsoft Azure Tool Service is created on the machine. This service runs the SQLBackup2Azure.exe to apply the configured rules such as backing up to Microsoft Azure Blob storage, compression, or encryption of the backup files. The installation also requires and attempts to create a low privilege account which is used to run the service. When installed, Microsoft SQL Server Backup to Microsoft Azure Tool adds two objects to the users and groups on the local machine:
• A user group called “TempGroup”
• A user in the group called “SQLBackup2Azure”
These names are hard coded. The password for the user SQLBackup2Azure account is generated automatically and not accessible by anyone including Microsoft.
When a rule is created, Read/Write permission for the specified folder is automatically granted to the group “TempGroup”. This includes the SQLBackup2Azure account. If the account does not have permission to read or write to the specified folder, it will not function correctly.

How to Use the Tool to Create Rules:
NOTES:
Going through the wizard to setup the rules allows the program to process the backup files that should be encrypted, compressed or uploaded to Azure storage. This tool does not provide for scheduling, error tracking or logging of the backups. All backup job scheduling, maintenance and error tracking should be done by using SQL Server Management Studio or other applications which can provide this functionality.
In addition, turn off SQL Backup compression on the databases that you want to back up with the Tool. SQL Backup Compression does not give additional benefit over the tool’s compression features. If you wish to compress your backup, please use the Tool for compression.

1. Once installed, the tool should be appear on the Programs list.

2. Click on the “Microsoft SQL Server Backup to Windows Azure…” to start the Wizard.

3. On the Rules page, click Add to create a new rule. NOTE: Only the Add button is enabled if you are using this tool for the first time.

Once you have created one or more rules, you will see the existing rules and the option to
Modify or Delete the rule as shown below.

4. On the Add Rule (Step 1 or 3) page, you can either choose to apply the rule to all paths to the local machine or to one specific location. You must also specify the file name pattern that this rule should apply to. For example, if you want to apply this rule to all files with the extension .bak, you would specify *.bak in the File name pattern field.

5. On the Add Rule (Step 2 of 3) page, you can specify the Windows Azure storage account information, so the backups you specified in Step 1 can be redirected to use the Windows Azure storage as the backup destination. Alternatively, you can choose to keep the local storage as the backup destination.

For Microsoft Azure Storage, you must specify the name of the account, the storage access key, and the name of the container. You can retrieve the name of the storage account and the access key information by logging into the Microsoft Azure management portal. For more information on where to find this information, see http://go.microsoft.com/fwlink/?LinkId=392743. The storage name and access key are used to authenticate to the storage account, the container. Click Verify account to ensure that the information specified is valid and the tools is able to connect to the storage account.

6. On the Add Rule (Step 3 of 3) page, you can enable or disable encryption or compression. If you enable encryption, you must specify a password. The password is used for decryption purposes. For more information, see Backup Encryption. Once you specify the options, click Finish to create the rule.

7. On clicking Finish you will see the following page with the rule configuration. You can close the wizard once you have completed adding or modifying the rules

Restoring a Database from a Backup Taken with SQL Server Backup to Microsoft Azure Tool in Place:

The SQL Server Backup to Microsoft Azure Tool creates a ‘stub’ file with some metadata to use during restore. Use this file like your regular backup file when you wish to restore a database. SQL Server uses the metadata from this file and the backup on Microsoft Azure storage to complete the restore. For example:
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘<metadata file location>’

Restoring a Database from a Backup Taken with SQL Server Backup to Microsoft Azure Tool in Place If You have Lost the Stub File

If you have lost the stub file (e.g. through loss of the storage media that contained the stub file) and you have chosen the option of backing up to a Microsoft Azure Storage account, you may recover the stub file through Microsoft Azure Storage by downloading it from the storage container in which it was placed. You should then place the stub file into a folder on the local machine where the Tool is configured to detect and upload to the same container with the same encryption password if encryption was used with the original rule.

Frequently Asked Questions:

Q: The tool doesn’t seem to detect and select my backup files correctly.

A: Try the following:

Confirm the “Microsoft SQL Server Backup to Windows Azure” service is started
Check if your rule matches the file name you try to back up. A rule will look for files named “.bak”, for example, if you placed “.bak” in the “File Name Pattern” field. To properly match files, you may need to use wild cards.

Q: I can’t start the “Microsoft SQL Server Backup to Windows Azure” service.

A: Check Windows System Event logs for errors when the service fails to start.

Q: Is there any retry logic built into the tool?

A: No. In the event of a loss of network connectivity, SQL Server will surface the error as not being able to write to the device. You will need to clean up the files relating to this backup operation (if any) and retry the backup.

Q: Can I back up to an existing backup file on disk?

A: No. This is a known limitation of the Tool. If you want to create a backup using the Tool, you must create a new backup file instead of overwriting/appending to an existing backup file.

Q: Should I use SQL Backup Compression with the Tool?

A: No. SQL Backup Compression provides no additional benefit over the Tool’s compression algorithms. We strongly suggest you turn off SQL Backup Compression for any backups taken to a folder that the tool is monitoring. If you wish to compress these backups, please turn on the Compression option in the Tool instead of using SQL Backup Compression.

Additional Resources:

SQL Server Backup and Restore with Windows Azure.

You can download the Microsoft SQL Server Backup to Microsoft Azure Tool here

April 3, 2014
by James van den Berg 1 Comment

Microsoft #SQL Server 2014 Feature Pack #ITPRO

The Microsoft SQL Server 2014 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server. It includes the latest versions of:

Tool and components for Microsoft® SQL Server 2014.
Add-on providers for Microsoft SQL Server 2014

You can download the Microsoft SQL Server 2014 Feature Pack here

March 21, 2014
by James van den Berg 3 Comments

Microsoft #SQL 2014 Platform for Hybrid Cloud with #WindowsAzure

Microsoft will release SQL Server 2014 on April, 1 2014

Together with Windows Azure, SQL Server can help you reduce costs, lower RTO and improve your disaster recovery capabilities.

New in SQL Server 2014, Managed Backup to Windows Azure schedules, performs and maintains backups based on the activity of the data, eliminating the need for custom code and manual backups. You also gain the benefits of Windows Azure storage, which offers geographic redundancy at a lower cost than maintaining additional hardware.

Achieve high availability and faster recovery using active AlwaysOn replicas in the cloud. Now with SQL Server 2014, easily create asynchronous replicas using a Windows Azure Virtual Machine.

Also in SQL Server 2014, you can move your database files to Windows Azure while keeping your applications on-premises for bottomless storage in the cloud and greater availability. The SQL Server Data Files in Windows Azure configuration also provides an alternative storage location for your database backup files by allowing you to restore from or to Windows Azure Storage.

Deploy your Database to WindowsAzure

Backup your Database to Windows Azure

Make Allways On replica’s in the Windows Azure Cloud.

Cloud computing brings a new paradigm shift in computing and organizations continue to realize the potential cost benefits of running their database applications in the cloud with greater scale and flexibility. Microsoft SQL Server runs very well in the cloud environment—your organization can deploy SQL Server in a private cloud, hybrid cloud, or public cloud easily and can experience familiar tools for development and management. In the public cloud, you can choose to run SQL Server in Windows Azure Virtual Machine or Windows Azure SQL Database. Microsoft SQL Server in a Windows Azure Virtual Machine provides full feature parity with on-premises SQL Server. Windows Azure SQL Database offers a strong foundation for building cloud-based relational database applications that scale out to millions of users.
More information on SQL Hybrid Cloud Scenario’s in this WhitePaper