20110221

Disconnecting users from MSSQL database

When trying to restore database from backup often error saying about connected users is displayed.
Script below kills all connections to given database.
Just replace XYZ with required database name.
Remember that all connections will be dropped without notice.

USE master

GO

DECLARE @userId varchar(10) 

DECLARE cur CURSOR READ_ONLY      
FOR 
SELECT request_session_id
FROM master.sys.dm_tran_locks     
WHERE resource_type = 'DATABASE'     
AND resource_database_id = db_id('XYZ')    
GROUP BY request_session_id

OPEN cur     

FETCH NEXT FROM cur INTO @userId      
WHILE (@@fetch_status <> -1)      
BEGIN      
 IF (@@fetch_status <> -2)      
 BEGIN      
  PRINT 'Killing connection ' + @userId      
  EXEC ('KILL ' + @userId)      
 END      
 FETCH NEXT FROM cur INTO @userId      
END      
      
CLOSE cur      
DEALLOCATE cur      

20110210

Deploying Sitecore CMS to Windows Azure using VM Role

Sitecore CMS can be deployed to Microsoft Azure using Sitecore CMS Azure Edition, but as a proof of concept I gave it a try to upload it using Virtual Machine (VM) Role.

Using exactly same procedure as with EPiServer you can deploy Sitecore CMS. Only change required was updating aspnet_Membership_GetNumberOfUsersOnline procedure as in original it's not compatible with Azure SQL. So after database migration additional query with code placed below is required:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetNumberOfUsersOnline]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u WITH (NOLOCK),
            dbo.aspnet_Applications a WITH (NOLOCK),
            dbo.aspnet_Membership m WITH (NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END' 
END

20110209

Deploying EPiServer CMS to Windows Azure using VM Role

Intro


This post describes running EPiServer CMS version 6 on the Windows Azure using Virtual Machine (VM) Role Beta Program. The main goal was to take developer Hyper-V machine and deploy it to the cloud as staging environment.

Prerequisites



Creating VHD


First Windows Server 2008 R2 is required - I've used Windows Server 2008 R2 Enterprise (Full Installation). Additionally it should be installed on a single partition, which is not the default behaviour. There are few posts on how to remove this additional partition but sysprep (described later) fails after those operations - it can be done only during system installation.

  1. select Custom (Advanced) instead of Upgrade
  2. on partition selection screen press Shift + F10 to open console
  3. run diskpart
    • list disk
    • select disk 0
    • create partition primary
    • exit
  4. close console
  5. refresh
  6. select created partition

After installation go to Server Manager:
  1. add Role: Web Server (IIS)
  2. add Feature: .NET Framework 3.5.1 (inside .NET Framework 3.5.1 Features node)
You may need to switch off Windows Update, but it should be inactive by default.
You may backup Virtual Machine now to reuse it later.

Installing Application


Now it's time to install EPiServer or any other application you'd like to deploy to the could. I've used empty EPiServer version 6 with Public Templates. Only web.config change was to switch to SQL Role and Membership providers.

Setting up database


Local database needs to be migrated to Microsoft SQL Azure Database - there's quite simple way to do it using SQL Azure Migration Wizard

Because this tool doesn't clean tables previously filled with data this script can be used to remove all tables if another run is required for any reason.

You should modify connectionStrings.config to connect to Azure SQL instance and test if application works.

Finalizing VHD


It's highly recommended to backup Virtual Machine now.
Further steps caused it hang during start up until in cloud.

Mount C:\Program Files\Windows Azure SDK\v1.3\iso\wavmroleic.iso as DVD drive and install WaIntegrationComponents-x64.msi. Provide it with valid Administrator password and let it install all the drivers it needs. After reboot run %windir%\system32\sysprep\sysprep.exe enter options as on image below and let Virtual Machine shutdown - now VHD image is ready for deployment.


If you ever start this Virtual Machine again (which is not obvious to succeed) you need to launch sysprep once more.

Creating Management Certificate


  1. Start Visual Studio
  2. create project of type C# / Cloud / Windows Azure Project.
  3. click Publish
  4. Select 'Deploy your Windows Azure project to Windows Azure'
  5. Credentials - select Add... and name it
  6. Click 'Copy the full path' to get Public key path and upload it to Azure



  7. Copy subscription ID back to Management Authentication dialog and click OK - it will be verified.
  8. You can now forget about this project :).

Uploading VHD


Open a Windows Azure SDK Command Prompt as an administrator from Start | All Programs | Windows Azure SDK v1.x.
Run command below replacing XXXXX and YYYYY with your Subscription Id and Certificate Thumbprint respectively. You should of course provide path to VHD and destination location to reflect your needs.

csupload Add-VMImage -Connection "SubscriptionId=XXXXX; CertificateThumbprint=YYYYY" -Description "Base image Windows Server 2008 R2" -LiteralPath "D:\VM\AzureTest\Virtual Hard Disks\Clean win 2008 r2.vhd" -Name baseimage.vhd -Location "North Europe"



Anywhere locations are not supported (i.e. Anywhere Europe and Anywhere US).

Statuses visible in Management Portal while uploading:
  • pending means uploading
  • committed means ready

Creating the Service Model (Visual Studio part)



  1. Start VS2010
  2. create Windows Azure Project without any roles
  3. right-click Roles > Add > New Virtual Machine Role
  4. in properties of new role > Virtual Hard Disk tab > select account and uploaded VHD
  5. in Endpoints tab add:
    • name: HttpIn
    • type: Input
    • protocol: Http
    • public port: 80
    • private port: 80
  6. go to Publish
  7. Configure Remote Desktop (save certificate to file WITH the private key)
  8. select Create Service Package Only
  9. click OK

Creating the Service Model (Management Portal part)


  1. New Hosted Service - name it, give it URL prefix, choose region and 'Do not deploy'
  2. Add Certificate (select one created in previous step)
  3. New Production Deployment - name it and select two files created by VS (.cspkg and .cscfg)
  4. wait...
  5. wait more...
  6. wait even more...
  7. you can connect to IIS using URL on the right
  8. Connect on the ribbon downloads RDP file




Further works


All file system modification on Virtual Machine will be lost when it's reimaged from uploaded VHD. It may happen because of machine failure or restart. At the moment only data stored in Azure SQL is safe - every change to VPP will be lost. There are two options - to store VPP data in database or in Cloud Storage. There's a post on this by Daniel Berg but I haven't tried yet.

There's also licensing issue as EPiServer license is linked either to IP or MAC address. Unfortunately MAC address changes as well during deployment. At the moment I've generated new demo license using MAC address obtained from Virtual Machine but it will be lost. Not sure what can be done about it.