SQLServerCentral Article

Exam 70-462: Using Azure for the Practice Setup

,

As part of my preparation for taking Microsoft’s database administration exam, I came across that bump in the road known as the ‘Practice Setup’, which I’m sure is familiar to anyone who has taken, or is planning on taking, the exam. For those of you who are not familiar with this, it is a network of six servers running Windows Server 2008 R2, on which eager-to-learn candidates like myself can use to practice installing and configuring SQL Server 2012, including failover clusters and AlwaysOn Availability Groups. I had neither a spare pocketful of server hardware, nor access to enough computing power of my own to virtualize the same, so I decided to investigate using Microsoft’s Azure cloud platform instead. The notes that follow are the results of my investigations.

My intention was to create, as faithfully as possible, an Azure-hosted rendition of the lab environment as specified in Microsoft’s exam prep book, “Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases” (I am working from a Sixth Printing: April 2014 copy). Page number references in the headings below refer to page numbers of this book, and numbered list items refer to the same numbered items in the book for that section. At the bottom of this article you can download the screenshots that accompany these notes, and it’s also worth downloading the errata for the book, too. Azure is a constant work in progress, and changes rapidly, so the exact location of a given item may have changed since the date of writing. These notes were based on what is now known as the ‘classic’ deployment model. Please explore in Azure until you find what you need; I’m sure it will be there somewhere.

Pre-requisites

I’m assuming that, like me, you’re not an expert in either networking or virtualization technologies. However, if you have at least a general understanding of the following concepts, you should have no problem creating the practice setup:

  • Virtual machines (VMs)
  • IP addresses and the difference between static and dynamic ones
  • Connecting to another computer using Remote Desktop

Preparation

I’m also assuming that you haven’t used Azure before, so this is a ‘from scratch’ walk-through. In my experience so far, it can often take Azure a good few minutes to complete operations on VMs, so plan to set aside a few hours for creating the entire practice setup. Of course you can always do it in stages. Azure charges for VMs by the minute, so if you need to pause the process for any appreciable length of time, remember to shut down your machines via the portal first (if you only shut them down via a Remote Desktop connection, Azure will still charge compute time for the stopped VM). You may need to modify some of the names (e.g. domain name) from the examples here so that they will be globally unique.

  • Sign up for an Azure free trial:

    • http://azure.microsoft.com
    • Requires a Microsoft account (e.g. hotmail.com or outlook.com)
    • $200 credit for 30 days; MSDN subscribers get free monthly credit

A. Create a resource group to hold the Practice Setup.

  • Log on to the Azure portal at https://portal.azure.com/
  • From the Azure Startboard click:

    • Browse All > Resource groups
  • From the ‘Resource groups’ blade click:

    • Add icon
  • In the ‘Resource group’ blade:

    • Name: Group70462
    • Click the ‘Create’ button
  • When Azure has finished creating the resource group, the Group70462 blade will open automatically

B. Create a virtual network to connect the VMs.

  • From the Azure Startboard click:

    • New > Networking > Virtual Network

      • Name: VNet70462
      • Address space:

        • Address space CIDR block: 10.10.10.0/24
        • Subnet name: VSubnet70462
        • Subnet CIDR block: 10.10.10.0/24
      • Resource Group:

        • Group70462
      • Location:

        • [pick the location nearest to you]
      • Click the ‘Create’ button
  • When Azure has finished creating the virtual network, the VNet70462 blade will open automatically

C. Pin the windows server images to the Startboard.

  • From the Azure Startboard click:

    • New > Compute > Azure Marketplace > Windows Server tile
  • On the Windows Server blade, click:

    • Windows Server 2008 R2 SP1 > Pin icon
    • Windows Server 2012 R2 Datacenter > Pin icon
  • Close all the open blades or click the home icon on the Jumpbar to go back to the Startboard and check that you now have two new tiles for these two servers

D. Prepare a Computer to Function as a Windows Server 2008 R2 Domain Controller (page xix).

1-4. From the Azure Startboard click:

  • Windows Server 2008 R2 SP1 tile > Create

    • Host Name: DC
    • User Name: [your name] e.g. JohnSmith
    • Password: [your password] e.g. JohnSmith70462 (Pa$$w0rd will not work)
    • Pricing Tier: [your choice]
    • Optional Configuration:

      • OS Settings:

        • Automatic Updates: Off
        • Time Zone: [your time zone]
      • Network:

        • Virtual Network:

          • VNet70462
        • Domain name:

          • Domain name: e.g. ContosoJS (contosojs.cloudapp.net)
        • IP addresses:

          • Private IP address:

            • IP address assignment: Static
            • IP address: 10.10.10.10
      • Storage account:

        • Create a storage account:

          • Storage: storage70462
    • Resource Group:

      • Group70462
  • Click the ‘Create’ button
  • When Azure has finished creating the VM, the DC blade will open automatically:

    • Click the ‘Connect’ icon
    • Save the .rdp file as e.g. DC.rdp
    • Double-click the .rdp file to log on with Remote Desktop

      • If the Computer box is empty, use the DNS name and the Remote Desktop public port e.g.:

        • Click All settings > Endpoints
        • Computer: ContosoJS.cloudapp.net:56124
      • Check the server details on the desktop background:

        • Internal IP should be 10.10.10.10
        • Host name should be DC

5-9. Follow instructions in the book.

10. This step didn’t appear during my setup…!

11. As per instructions in the book, except there are two warnings. For the first:

  • Static IP assignment warning: Yes

12. Follow instructions in the book.

13. Follow instructions in the book, but use the password that you previously chose for this machine.

14. Follow instructions in the book, except restart the machine using the Azure portal.

E. Prepare AD DS (page xx).

1. Check in the Azure portal that DC status is ‘Running’, and log on again using the same .rdp file you saved earlier.

2. If it doesn't open itself, start Server Manager:

  • In the Server Manager left pane, drill down through the following nodes:

    • Roles > Active Directory Domain Services > Active Directory Users and Computers > Contoso.com > Users

      • Right-click Users node > New > User
      • Fill out the boxes with the credentials listed in the book
  • In the Server manager center pane:

    • Right-click Kim Akers > Add to a group...
    • Type "Enterprise Admins; Domain Admins; Schema Admins" into the text box
    • Click the 'Check Names' button (names should then appear underlined)
    • Click OK > OK
    • Close the remote connection

3. From the Azure Startboard, click the ‘VNet70462’ tile. When the blade opens, click:

  • All settings > DNS servers

    • Enter: 10.10.10.10
    • Click the Save icon
  • Close all the open blades then restart DC via the Azure portal.

F. Prepare a Member Server and Join It to the Domain (page xx).

1. Check in the Azure portal that DC is running.

2. Create a new VM similar to DC, except this time with the following changes:

  • Host Name: SQL-A
  • Optional Configuration:

    • OS Settings:

      • Domain join:

        • Domain: Contoso.com
        • User Name: e.g. JohnSmith
        • Password: e.g. JohnSmith70462
  • IP address: 10.10.10.20

3-7. Log on with Remote Desktop and open an elevated command prompt:

  • Start > right-click the Command Prompt icon > Run as administrator

    • Enter the following commands:

      • netsh interface ipv4 show dnsservers
      • netdom query dc /domain:contoso.com
      • netdom query workstation /domain:contoso.com
    • Check the results of the first command show the DNS server IP as 10.10.10.10
    • Check the results of the second command shows DC
    • Check the results of the last command includes the machine name
  • Download the SQL Server 2012 and AdventureWorks installation files:

    • Either with Internet Explorer:

    • Or with Powershell, by entering the following commands:

      • Import-Module BitsTransfer
      • Start-BitsTransfer -source http://download.microsoft.com/download/4/C/7/4C7D40B9-BCF8-4F8A-9E76-06E9B92FE5AE/ENU/x64/SQLFULL_x64_ENU_Core.box -destination C:\SQLFULL_x64_ENU_Core.box
      • Start-BitsTransfer -source http://download.microsoft.com/download/4/C/7/4C7D40B9-BCF8-4F8A-9E76-06E9B92FE5AE/ENU/x64/SQLFULL_x64_ENU_Install.exe -destination C:\SQLFULL_x64_ENU_Install.exe
      • Start-BitsTransfer -source http://download.microsoft.com/download/4/C/7/4C7D40B9-BCF8-4F8A-9E76-06E9B92FE5AE/ENU/x64/SQLFULL_x64_ENU_Lang.box -destination C:\SQLFULL_x64_ENU_Lang.box
      • Unfortunately for Powershell, CodePlex uses some webscript-witchery to present the download without using a URL that points directly to the file, so the AdventureWorks files will need to be downloaded with Internet Explorer.
  • Double-click the .exe file to unpack the setup files.
  • Check for a new folder C:\SQLFULL_x64_ENU, with SETUP.EXE inside.
  • For the SQL-CORE machine, you’ll probably want to unzip the AdventureWorks files now, instead of having to do it through the command line later.
  • Shut down the VM through the Azure portal

G. Prepare a Second/Third/Fourth Member Server and Join It to the Domain (pages xxi, xxii).

  • Repeat section F with the following changes to create three more VMs:

    • Second member:

      • Host Name: SQL-B
      • IP address: 10.10.10.30
    • Third member:

      • Host Name: SQL-C
      • IP address: 10.10.10.40
    • Fourth member:

      • Host Name: SQL-D
      • IP address: 10.10.10.50

H. Prepare a Computer Running the Server Core Installation Option and Join It to the Domain (page xxii).

At the time of writing, there is no image for the Windows Server 2008 R2 Server Core installation option available in the Azure gallery. Fortunately, Windows Server 2012 allows for reconfiguring a server into the Server Core mode without a reinstallation of the operating system, so we can substitute a later version.

1. Check in the Azure portal that DC is running.

2. Create a new VM similar to DC, except this time with the following changes:

  • Windows Server 2012 R2 Datacenter
  • Host Name: SQL-CORE
  • Optional Configuration:

    • OS Settings:

      • Domain join:

        • Domain: Contoso.com
        • User Name: e.g. JohnSmith
        • Password: e.g. JohnSmith70462
  • IP address: 10.10.10.60

3-9. Log on with Remote Desktop and open an elevated command prompt:

  • Use the command prompt to check the network setup as before
  • Download and unpack the SQL Server files as before
  • If it’s not already running, open Powershell from the taskbar and enter the following:

    • Uninstall-WindowsFeature Server-Gui-Shell
  • When complete, close the remote connection and restart SQL-CORE through the Azure Portal
  • Log on again with Remote Desktop
  • Close the Server Manager window. At the command prompt (which should already be visible), enter the following:

    • powershell (the prompt should then appear with a “PS” prefix)
    • Remove-WindowsFeature User-Interfaces-Infra
  • When complete, close the remote connection and restart SQL-CORE through the Azure Portal
  • Log on again with Remote Desktop. This time you should see only the command prompt window
  • Close the remote connection and shut down the VM through the Azure portal

I. Final notes (or really, Getting Started).

Chapter 1

At the completion of Chapter 1 of the book, I have already come across a few gotchas that mainly seem to be related to the assumption of prior knowledge around Windows Server administration. So here are some final notes based on these observations:

  • To log on to one of the machines using the Kim Akers domain account, either create a new or edit the existing .rdp file and use the username “CONTOSO\Kim_Akers”. Remember to enter the correct password if you chose a different one from the server admin accounts. After logging on, check that the username shown on the desktop background appears as “Kim_Akers”.
  • While I was able to log on to e.g. SQL-A when it was the only machine running, I wasn’t able to log on to SQL-CORE unless DC also was running.
  • To start Active Directory Users and Computers, find it from the Start menu under All Programs > Administrative Tools. Create a new OU by right-clicking the Contoso.com node. To move the computers, click the Computers node and drag each computer to the SQL-Servers node.
  • Open Group Policy Management from Start > All Programs > Administrative Tools. Expand the nodes for Forest: Contoso.com, then Domains, then Contoso.com, then right-click the SQL-Servers node to Create a GPO in this domain, and Link it here…
  • In Group Policy Management again, click the Group Policy Objects node, then right-click the SQL-POLICY entry to edit. In the Editor, drill down to Computer Configuration > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security > Windows Firewall with Advanced Security > Inbound Rules, then right-click to Add a New Rule…
  • The phrase “when the ServerManager module is loaded” means you first have to enter the PowerShell command: Import-Module ServerManager. (For more info about modules, enter the command: Get-Help about_Modules).
  • In Lesson 2, Exercise 2, Item 4, there was no warning about searching for updates. I just clicked the ‘Skip’ button.
  • In Lesson 2, Exercise 2, Item 9 should read, “On the Database Configuration page…”. The image in Figure 1-29 is incorrect.
  • When connecting to SQL Server on SQL-B or SQL-CORE from SQL-A, only SQL-A will appear in the ‘Server name’ drop-down. Just type SQL-B or SQL-CORE into the box and click the ‘Connect’ button.
  • In Lesson 2, Exercise 5, Item 7, copying the file to C:\adventureworks2012 will produce an error when you try to attach the database. Instead copy the file to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA.
  • Copy the .mdf files on the Server Core installation by entering the following at the command prompt:

    • copy C:\AdventureWorks2012_Data.mdf "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf"
    • copy C:\AdventureWorksDW2012_Data.mdf "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_Data.mdf"
  • I arrange the tiles on my Startboard in the following order: Group70462, storage70462, VNet70462, DC, SQL-A ... SQL-CORE. When it comes time to delete the project, I’ll delete from the bottom (SQL-CORE) to the top (Group70462). The reason for this is that there’s a certain trick to deleting the VMs entirely. When the VMs are created, two disks are provisioned: one for the OS, and one for storage. When you delete a VM, the OS disk is not automatically deleted. So, before you can delete the storage account, you first need to delete all the individual disks that still appear inside it. They should be listed as ‘unattached’, but I experienced an issue where there was a disk appearing as attached to a VM that no longer existed. It seems this is not a new problem. The Powershell solution at https://social.msdn.microsoft.com/Forums/en-US/WAVirtualMachinesforWindows/thread/7381ea0e-0443-4b33-aa12-ba39df003409/#5913424e-909e-4f9d-80de-88b0d555d040 worked for me.

Chapter 2

  • After installing the ‘ALTERNATE’ named instances on SQL-A, SQL-B and SQL-CORE, I was unable to connect using SSMS running on SQL-A to SQL-B\ALTERNATE. The issue was actually exactly the same as this one. The solution in short, is to use Group Policy Management on DC to add a new inbound rule to the SQL-POLICY to allow UDP connections on port 1434. These are the steps I took:

    • Check in the Azure portal that DC is running
    • Log on to DC with Remote Desktop and open Group Policy Management (Start > All Programs > Administrative Tools > Group Policy Management)
    • In the left pane, expand the nodes for Forest: Contoso.com, then Domains, then Contoso.com, then click the Group Policy Objects node, then right-click the SQL-POLICY entry to edit.
    • In the Editor, drill down to Computer Configuration > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security > Windows Firewall with Advanced Security > Inbound Rules, then right-click to Add a New Rule…

      • Select ‘Port’, then Next
      • Select ‘UDP’ and ‘Specific local ports’ and enter 1434 in the box, then Next
      • Select ‘Allow the connection’, then Next
      • Check ‘Domain’, ‘Private’ and ‘Public’ check boxes, then Next
      • Choose a name, e.g. ‘UDP Port 1434 for SQL Server Named Instances’, then Finish
      • Right-click on the newly created rule in the right pane, then click on ‘Properties’
      • On the Scope tab, in the ‘Remote IP address’ section, select the ‘These IP addresses’ radio button, then click the ‘Add’ button
      • Select the ‘This IP address or subnet’ radio button and enter 10.10.10.0/24 in the box, then OK, and OK again
      • Via the Azure portal, restart SQL-A and SQL-B to propogate the new rules
      • Log on to SQL-A with Remote Desktop, start SSMS, and open a connection to SQL-B\ALTERNATE
  • Windows System Resource Manager can be installed through Server Manager: under the ‘Features Summary’ section click on ‘Add Features’; or in the left pane right-click the ‘Features’ node, then click ‘Add Features’. When the ‘Add Features Wizard’ appears, check the ‘Windows System Resource Manager’ box, then follow the prompts from there.

Chapter 3

  • When installing Analysis Services from the command line, a couple of useful switches to include are:

    • /UpdateEnabled=False
    • /INDICATEPROGRESS
  • You can find Reporting Services Configuration Manager under: Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools

Chapter 4

Chapter 5

  • Lesson 1, Exercise 1, Item 1, to create a local security group:

    • Start > All Programs > Administrative Tools > Server Manager
    • In the left pane, drill down to Configuration > Local Users and Groups > Groups
    • Right-click on the the Groups folder > New Group …
    • Enter the name of the group, then click on the ‘Create’ button
    • The name in the ‘Group name’ box will disappear, and it will seem that nothing happened. Click on the ‘Close’ button, then check the list of groups in the center pane, and you should see the new group has appeared.
  • Lesson 1, Exercise 1, Item 2, to create a local user account:

    • Start > All Programs > Administrative Tools > Server Manager
    • In the left pane, drill down to Configuration > Local Users and Groups > Users
    • Right-click on the the Users folder > New User …
    • Enter the name of the user and the password in the appropriate boxes. For convenience, I unchecked ‘User must change password at next logon’, and checked ‘Password never expires’. The password ‘Pa$$w0rd’ will work here, or if you prefer, use the same password from earlier. Then click on the ‘Create’ button.
    • Like when creating a local security group, the text and check boxes will reset, and it will seem that nothing happened. Click on the ‘Close’ button, and check the list of users in the center pane, and you should see the new user has appeared.
  • Lesson 1, Exercise 1, Item 3, to create a global security group:

    • Start > All Programs > Administrative Tools > Active Directory Users and Computers
    • In the left pane, drill down to Contoso.com > Users
    • Right-click on the the Users folder > New > Group
    • Enter the name of the group, then click OK
  • Lesson 1, Exercise 1, Item 4, to create a domain user account:

    • Start > All Programs > Administrative Tools > Active Directory Users and Computers
    • In the left pane, drill down to Contoso.com > Users
    • Right-click on the the Users folder > New > User
    • Enter the names and logon name of the user, then click Next
    • Enter and confirm the password, then click Next, then click Finish
  • Lesson 2, Exercise 3, Item 1: follow the same steps as Lesson 1, Exercise 1, Item 4 (above).

Chapter 6

  • Lesson 3, Exercise 1, Item 2 asks you to create a service account. From what I can tell, there could be two different interpretations of this:

    1. Create a regular user account as before, then configure it (as below) for SQL Server to use as its service account

      • This seems to be what the book is going for. I completed the steps below, but after the next Windows restart none of the other SQL Server instances on that machine would start. The server log told me that they didn’t have the ‘Log on as a service’ right, which was being overridden by the group policy configuration.
    2. Create a Managed Service Account

      • Don’t be fooled by the Managed Service Accounts node in Active Directory Users and Computers! According to a note at the link above, managed service accounts created using this will be missing essential attributes. Only Windows PowerShell should be used to create managed service accounts. To do this, log on to DC, run Powershell as an administrator (Start > All Programs > Accessories > Windows PowerShell > right-click Windows PowerShell > Run as administrator) and enter the following:

        • Import-Module ActiveDirectory
        • New-ADServiceAccount -Name SQL-SVC-A -Path "CN=Managed Service Accounts,DC=CONTOSO,DC=COM"
      • To see the new account, refresh the Contoso.com node in Active Directory Users and Computers and click on the Managed Service Accounts node, or run the following PowerShell command:

        • Get-ADServiceAccount SQL-SVC-A
      • Now log on to SQL-A, start a new elevated PowerShell session and enter the following:

        • Import-Module ServerManager
        • Add-WindowsFeature RSAT-AD-PowerShell
        • Import-Module ActiveDirectory
        • Install-ADServiceAccount SQL-SVC-A
  • Lesson 3, Exercise 1, Item 3:

    • Open Group Policy Management (Start > All Programs > Administrative Tools). In the left pane, drill down to Forest: Contoso.com > Domains > Contoso.com > Group Policy Objects > right-click SQL-POLICY > Edit…
    • If you’re using a Managed Service Account, skip this step. Otherwise, to grant the SQL-SVC-A user account the Log On As A Service right:

      • In the Group Policy Management Editor window, in the left pane, drill down to Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > click on User Rights Assignment
      • In the right pane, double-click on ‘Log on as a service’
      • In the Log on as a service Properties window, check the ‘Define these policy settings’ box, then click the ‘Add User or Group…’ button
      • In the Add User or Group window, click on the ‘Browse…’ button
      • Under ‘Enter the object names to select’ type SQL-SVC-A, then click on the ‘Check Names’ button; the entry should change to SQL-SVC-A (SQL-SVC-A@Contoso.com)
      • Click OK > OK > OK
      • The Policy Setting entry for ‘Log on as a service’ should now show as ‘CONTOSO\SQL-SVC-A’
    • To add the SQL-SVC-A user account to the Generate Security Audits policy, find ‘Generate security steps’ in the list in the right pane and follow the same steps as for ‘Log on as a service’ above (the one you just skipped if you’re using a Managed Service Account; when you’re done, the account name appear with a trailing $ i.e. CONTOSO\SQL-SVC-A$).
    • The last paragraph on page 250 mentions that when you want to use Windows Security Log as the target for an SQL Server Audit, you must configure the Audit Object Access policy for both success and failure, so we need to include that here:

      • In the left pane of the Group Policy Management Editor window, click on the Audit Policy node (directly above the User Rights Assignment node)
      • In the right pane, double-click on the ‘Audit object access’ item
      • In the Audit object access Properties window, check the ‘Define these policy settings’, ‘Success’ and ‘Failure’ boxes, then click OK
      • The Policy Setting entry for ‘Audit object access’ should now show as ‘Success, Failure’
  • Lesson 3, Exercise 1, Item 5, if you’re using a Managed Service Account, you don’t need to specify the passwords for the command line installation, but remember to append the account name with a ‘$’ i.e.:

    • setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=AUDITING_B /SQLSVCACCOUNT="CONTOSO\SQL-SVC-A$" /SQLSYSADMINACCOUNTS="Contoso\Kim_Akers" /AGTSVCACCOUNT="CONTOSO\SQL-SVC-A$" /IACCEPTSQLSERVERLICENSETERMS
  • Lesson 3, Exercise 3, Item 3, you may want to clear the security log before creating the new database to make it easy to pick out the relevant entries:

    • Open Server Manager (Start > All Programs > Administrative Tools >  Server Manager)
    • In the left pane drill down to Diagnostics > Event Viewer > Windows Logs > Security
    • In the right pane, click on Clear Log… > Clear
  • Lesson 3, Exercise 3, Item 4, check the security log:

    • When I checked the security log, I saw about a hundred new entries saying nothing more useful than ‘A handle to an object was requested’. I couldn’t see anything that suggested a specific action in SQL Server. There may be a way of exctracting more detail, but I haven’t been able to find out what it is. When I tried excercises 2 and 3 again, but specifying the application log as the destination instead of the security log, I saw only one entry in that log which had a clear reference ‘Starting up database 'Hovercraft'.’
    • An application log entry from a database audit specification that tracked object creation included much more detail, even showing the SQL statement that was executed ‘statement:CREATE TABLE AuditTestTable…’

Chapter 7

  • Lesson 1, Exercise 1, Item 2, making a copy of a database using the Database Copy Wizard was described in Chapter 4. To do this you will need to create a credential and a proxy (see the links in the section for Chapter 4). When creating the proxy, select ‘SQL Server Integration Services Package’ for the subsystem.
  • Lesson 1, Exercise 1, Item 6, to copy the file, first share the source folder with everyone (see the notes in the section for Chapter 4). Later on you’ll need to move files in the other direction to, so share the destination folder similarly.
  • Lesson 1, Exercise 2, Item1, to add a new firewall rule, see the notes in the section for Chapter 2.
  • Lesson 1, Exercise 2, Item 6, once you’ve created the certificate backup, copy the SQL_A_Cert.cer file to the Backup folder on SQL-B (this is needed for Exercise 3).
  • Lesson 1, Exercise 2, Item 7, once you’ve created the certificate backup, copy the SQL_B_Cert.cer file to the Backup folder on SQL-A. The Backup folder on each server should now have a copy of both certificates.
  • Lesson 1, Exercise 3, Item 10, it doesn’t mention this in the book, but as a final step it’s worth making some simple data changes in the AdventureMirror database on SQL-A. After you’ve completed Exercise 4 (failover), you can check to see that the same changes appear in the same place on SQL-B.
  • Lesson 2, Exercise 2, Item 1, the book asks you to publish all tables in the database. The Sales.Customer table has a computed column that relies on a user defined function, dbo.ufnLeadingZeros. To prevent errors during the initialization of the publication, when selecting the articles (objects) for publication, you can include the function from the User Defined Functions node. Alternatively, if you want some practice at troubleshooting, select only tables for publication and follow the steps under Item 4 further down.
  • Lesson 2, Exercise 2, Item 1, after I had run the wizard, the first item showed a warning saying that it was unable to configure the SQL Server Agent service to start automatically. To configure this manually, open SQL Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager). Find ‘SQL Server Agent (MSSQLSERVER)’ in the list then right-click > Properties > Service tab. Find the ‘Start Mode’ entry in the list and click on the word ‘Manual’, then from the drop-down that appears, click on ‘Automatic’, then click on OK.
  • Lesson 2, Exercise 2, Item 3, before configuring transactional replication, to prevent a replication error, configure the owner of the files in the source database to match the database owner:

    • In SSMS Object Explorer, right-click on the database node > Properties > Files page
    • Enter CONTOSO\Kim_Akers as the owner
    • As with snapshot replication, also include the ufnLeadingZeros UDF as an article for publication
  • Lesson 2, Exercise 2, Item 4, if you didn’t include the UDF for publication with snapshot replication earlier, you can resolve this with the following steps:

    • In the Replication Monitor left pane, drill down to Replication Monitor > My Publishers > SQL-A > [AdvWrksSnapshotRepl]
    • In the All Subscriptions tab, right-click on the entry then click on View Details
    • In the details window that opens, in the top pane, click on an entry that has an Error Message entry starting ‘Cannot find either column “dbo” or the user-defined function…’
    • In the bottom pane, you will see ‘Command attempted:’ followed by a CREATE TABLE statement that references the ufnLeadingZeros function
    • In SSMS Object Explorer, drill down to SQL-A > Replication > Local Publications > right-click on [AdvWrksSnapshotRepl] > Properties
    • In the Publication Properties window, select the Articles page, select the function, then click on OK
    • In SSMS Object Explorer, right-click on the [AdvWrksSnapshotRepl] node again, then click Reinitialize All Subscriptions
    • In the Reinitialize Subscription(s) dialog box, click Mark For Reinitialization
    • Wait until the entry in Replication Monitor shows a status of ‘Running’, then right-click on the entry, then click on View Details
    • In the details window top pane, click on an entry that has a Status of ‘Running’, then in the bottom pane, look for the message that says “The initial snapshot for article ‘ufnLeadingZeros’ is not yet available.”
    • In SSMS Object Explorer, drill down to SQL-A > SQL Server Agent > Jobs > right-click on SQL-A-AdvWrksSnapshotRepl-AdventureWorks Snapshot Repl-1 (the exact name may be different depending on the name you specified in the New Publication Wizard, but it will be the one that has a two-hourly schedule) > Start Job at Step…
    • In the dialog box that opens, click on Start
    • When the job has completed, click Close, then go back to Replication Monitor, view the details of the subscription again and in the bottom pane, look for the message “Bulk copied data into table ‘Customer’”
  • To  create a shared folder using the command line, you can use the net share commands. Enter the following at the C:\> prompt:

    • md C:\Backup
    • net share Backup=C:\Backup /GRANT:Everyone,CHANGE
    • The folder can be accessed from other machines in the domain with a network address e.g. \\SQL-CORE\Backup
  • When setting up merge replication, there are a couple of additional points to note:

    • Create a copy of the database as AdvWksMergeRepl, then set the owner of the files as mentioned for Lesson 2, Exercise 2, Item 3, above
    • When setting up the publication:

      • Don’t include the procedure dbo.uspSearchCandidateResumes from the article list. (The default for table articles is not to include full-text indexes, so excluding this procedure, which makes use of full-text indexing, will prevent an error.)
    • The steps in the book for setting up merge replication publication include a screenshot showing the Snapshot Agent credentials as contos\snapshot_agent. As no details are provided about how this account should be set up, I used CONTOSO\Kim_Akers instead.
    • When setting up the subscription:

      • Set the owner of the database as CONTOSO\Kim_Akers
      • Select push subscriptions
      • Use the default subscription type values that appear. (Push subscriptions have the default subscription type ‘Server’ mentioned in the book. Using pull subscriptions for merge replication requires more complex management of permissions.)
    • After the subscription has been successfully set up, in Replication Monitor look for the status of ‘Synchronizing’. Unlike the other replication methods, the status for merge replication doesn’t change to ‘Running’. If you right-click on the entry then click on View Details, in the bottom pane of the window that opens you can see a description of the currently processing item. If you see ‘Waiting 60 second(s) before polling for further changes’ then merge replication is running successfully.
  • Peer-to-peer replication seems to be the least used (and therefore least documented) of the replication types (even Redgate’s book on replication doesn’t cover it). I certainly found it challenging to get useful information from the Microsoft website (at the time of writing there isn’t a page that covers the 2012 version, but I found this page for configuring peer-to-peer replication on 2008 R2 to be the most useful). The steps in the book for creating the peer-to-peer publication will only be correct if SQL Server Agent on the server where you are creating the publication is not configured to start automatically and the server is not yet set up for distribution. The final exercise for this chapter, however, asks you to create the publication on SQL-A, which, if you have done the other excercises, is already set up for distribution. Also, peer-to-peer replication does not allow initialization using a snapshot, so you can’t use the New Subscription Wizard to set up the subscription. The trick (which is also Microsoft’s recommendation) seems to be in setting up every node as a distributor and then using the Configure Peer-To-Peer Topology Wizard to get things running. These are the steps that worked for me:

    • Log on to SQL-B:

      • Create a folder, C:\Replication, and share it with everyone as mentioned for Chapter 4, Exercise 2, Item 4, above
      • Check that the SQL Server Agent is configured to start automatically as mentioned for Lesson 2, Exercise 2, Item 1 above
    • Configure SQL-B as a distributor:

      • In SSMS Object Explorer, drill down to SQL-B > Replication > right-click on Replication > Configure Distribution…
      • On the Configure Distribution Wizard start page, click Next
      • On the Distributor page, select “'SQL-B' will act as its own distributor…”, then click Next
      • On the Snapshot folder page, enter \\SQL-B\Replication, then click Next
      • On the Distribution Database page, review the defaults, then click Next
      • On the Publishers page, select SQL-B, then click Next
      • On the Wizard Actions page, select ‘Configure distribution’, then click Next
      • On the Complete the Wizard page, review the summary, then click Finish
      • To confirm the setup, in SSMS Object Explorer, drill down to SQL-B > Databases > System Databases > distribution (refresh the System Databases node if you don’t see it)
    • On SQL-A, create a copy of the AdventureWorks2012 database as AdvWksP2PRepl

      • Set the owner of the files as mentioned for Lesson 2, Exercise 2, Item 3
    • Create the peer-to-peer publication:

      • In SSMS Object Explorer, drill down to SQL-A > Replication > Local Publications > right-click on Local Publications > New Publication…
      • On the New Publication Wizard start page, click Next
      • On the Publication Database page, select AdvWksP2PRepl, then click Next
      • On the Publication Type page, select ‘Peer-to-Peer publication’, then click Next
      • On the Articles page, select all available objects, then click Next
      • On the Article Issues page, review the issues, then click Next
      • On the Agent Security page, click on the ‘Security Settings…’ button, enter the security details for Kim Akers, click OK, then click Next
      • On the Wizard Actions page, select ‘Create the publication’, then click Next
      • On the Complete the Wizard page, enter a name for the publication e.g. AdventureWorks Peer-to-Peer Replication, then click Finish
      • To confirm the setup, in SSMS Object Explorer, drill down to SQL-A > Replication > Local Publications > AdvWksP2PRepl (refresh the Local Publications node if you don’t see it)
    • Create a backup of the AdvWksP2PRepl database and copy the .bak file to a shared directory e.g. \\SQL-B\Backup
    • Restore the backup on SQL-B with the same database name

      • Set the owner of the files as mentioned for Lesson 2, Exercise 2, Item 3
    • Configure the topology:

      • In SSMS Object Explorer, drill down to SQL-A > Replication > Local Publications > AdvWksP2PRepl > right-click on AdvWksP2PRepl > Configure Peer-To-Peer Topology…
      • On the Publication page, choose the peer-to-peer publication, then click Next
      • On the Configure Topology page, right-click on the background, then click Add a New Peer Node
      • Connect to SQL-B
      • On the Add a New Peer Node box:

        • From the Select Database drop-down, choose AdvWksP2PRepl
        • Check the ‘Connect to ALL displayed nodes’ box
        • Check that the ‘Use Push subscription’ radio button is selected, then click on OK
      • Look for the new node in the diagram, then click Next
      • On the Log Reader Agent Security page, click on the ‘…’ button, enter the security details for Kim Akers, click OK, then click Next
      • On the Distribution Agent Security page, click on the ‘…’ button for SQL-A, enter the security details for Kim Akers, then click OK
      • Check the “Use the first peer’s security setting for all other peers.” box, then click Next
      • On the New Peer Initialization page, select ‘I created the peer database manually…’, then click Next
      • On the Complete the Wizard page, review the summary, then click Finish
      • To confirm the setup, in SSMS Object Explorer, drill down to SQL-A > Replication and look for AdvWksP2PRepl under both Local Publications and Local Subscriptions (refresh the parent node if you don’t see it). Check for the same on SQL-B. Also, right-click on the AdvWksP2PRepl publication > Launch Replication Monitor, then look for AdvWksP2PRepl under both SQL-A and SQL-B.
      • You can test out the replication in both directions with either an UPDATE or DELETE statement. If you want to test with an INSERT statement, you’ll need to reseed the identity values of the test table on SQL-B.

Chapter 8

  • Lesson 1, Exercise 1, Item 2, refer to Chapter 1 for adding computers to an organizational unit.
  • Lesson 1, Exercise 1, Item 3:

    • To hold the downloaded file, I created a new folder, C:\iSCSI Software Target. If you want to download the iSCSI Software Target from an elevated PowerShell session (Start > All Programs > Accessories > Windows PowerShell > right-click Windows PowerShell > Run as administrator), use the following commands:

      • Import-Module BitsTransfer
      • Start-BitsTransfer -source https://download.microsoft.com/download/2/F/1/2F110B9C-6CB0-45F1-971B-4BA37395D820/iscsiTargetqfe6.exe -destination "C:\iSCSI Software Target\iscsiTargetqfe6.exe"
    • Double-click on the .exe file to unpack the files, then navigate to the x64 folder and double-click on    iscsitarget_public.msi
    • Accept the terms in the License Agreement and all the default selections to install the software, then click Finish when the setup is complete
    • Click Start > All Programs > Administrative Tools and look for Microsoft iSCSI Software Target
  • Lesson 1, Exercise 1, Item 5: you can confirm the firewall setup by using either:

    • Windows Firewall With Advanced Security:

      • Click Start > All Programs > Administrative Tools > Windows Firewall With Advanced Security
      • Click on Inbound Rules in the left pane and find the new rules in the list
    • netsh commands at the command prompt, e.g.:

      • netsh advfirewall firewall show rule name="Microsoft iSCSI Software Target Service-TCP-3260"
  • Lesson 1, Exercise 1, Item 6: I couldn’t create a successful connection from the client (e.g. SQL-C) to the iSCSI target on DC after configuring the iSCSI Software Target application using DNS Domain Name as the Identifier Type as shown in the book. However using IQN as the Identifier Type was successful. The IQN value is just the server’s domain name prefixed with “iqn.1991-05.com.microsoft:” e.g. iqn.1991-05.com.microsoft:sql-c.contoso.com. If you want to copy and paste from the client itself:

    • On the client server (e.g. SQL-C) click Start > All Programs > Administrative Tools > iSCSI Initiator
    • If a Microsoft iSCSI dialog box appears, click Yes, and if necessary re-try the iSCSI Initiator
    • In the iSCSI Initiator Properties window, click on the Configuration tab and look for the Initiator Name
  • Lesson 1, Exercise 1, Items 7 and 8: the Create Virtual Disk Wizard will ask you for the virtual disk size in MB not GB. Use 2048 for 2GB and 10240 for 10GB.
  • Lesson 1, Exercise 1, Item 10:

    • On the Initialize Disk dialog, ensure that both disks have check marks showing, select MBR for the partition style, then click OK
    • In the Disk Management graphical view, Disks 2 and 3 should both show as Online
    • Right-click on the blank area for Disk 2 > New Simple Volume…
    • On the New Simple Volume Wizard, keep clicking Next until you come to the Format Partition page
    • Enter a name for the volume label (I made this the same as the description I gave the corresponding virtual disk e.g. QuorumDisk70462), then click Next, then Finish
    • When the formatting has finished, you will see the disk name appear in the Disk Management list
    • Repeat the formatting for Disk 3
    • Note: viewing the Disk Management console from SQL-D will show the disks as Offline
  • Lesson 1, Exercise 1, Item 11:

    • To open the DNS console, click Start > All Programs > Administrative Tools > DNS
    • In the left pane, drill down to DNS > DC > Forward Lookup Zones > Contoso.com
    • Right-click on the Contoso.com node > New Host (A or AAAA)…
    • In the New Host window, enter SQL-Cluster as the name and 10.10.10.111 as the IP address then click Add Host
    • Click OK on the confirmation dialog, click Done on the New Host window and look for the new entry in the list
  • Lesson 1, Exercise 1, Item 12, to add a new user, see notes for Lesson 1, Exercise 1, Item 4 for Chapter 5 above (unfortunately, SQL Server 2012 clustering on Windows Server 2008 R2 doesn’t support the use of Managed Service Accounts).
  • Lesson 1, Exercise 1, Item 13, to edit the SQL-POLICY Group Policy Object, see notes for Lesson 3, Exercise 1, Item 3 for Chapter 6 above.
  • Lesson 1, Exercise 2, Item 1, installing the Failover Clustering and .NET Framework 3.5.1 features is similar to installing Windows System Resource Manager as mentioned in the notes for Chapter 2. The equivalent PowerShell commands to use are:

    • Import-Module ServerManager
    • Add-WindowsFeature NET-Framework
    • Add-WindowsFeature Failover-Clustering
  • Lesson 1, Exercise 2, Item 2, configuring the failover cluster per the book won’t work in Azure because the cluster can’t acquire a unique virtual IP address from the DHCP service. Instead, you will need to use this alternative method (the IP address from the book doesn’t need to be substituted with the link-local one):

    • Use the Create Cluster Wizard steps as given in the book, but instead add only a single node e.g. SQL-C. If the wizard doesn’t give the option of specifying an IP address:

      • In the Failover Cluster Manager left pane, click on SQL-Cluster.Contoso.com
      • In the center pane, expand the Cluster Core Resources section, expand the SQL-Cluster item, then right-click on the failed IP address, then click on Properties
      • On the General tab, select the Static IP Address radio button, and enter 10.10.10.111 for the address, then click on OK
      • In the Cluster Core Resources section again, right-click on the SQL-Cluster item, then click on ‘Bring this resource online’ and wait for both the cluster and IP address to show as Online
    • In the Failover Cluster Manager right pane, click on Add Node…
    • Use the Add Node Wizard like the Create Cluster Wizard, except this time specify the remaining node e.g. SQL-D
    • In the left pane, click on Storage and look for both disks with a status of Online
    • In Server Manager, on the newly added node e.g. SQL-D, refresh the Disk Management pane and look for disks 2 and 3 showing as Reserved. This is because only one node in the failover cluster can have ownership at a given moment; ownership should transfer during failover.
    • If you want to run validation tests at this point, in the Failover Cluster Manager left pane, click on Failover Cluster Manager, then in the right pane, click on ‘Validate a Configuration…’ to start the wizard. If the results show a network error like ‘Node sql-d.Contoso.com is reachable from Node SQL-C.Contoso.com by only one pair of interfaces’, this can be ignored as Azure has built-in network redundancy.
  • Lesson 1, Exercise 4, Item 2, to verify which server has control of the two SAN disks, open Failover Cluster Manager, in the left pane click on the Storage node and in the center pane, look under the Current Owner column.
  • Lesson 1, Exercise 4, Item 3:

    • On the Cluster Network Configuration page, to enter an IP address, check the box to the left of the IP Type column and uncheck the box in the DHCP column
    • Using the IP address in the book gave me an error, so instead I used 10.10.10.120
  • Lesson 1, Exercise 4, Item 4, once this step is complete, it’s worth connecting to the clustered instance SQL2012CLUSTER from the active node. Some useful queries to run before and after failing over between nodes are:

    • SELECT 'MachineName' AS [ServerProperty], CONVERT(varchar(500),SERVERPROPERTY('MachineName'                )) AS Value UNION ALL

SELECT 'IsClustered'                    , CONVERT(varchar(500),SERVERPROPERTY('IsClustered'                ))          UNION ALL

SELECT 'ComputerNamePhysicalNetBIOS'    , CONVERT(varchar(500),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

  • SELECT DATEADD(ms,-sample_ms,GETDATE()) AS WindowsStartTime FROM sys.dm_io_virtual_file_stats(1,1)
  • SELECT sqlserver_start_time AS SqlServerStartTime FROM master.sys.dm_os_sys_info
  • SELECT create_date AS TempDbStartTime FROM sys.databases WHERE name = N'tempdb'
  • Lesson 1, Exercise 5, Item 4, to use the Move-ClusterGroup cmdlet, first import the FailoverClusters module:

    • Import-Module FailoverClusters
  • Lesson 2, Exercise 1, Item 1, Part A, the specified Isolation connection security rule will also block your own RDP connection to the server. To avoid this, first add an exemption rule for your own computer:

    • First, find your public IP address by typing “What’s my IP address?” into your favorite search engine (note: your public IP address will probably be different to the IP address shown by ipconfig, which will be your IP address on your internet provider’s subnet)
    • Edit the SQL-POLICY Group Policy Object (see notes for Lesson 3, Exercise 1, Item 3 for Chapter 6 above)
    • In the left pane of the Group Policy Management Editor window, drill down to Computer Configuration > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security > Windows Firewall with Advanced Security > right-click on Connection Security Rules > New Rule…

      • On the Rule Type page, select ‘Authentication exemption’, then click Next
      • On the Exempt Computers page, click on the ‘Add…’ button
      • Enter the IP address you copied earlier, then click OK, then click Next
      • On the Profile page, select all of the profiles, then click Next
      • On the Name page, enter a name for this rule, then click Finish
      • Look for the new entry in the Group Policy Management Editor window
    • Now use the wizard again to add an Isolation connection security rule using the selections as noted in the book
  • Lesson 2, Exercise 1, Item 1, Parts B and C, to add an Inbound Port-based rule, see notes for Chapter 2 to access the New Inbound Rule Wizard, making selections as noted in the book.
  • Lesson 2, Exercise 1, Item 5, if you’re copying the installation command string from the .pdf version of the book, remember to remove line breaks and change “Pa$$w0rd” to your chosen password e.g. JohnSmith70462.
  • Lesson 2, Exercise 2, Item 3, restart SSMS before expanding the AlwaysOn High Availability node.
  • Lesson 2, Exercise 3, Item 1, using the IP address in the book gave me an error, so instead I used 10.10.10.222.
  • Lesson 2, Exercise 4, Item 1, to configure the availablility group, in SSMS Object Explorer drill down to SQL-C\ALWAYSON > AlwaysOn High Availability > Availability Groups > Availability Group Alpha > Availability Replicas > right-click on the replica to be configured e.g. SQL-C\ALWAYSON > Properties.
  • Lesson 2, Exercise 4, Item 2, to failover the availablility group, in SSMS drill down to SQL-C\ALWAYSON > AlwaysOn High Availability > Availability Groups > right-click on Availability Group Alpha > Failover….

Chapter 9

  • Lesson 1, Exercise 1, Item 2, I started the Create New Data Collector Set wizard by clicking Action > New > Data Collector Set.
  • Lesson 4, Exercise 1, Item 4, for the SQL Server service account, use NT Service\MSSQLSERVER.
  • When creating a data collector set from an exported trace definition script, the XML in the script is validated against the schema in the syscollector_collector_types_internal table (SELECT * FROM msdb.dbo.syscollector_collector_types_internal) for the Generic SQL Trace Collector Type type. This type requires at least one event and one filter to be included in the trace.

Chapter 10

  • No additional notes.

Chapter 11

  • Lesson 1, Exercise 1, Item 2, to add a new user, see the notes for Lesson 1, Exercise 1, Item 4 for Chapter 5 above. Note that the setup the book uses for the SQL-A-AGNT account suffers the same problem of preventing other instances from starting as encountered in Chapter 6. There are a couple of ways around this:

    • A temporary solution is to reset the account  for the service that you want to start (which will work until the next time the group policy is updated)
    • Disable the ‘Log on as a service’ rights assignment in the SQL-POLICY (which may affect the cluster setup from Chapter 8) and add SQL-A-AGNT as a Managed Service Account instead (see the notes for Lesson 3, Exercise 1, Item 2 for Chapter 6 above)
    • Reconfigure the required service(s) to use a different (new or existing) user account that has the ‘Log on as a service’ right
  • Lesson 1, Exercise 1, Item 3, to add a user to the Pre-Windows 2000 Compatible Access security group:

    • Open Active Directory Users and Computers (Start > All Programs > Administrative Tools > Active Directory Users and Computers)
    • In the left pane, drill down to Contoso.com > Builtin
    • In the right pane, double-click on the Pre-Windows 2000 Compatible Access entry
    • In the Pre-Windows 2000 Compatible Access Properties window, click on the Members tab, then click on the ‘Add…’ button
    • In the text box type SQL-A-AGNT, then click on the Check Names button
    • Click on OK, OK
  • Lesson 1, Exercise 1, Item 4, if you used a Managed Service Account, you can skip this step. Otherwise, to grant the ‘Log on as a service’ right, see the notes for Lesson 3, Exercise 1, Item 3 for Chapter 6 above.
  • Lesson 1, Exercise 2, Item 2, to view the application log:

    • Open Server Manager (Start > All Programs > Administrative Tools >  Server Manager)
    • In the left pane drill down to Diagnostics > Event Viewer > Windows Logs > Application
    • Look for an Information entry with the Job Engine as the Task Category
  • Lesson 2, Exercise 2, Item 5, to open the Log File Viewer, in SSMS Object Explorer, drill down to SQL-A > Management > right-click on SQL Server Logs > View > SQL Server Log.
  • To edit a registry key:

    • Open the registry editor by clicking Start, then type ‘regedit’ and hit Enter
    • In the left pane, drill down to the required folder and click on it
    • In the right pane, find the name of the key you need to change, right-click on it, then click on ‘Modify…’
    • You’ll probably need to set the MsxEncryptChannelOptions registry key to a zero
  • To find the SQL Server Agent Error Log, in SSMS Object Explorer, drill down to SQL-A > SQL Server Agent > Error Logs.
  • You probably already created a server certificate for SQL-A in Chapter 3 (you can also find details of how to backup the certificate in that chapter).
  • To successfully create a new certificate on the destination instance I had to copy the database, certificate and private key backup files to the default location for the destination server i.e. in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL, I copied the database backup into the Backup  folder and the encryption files into the DATA folder.
  • When restoring the encrypted database, remember to either check the ‘Relocate all files to folder’ check box on the Files page of the Restore Database window (if using SSMS), or use the WITH MOVE syntax (if using T-SQL), so that the restored .mdf and .ldf files will be created in the DATA folder of the destination instance.
  • To rebuild and restore the system databases:

    • Take a full copy-only backup of the master, model and msdb databases
    • In SSMS, click on Tools > Options
    • In the Options window, expand Environment, then click on Startup, then select ‘Open new query window’ from the drop-down (this will prevent Object Explorer from taking the only available connection when the instance is running in single-user mode)
    • Close SSMS
    • Open an elevated command prompt and change the context to the folder that holds the set-up executable:

      • cd C:\SQLFULL_x64_ENU
    • When using SETUP.EXE to rebuild the system databases, the syntax requires an admin account to be specified e.g.:

      • SETUP.EXE /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=ALTERNATE /SQLSYSADMINACCOUNTS=CONTOSO\Kim_Akers
    • Open SQL Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager), then in the left pane, click on SQL Server Services
    • In the right pane, right-click on the entry for SQL Server (ALTERNATE), then click on Stop
    • Do the same for SQL Server Agent (ALTERNATE) if it’s running
    • In the right pane, right-click on the entry for SQL Server (ALTERNATE), then click on Properties, then click on the Startup Parameters tab
    • Under ‘Specify a startup parameter’, type ‘-m’ then click Add, then OK, OK
    • In the right pane, right-click on the entry for SQL Server (ALTERNATE), then click on Start
    • Open SSMS as an administrator (Start > All Programs > Microsoft SQL Server 2012 > right-click on SQL Server Management Studio > Run as administrator)
    • Connect to SQL-A\ALTERNATE
    • Use T-SQL to restore the master database from the backup that was made earlier e.g.:

      • RESTORE DATABASE master

FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\Backup\master.bak'

WITH REPLACE

  • In the messages pane, you should see a message that includes ‘Shutting down SQL Server’
  • Close SSMS
  • Switch back to SQL Server Configuration Manager and refresh, and look for the SQL Server (ALTERNATE) entry showing as Stopped
  • Right-click on the SQL Server (ALTERNATE) entry, then click on Properties, then click on the Startup Parameters tab
  • This time, click on the ‘-m’ entry under ‘Existing parameters’, then click Remove, then OK, OK
  • In the right pane, right-click on the entry for SQL Server (ALTERNATE), then click Start
  • Open SSMS normally (reset your startup preferences if you want), open Object Explorer and check the master database is available
  • Proceed to restore the model and msdb databases using either SSMS or T-SQL

Chapter 12

  • No additional notes.

Resources

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.78 (18)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.78 (18)

You rated this post out of 5. Change rating

Related content

Technical Article

Building Windows Azure Cloud Service Applications with Azure Storage and the Azure SDK

Windows Azure and Azure Storage offers a new scalable and robust architecture that borrows much from the common feel of ASP.NET applications but brings plenty of new features as well. This paradigm shift from what has become traditional client-server architecture will offer new options to developers and headaches alike. While "the cloud" is not intended to be the answer for all applications and situations, it can only be a potential answer (another "tool" in the proverbial "tool belt") if you have at minimum a general understanding.

2009-11-23

1,893 reads