European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European Windows 2012 Hosting - Amsterdam :: Windows Server 2012 Hyper-V Replica

clock July 29, 2013 07:30 by author Scott

For small businesses, there’s often no easy answer when it comes to disaster recovery. With many storage and DR options out of reach, finding an affordable way to protect data can be challenging. But in Hyper-V Replica, Microsoft offers a new path to DR for many SMBs.

Usage Scenarios

Hyper-V Replica, one of many operational and performance improvements to Hyper-V in Windows Server 2012, allows users to replicate virtual machines over slow, unreliable network links. Replica is purely a DR solution and doesn’t replace clustering, but it can be used to replicate clusters of Hyper-V servers.

With Replica, SMBs can set up a DR site at a branch office (or at a remote site hosted by a service provider) and use Hyper-V to replicate the contents of selected VMs at 5-minute intervals.

Unlike most enterprise-grade replication technologies, Hyper-V Replica does not require a reliable, high-bandwidth network link between primary and DR sites. It’s designed to work with links that don’t run over dedicated lines, such as broadband or asymmetric digital subscriber line (ADSL) technologies, which may be subject to short outages.

The Replication Process

To begin, Hyper-V Replica creates an initial copy of the primary server. That file is then copied over, either manually or over the network, to the Replica server at the recovery site. Replica then writes to a log file (Hyper-V Replica Log, or HRL) on the primary server every time there’s a write operation. This HRL file is replicated on the Replica server and is replayed every 5 minutes, in reverse, replicating only changes made since the last replay.

Due to the 5-minute replication frequency, if the primary server goes offline, up to 10 minutes of data might be lost. If Hyper-V can’t replicate at the 5-minute interval, an alert is written to the event log. If replication fails for more than 30 minutes, the Replica server is put into a failed state and replication must be manually restarted — so it’s possible that an hour of data could be lost at the DR site.

The replication schedule is designed to keep the state of the operating system consistent after a failover to the Replica server. If it’s critical that applications remain consistent, Hyper-V Replica can be set to create additional Volume Shadow Copy Service (VSS) recovery points for this purpose.

Ensuring Security with Hyper-V Replica

Before implementing Hyper-V Replica, Windows Firewall must be configured to allow the necessary replication traffic to pass through. This applies as well to any other firewalls that separate the corporate intranet from the DR site. Replica comes with default Windows Firewall rules, but they must be enabled manually in the Windows Firewall with Advanced Security management console. There are two inbound rules:

  1. Hyper-V Replica HTTP Listener (TCP-In)
  2. Hyper-V Replica HTTPS Listener (TCP-In)

The HTTPS rule is needed only if certificate-based authentication is used. Hyper-V in Windows Server 2012 also includes a new simple authorization model in which a Hyper-V Administrators group is created on each server that hosts the Hyper-V role. Hyper-V Administrators can configure Replica servers to accept incoming connections from specific Hyper-V servers.

There are two different types of authentication that Hyper-V Replica uses. Kerberos, a network authentication protocol, is the default option, but certificate-based authentication allows replication between primary and Replica servers to be encrypted.

Integrated authentication uses the Kerberos protocol and can be established between trusted Active Directory domains. In the absence of Active Directory, X.509v3 certificates can be used instead of Kerberos. Replication Authorization Tags protect Replica VMs residing on a single server in a data center, which is useful for hosting service providers that need to stop clients from gaining access to Replica VMs other than their own.

Installing Hyper-V Replica

Replica works in both workgroup and domain scenarios, and Replica servers don’t need to be in the same AD domain as primary servers. The first step is to configure a Replica server in a branch office by adding the Hyper-V role. This is done either with Server Manager or by using PowerShell. Once the role is installed, the server must be restarted. The following PowerShell command will install the Hyper-V role and management tools:

Install-WindowsFeature -Name Hyper-V –IncludeManagementTools

Now open Hyper-V Settings in the Actions pane of Hyper-V Manager, click on Replication Configuration and select Enable this computer as a replica server in the Details pane. Choose an authentication method, and don’t forget to enable the default Windows firewall rules. In the Authorization and storage section, the system can be set to allow replication from any authenticated server or from a specific list of servers.

VMs in the primary site can be enabled for replication with a simple wizard. In Hyper-V Manager, select a VM in the Details pane, right-click the VM and choose Enable Replication. The wizard allows a choice in how to perform the initial replication, which by default happens over the network. That may not be feasible or desirable over a slow network, so there’s also the option to perform the first replication using external media and then import the data at the Replica site.


Hyper-V has a new section in the configuration option for each VM called Failover TCP/IP, where both alternative IPv4 and IPv6 addresses can be provided should the DR site have a completely different IP addressing scheme than the primary site. These addresses are used when a failover action is performed.

There are three kinds of failover:

  • Test Failover
  • Planned Failover
  • Unplanned Failover

A Test Failover allows Hyper-V administrators to perform a failover without disrupting the primary server.

A Planned Failover should involve no data loss; the primary server will temporarily become the Replica server for the VM that has failed over. The VM on the primary server needs to be shut down beforehand, and the primary server must be configured as a Replica server.

An Unplanned Failover is not expected and is initiated from the Replica server at the DR site. A VM on the Replica server will be started from a Replica recovery point. This could result in some data loss.

European SQL 2012 Hosting - Amsterdam :: SQL 2012 Always On Hosting on Cloud

clock July 17, 2013 10:45 by author Scott

It has traditionally been almost impossible to architect infrastructure-class, highly available (HA) SQL Server solutions using shared storage in the public cloud. Recently Microsoft announced support for some System Center 2012 SP1 applications to work with SQL AlwaysOn, a new way to achieve HA SQL. AlwaysOn uses an availability group concept, much like Exchange 2010 database availability groups (DAGs) to achieve clustered HA services without shared storage.

This is good news for architects looking to move management workloads into the public cloud when appropriate. In theory, a pair of powerful VMs in Azure running SQL 2012 AlwaysOn can approach and exceed the HA SLAs expected of many mission critical applications. Figure 1 shows the new dashboard view of AlwaysOn HA availability groups with some System Center 2012 SP1 databases made highly available.

Figure 1

HA SQL is Important. What is the Reason?

Highly available (HA) SQL Server services are the cornerstone of many enterprise database applications. Few enterprise solutions today are deployed on non-HA SQL. Without an HA mechanism for database services, enterprise applications and e-commerce websites cannot offer maximum available uptime. Even perfectly managed servers require periodic restarts for updates and maintenance; and you always need to be prepared for equipment failure such as extended outages of particular servers or disk drives.

The traditional way to offer HA SQL is by creating a SQL Server failover cluster based on shared storage. That is, a storage area network (SAN) presents shelves of disk drives to two or more servers at the same time ("shared storage"). The SAN and shared storage is often the most expensive component in the datacenter. Public cloud solutions abstract you from the storage, and usually don't offer the kind of infrastructure you would need to run a conventional HA SQL failover cluster with shared storage in the cloud.

SQL Server 2012 AlwaysOn, no need for SAN

With SQL Server 2012 AlwaysOn, two or more complete copies of each HA database can exist, synchronized by AlwaysOn technology. The independent database copies are presumed to exist locally in direct attached storage (DAS) on each SQL node, or over on the network using economical Windows Server 2012 SMB 3.0 file shares on dedicated Windows Server 2012 file servers.

- You can use Windows Server 2012 Standard for the SQL server nodes-unlike in previous Windows releases, you can enable the failover cluster feature in the Standard edition of Windows Server 2012 as well as Windows Server 2012 Datacenter.

- SQL Server 2012 does require the Enterprise edition of SQL Server 2012 to use the AlwaysOn feature.

Steps to deploy a SQL Server 2012 AlwaysOn Availability Group

Here are the high-level steps to follow to deploy SQL 2012 AlwaysOn in a SQL 2012 failover cluster (without shared storage).

1. Install Windows Server 2012 in two computers or virtual machines (VMs), each with a single network interface card (NIC).

2. Create a two-node failover cluster without shared storage. You will need a cluster name and an IP address for the cluster network name.

3. Install SQL Server 2012 Enterprise on both computers as if they were going to be stand-alone SQL servers.

  • When you install, use a domain account for the SQL server services.
  • Open the Windows Firewall on ports TCP 1433 and TCP 5022.

4. Create a temporary "seed" database on the first SQL server using SQL Server 2012 Management Studio. This database will be used to establish the AlwaysOn cluster, and then can be deleted after the first production database is deployed.

5. Make sure the database is of the "Full" type model, and perform a SQL Backup job.

6. In the Management Studio, create an AlwaysOn Availability Group and an Availability Group Listener. (The Availability Group Listener is essentially the virtual (or clustered) SQL Server instance. There is a one-to-one relationship between availability groups and listeners.)

  • Assign a DNS name and TCP IP address for the AlwaysOn Availability Group and an Availability Group Listener.
  • Assign a shared network folder that is accessible to all SQL servers that will have AlwaysOn database replicas.

7. At the AlwaysOn High Availability node, right-click and select Add A Database To An Availability Group. If your database is of the Full type and has been backed up, the status will be "Meets Requirements". Click Next.

8. Select that you will perform a full synchronization, using the shared network folder you specified in step 6(b). Click Next.

9. Enter security information to access the primary database replica. Click Next, observe the validation and click Next, and then Finish.

10. Observe after a moment that new database replica on the secondary node in the SQL AlwaysOn availability group has been created, as seen in Figure 2.

Figure 2

European SQL 2008 R2 Hosting - Amsterdam :: Generated LINQ to SQL, SQL Server Profiler, and Provide Final T-SQL Generated

clock July 5, 2013 07:42 by author Scott

If you’re interested in taking a look at the final T-SQL generated by your LINQ to SQL queries then you have a few options available:

  1. Utilise LINQPad which converts your C# LINQ database queries into T-SQL
  2. Execute an SQL Server Profiler trace against the database your application is querying while its running
  3. Understand how ADO.NET translates the LINQ to SQL expression tree into T-SQL so you know what it’s going to output

So first off I’ll provide a brief overview of SQL Server Profiler and what a “trace” is. Then I’ll show you how to configure and run a trace. Finally I’ll throw a few LINQ to SQL examples at a local SQL database and provide the final T-SQL generated.

SQL Server Profiler & Traces

SQL Server Profiler comes packaged with SQL Server so if you’re running SQL Server Management Studio chances are you’ll find a shortcut to this particular tool in amongst the SQL shortcuts, or via the Tools menu option in SQL Server Management Studio. Its primary aim is to analyse, and even replay SQL trace results, for the purpose of troubleshooting, diagnosis, benchmarking, monitoring, etc. As a developer you can imagine how helpful this can be when you want to identify bottlenecks, optimise your system during the testing and performing tuning phase, generate performance reports, etc.

Once you delve into examining SQL events you’ll start to notice that SQL generates a LOT of unique events (well over a hundred) and in order to help isolate the ones you’re looking for you have to create what’s called a trace. The trace defines which SQL events to capture as well as providing very handy filtering and output options.

The trace results are also returned in a tabular fashion which you can browse through within the tool or export for use elsewhere.

Creating a Trace

Start up SQL Server Profiler. The first screen you’ll see will hopefully look like this:

Click on “File” in the menu and select “New Trace…” then select the appropriate connection settings.

You’ll then be presented with a fresh “Trace Properties” window from where you’ll be able to configure your trace.

Leave everything as is on the “General” tab with the exception of the “Use the template:” option which you want to change to “Blank”. This essentially clears the events the trace is going to capture so that we can start from scratch and only add those we want. There are a lot of preconfigured templates but we want to avoid those for now.

Now click on the “Events Selection” tab and you’ll see an array of events all grouped accordingly. We want a specific event called “RPC:Completed” which is under the “Stored Procedures” category.

Select the checkbox to the left of “RPC:Completed”. This tells our trace that we want to capture all events that are fired when a remote procedure call completes it’s execution.

One small but useful tip is to organise how the data will be returned so click on the “Organize Columns…” button and reorder the columns so the “StartTime”, “Duration” and “TextData” are at the top. This means we’ll have our trace results returned in chronological order and each will tell us how long they took to execute in milliseconds and what T-SQL was executed in the first few columns. See below:

Click “OK” to save the column order then click “Run” to proceed.

Congratulations you just created a trace!

Reviewing the Trace for LINQ to SQL queries

Now that the trace is up and running we’ll start to see all stored procedure calls that are completed against any database associated with the connection you chose earlier.

If you fire up a Windows C# application that makes LINQ to SQL calls you’ll start to see them appearing in the trace output. What’s important to clarify at this point is that even if your LINQ to SQL queries aren’t calling a stored procedure the system stored procedure sp_executesql will be executing the queries, and that’s what the trace will be capturing.

See below for an example:

What I’ve selected is a simple LINQ to SQL insert into a table called “ExceptionLog”, and here’s the C# code that generated this call:

var exceptionLog = new ExceptionLog
    ExceptionType = exceptionType,
    Message = message,
    AddedDateTime = DateTime.UtcNow

using (var dataContext = GetDataContext())

You can use the Pause/Start buttons to halt the trace if need be so you don’t find SQL Server Profiler auto-scrolling to the bottom of the list every time a new procedure is completed.

Here’s another example of a simple LINQ to SQL select:

var query =
        from el in dataContext.ExceptionLogs
        where el.AddedDateTime.Year == 2012
        orderby el.AddedDateTime ascending
        select new ExceptionLog
            ExceptionLogId = el.ExceptionLogId,
            ExceptionType = el.ExceptionType,
            AddedDateTime = el.AddedDateTime

And in the trace window we’ll see:

exec sp_executesql N'SELECT [t0].[ExceptionLogId], [t0].[ExceptionType], [t0].[AddedDateTime]
FROM [dbo].[ExceptionLog] AS [t0]
WHERE DATEPART(Year, [t0].[AddedDateTime]) = @p0
ORDER BY [t0].[AddedDateTime]',N'@p0 int',@p0=2012

You can see the exact SQL output. More information is displayed above as well so you can get an idea for what other data is associated with the trace; duration in milliseconds, DatabaseName, RowCounts etc. All very useful.

Here’s another example where I’ve employed the SQL IN operator to retrieve a collection of authorised users. The LINQ to SQL:

var authorisedUserRoleIds = new List
    1, // 1 = System Administrator
    2, // 2 = Regional Manager
    3 // 3 = County Manager

var query =
        from u in dataContext.Users
        join ur in dataContext.UserRoles on u.UserId equals ur.UserId
        where authorisedUserRoleIds.Contains(ur.RoleId)
        orderby u.Surname ascending
        orderby u.FirstName ascending
        select new User
            UserId = u.UserId,
            FirstName = u.FirstName,
            Surname = u.Surname,
            EmailAddress = u.EmailAddress

And the resulting SQL:

exec sp_executesql N'SELECT [t0].[UserId], [t0].[FirstName], [t0].[Surname], [t0].[EmailAddress]
FROM [dbo].[User] AS [t0]
INNER JOIN [dbo].[UserRole] AS [t1] ON [t0].[UserId] = [t1].[UserId]
WHERE [t1].[RoleId] IN (@p0, @p1, @p2)
ORDER BY [t0].[FirstName], [t0].[Surname]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=2,@p2=3

If you look closely you can see the JOIN, WHERE  clause and ORDER BY  just as you’d expect.

What Next?

This is really a starting point and there is a lot more that can be achieved with SQL Server Profiler further. Some examples being:

If you want to edit your running trace you’ll have to pause it, select the properties option to edit it then get it running again – “File > Properties”.

Another useful event under the SQL group is SP:StmtCompleted. SP:StmtCompleted returns every single statement completed whereas RPC:Completed only returns the last remote procedure executed. So if you called a stored procedure which contained five individual queries from your LINQ to SQL you’d see five SP:StmtCompleted events and one RPC:Completed.

Save the trace you’ve created as a template so that you can reuse it again – “File > Save as Template“. You can even save it into the list of templates that appears in “New Trace” window for easier access or share it amongst your development team.

Apply filters to the trace in the “Events Selection” tab. See the “Column Filters…” button. Useful if you want to identify queries taking longer than 10 milliseconds or just target a given database.

Look into the Database Engine Tuning Advisor tool and how it can analyse trace outputs to help create indexes to boost performance.

About is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.

Tag cloud

Sign in