26 Nov 2010

App-V / Database Mirroring

SQL DB fail over for App-V Management Server

Since 4.5 SP2 App-V Supports SQL Database mirroring.
That helps if you’re using the full enterprise model and want to increase the availability of your App-V environment but not like to use MS fail over clustering for the database.

For how to Configure Database Mirroring read the following articles
For SQL 2005
For SQL 2008

How to configure your App-V Management Server(s) to support fail over  to a mirrored database

  • enable TCP/IP and Named Pipes on both SQL Servers
  • run SQL Server and SQL browser service under domain account
  • If you use Kerberos (I’ll recommend that) you have to manually set the SPN for both SQL Servers (Principal and mirror) for the service account ( setspn -A MSSQLSvc/ <SQL\_Service\_Account> )
  • Restart both SQL Server services
  • Add a login for the computer account of the principal server to mirror server <domain\server$> and grant SFT_Admin role to AppV DB
  • Add a login for the computer account of the App-V Management Server to mirror server <domain\server$> and grant SFT_Admin role to AppV DB
  • Grant connect permission to the logins created above
    USE master;
    GRANT CONNECT ON ENDPOINT::Mirroring TO “domain\server$” 
        WITH GRANT OPTION;
    GO
  • Edit the Connection File on the AppV Management Server
  • (i.e. C:\Program Files\Microsoft System Center App Virt Management Server\App Virt Management Service\SftMgmt.udl)
  • On the Provider tab, select the OLE DB provider SQL Server Native Client 10.0.
  • Click Next to select the Connection tab. In the Server Name box, enter the server name of the SQL Server. Next, select Use Windows NT Integrated Security. Finally, click the list Select the database, and then select the App-V database name.

  • Click the All tab, and then select the entry fail over Partner. Click Edit Value, and then enter the server name of the failover SQL Server. Click OK

  • Add the following Reg Keys/Values to the App-V Management Server
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Softgrid\4.5\Server\SQLFailOverServerName as REG_SZ and then set the value to the host name of the SQL Server that hosts the mirror.
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Softgrid\4.5\Server\SQLFailOverServerPort as DWORD and then set the value to the port number that is used for SQL on the computer that is running SQL Server to host the mirror. If you are using a named instance for the mirror this key value must be set to the port number that is used for the named instance.

The failover case (where your primary database server has gone)

In my environment I didn’t use a witness server. So in a fail over case I’ve to manually fail over the mirror database, so it can be used by the App-V Management server.

USE
master
GO
ALTER DATABASE SET PARTNER FORCE\_SERVICE\_ALLOW\_DATA\_LOSS GO

!!! This can only be performed if the principal SQL server is not reachable. Be aware that you could have some data loss using this option (depending if you configured a synchronous mirroring or not)

To go back (if original server is available again)

– backup database on mirror server
– restore db on the recovered server (with no recovery and override)
– configure the mirroring (so your recovered server will get a mirror from the new principal)
– stop the sql server on the principal
– repeat the above statement on the recovered server, which will be your principal again.
– repeat the steps for your original mirror server to get the original situation back up and running