Advertisements

Mirroring SQL Databases

Recently I’ve been setting up mirroring of Microsoft SQL databases.  While the steps required are well documented on Technet (which is where I learned), they are spread over several pages.  There is no single list of steps so I decided to put one together documenting how I setup mirroring using SQL Server Management Studio.

What I’m not going to do is detail all the requirements or possible ways to set mirroring up.  For this information Technet is probably still your best resource.  So, the process below makes certain assumptions:

  1. You’ve already installed and configured the same version of SQL on two servers.
  2. These servers should have the drive setup so that the paths for database and logs files can match on both server. For example, if databases are stored at D:\SQLData on the primary server, this path needs to be available on the secondary server.
  3. These servers both members of the same domain.  This is not a hard requirement of mirroring, but it does make the process easier.  If your servers are in different domains see TechNet for the process of setting up security.
  4. The SQL server service is running as the same domain user on both servers. Again, not a hard requiremnet but it’s how I’ve done it.
  5. I am not including setting up a witness server or automatic failover.  For a configuration with these options, consult technet.

The basic process involves 3 steps, backing up from the primary, restoring to the secondary and configuring the mirroring.

Backup the database on the Primary server

  1. Open SQL Server Management Studio and connect as a user with appropriate permissions (probably sysadmin).
  2. Right-click on the database you wish to mirror and select Tasks->Back Up…
  3. Add a backup destination file with a name like database.bak.
  4. Leave the default options selected.
    1. Backup type: full
    2. Overwrite media:
    3. Backup to the existing media set
    4. Append to the existing backup set
  5. Click OK and let the backup run.
  6. Right-click on the database and again choose Tasks->Back Up…
  7. Change the Backup type: to Transaction log.
  8. Check that the destination file is the same.
  9. Click OK and let it backup again.


Restore the database on the Mirror (destination) server with “No Recovery”

  1. Copy the backup file “database.bak” to the Mirror server, preferably in the default SQL backup directory.
  2. Open SQL Server Management Studio and connect to the Mirror server as a user with appropriate permissions (probably sysadmin).
  3. Right-click the “Databases” object and select “Restore Databases.”
  4. Under “Source to restore” click the “From device” radio button, then click the browse button to the right.
  5. Click Add and select the backup file you copied from the Primary server. Click OK.
  6. Select both the full and transaction log backups you created earlier.
  7. Under the Destination for restore, use the drop down box to select the database name. This should match the database name on the primary server.
  8. Change to the Options page.
  9. Change the recovery state to “RESTORE WITH NORECOVERY” as shown below.
  10. Click OK to restore the database.

Configure Mirroring

  1. Return the SQL Management studio connected to the Primary server.
  2. Right-click on the database and choose Tasks->Mirror…
  3. Click the Configure Security button.
  4. Click next on the starting page.
  5. Choose whether or not to include a witness server instance. For this example we will choose no. Click next.
  6. Click Next on the Principal Server Instance page.
  7. Select or browse so the “Mirror server instance”  shows the name of the desired mirror server.
  8. Click the Connect button to the right. Enter credentials (if necessary) to connect again with sysadmin privileges.  Click connect.
  9. Enter the correct service accounts.
    1. I’ve always configured mirroring between SQL servers in the same domain, with SQL services running as the same user.  So I always enter something like the below.
  10. Click Next then Finish.
  11. You should then get a Success box. Click close.
  12. You will then be prompted with the below message. If you want mirror using the default Operating mode (High Safety without automatic failover) then click Start Mirror. For this example I will choose Do Not Start Mirroring.
  13. Change to the desired Operating Mode. (Below I’ve changed to High Performance which I believe requires SQL Enterprise)

  14. Click the “Start Mirroring” button.

  15. The Status should change to the below:

Congratulations, your database is now mirrored.

Notes:

  • With some backup software it may be necessary to recreate you backup jobs after configuring mirroring.

 

 

 

Advertisements

VBScript to create a My Documents Variable

I was recreating a login script for a client that mapped a drive to the location of their redirected My Documents.  Because they were in the middle of a migration the My Documents could be in a different spot depending on if a user had been migrated to a new environment or not. So I needed to be able to query the current location of the My Docs, much like the %userprofile% shows the path to the current profile.

Below is a chunk of VBScript I cobbled together from a variety of sources including ActiveXperts that reads the appropriate registry key value of the user that runs the script.  This chunk just outputs the path to the command line but the myDocsPath variable could also be used to map a drive.

(Please note that the line that starts “Set oReg=GetObject….” and the line below it need to be a single line)

—-Copy Everything Below—–

const HKEY_CURRENT_USER = &H80000001
const HKEY_LOCAL_MACHINE = &H80000002
strComputer = "."
Set StdOut = WScript.StdOut
 
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer &
 "\root\default:StdRegProv")

strKeyPath = "Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders"
strValueName = "Personal"
oReg.GetExpandedStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,myDocsPath
StdOut.WriteLine "Current My Docs path is: " & myDocsPath

 —-Copy Everything Above—–

Creating Lists of Installed Applications

Frequently while performing network assessments I need to assemble a list of all the programs installed on servers. Going to Add/Remove Programs and writing it down is a tedious process.  Installing applications like Belarc Advisor is a bit more intrusive then I want to be on another companies servers.  After some research today I found two solutions that work well:

For XP and Server 2003

http://www.billsway.com/vbspage/ShowScript.asp?tgt=txtfiles/InstalledPrograms.txt

The above contains the contents of a vbscript. You copy the contents into a text file and save it with a .vbs extension.  You can then double-click it. It will prompt you for a computer name or IP address. When it finishes running it generates a file that contains a list of all the programs installed.  It seems to work great with Windows XP and 2003, but only returns a partial list for Windows 2008. 

 For Vista, Windows 7, Server 2008 and 2008 R2

http://www.intowindows.com/how-to-get-list-of-installed-apps-in-vistawindows-7-without-any-software/

This link shows how to generate the list using wmi commands. It says it is for vista and windows 7, but seems to work on Server 2008 just fine. Unlike the vbscript, this must be run directly on the computer in question.

 Conclusion

Both these methods seem to work well, but they do generate files on the system that need to be copied off to be useful.  If you are using them on someone elses computers I would advise you get explicit permission before using these tool. 

Unrelated but also helpful.

If your assessment involves Dell servers, you might want to get the service tags for the servers.  Older Dells tend to put this sticker in inconvenient locations. Luckily you can query the servers for the tag using this handy vbscript.

http://lazynetworkadmin.com/content/view/13/6/

Using Windows Server 2008 as a RADIUS Server for a Cisco ASA

Recently I needed to get a Cisco ASA 5510 to use a RADIUS Server on Server 2008 to authenticate Active Directory users for VPN access. The ASA was already configured to use a Server 2003 RADIUS server, so much of the below was just replicating the existing configuration on a 2008 server. I suspect many of the settings are less than ideal and some are unnecessary, but the below steps worked for now.

Components

  • AD1:
    • Windows Server 2008
    • Also the domain controller
    • IP: 192.168.1.10
  • CiscoASA:
    • ASA 5510 (though I believe these instructions should work for all ASA models)
    • IP: 192.168.1.2

Cisco Configuration

I performed the Cisco configuration using the ASDM management tool. The same configuration could be achieved via the command line interface, but I found the ASDM was more convenient for checking existing settings and then replicating.

Launch ASDM and connecting to the ASA, I went to the Configuration view.

Create an IP Name object for the target

  1. Under the Firewall section, expand the Objects link and select the IP Names.
  2. Click the Add button at the top.
  3. Enter a descriptive name, the IP address and a description of the server. For this server I used
  4. Name: INT-AD1
  5. IP: 192.168.1.10
  6. Description: AD / RADIUS
  7. Click OK and then Apply

Create a new AAA Server Group

  1. Click the Remote Access VPN section.
  2. Expand AAA Setup and select AAA Server Groups.
  3. Click the Add button to the right of the AAA Server Groups section.
  4. Give the server group a name, like TEST-AD, and make sure the RADIUS protocol is selected.
  5. Accept the default for the other settings. And click OK

Add the RADIUS server to the Server Group.

  1. Select the server group created in the step above.
  2. Click the Add button to the right of Servers in the Select Group.
  3. Under the Interface Name select the interface on the ASA that will have access to the RADIUS server, most likely inside.
  4. Under Server Name or IP Address enter the IP Name you created for the RADIUS server above.
  5. Skip to the Server Secret Key field and create a complex password. Make sure you document this as it is required when configuring the RADIUS server. Re-enter the secret in the Common Password field.
  6. Leave the rest of the settings at the defaults and click Ok.

Setting Up RADIUS on Windows Server 2008

This part gave me the most trouble. The documentation from Microsoft was somewhat vague and other resources I found using the trusty Google method listed steps and addition pieces I knew to be unnecessary.

To perform the below steps you need Administrator permissions to the server that will host the RADIUS server. You also will need permissions to “Register” the server in AD. I believe this requires Domain Admin privileges.

Add the Network Policy Server function.

  1. Connect to the Windows Server 2008 server and launch Server Manager.
  2. Click the Roles object and then click the Add Roles link on the right.
  3. Click Next on the Before You Begin page.
  4. Select the Network Policy and Access Services role and click Next.
  5. Under Role Service select only the Network Policy Server service and click Next.
  6. Click Install.

 

After the role finishes installing you will need to set up the server using the Network Policy Server (NPS) management tool found under Administrative Tools.

Registering the server.

  1. After launching the NPS tool right-click on the entry NPS(Local) and click the Register Server in Active Directory.
  2. Follow the default prompts.

Create a RADIUS client entry for the ASA.

  1. Expand the RADIUS Clients and Servers folder.
  2. Right-click on RADIUS Clients and select New RADIUS Client.
  3. Create a Friendly Name for the ASA device. I used “CiscoASA” but if you had more than one you might want to make it more unique and identifiable. Make sure you document the Friendly Name used as it will be used later in some of the policies created.
  4. Enter the Server Secret Key specified on during the ASA configuration in the Shared secret and Confirm shared secret field.
  5. Leave the default values for the other settings and click OK. See Figure 1 for all the complete RADIUS Client properties.

Figure 1

Create a Connection Request Policy.

  1. Expand the Policies folder.
  2. Right-click on the Connection Request Policies and click New.
  3. Set the Policy Nameto something meaningful. I used CiscoASA because this policy is geared specifically for that RADIUS client. Leave the Type of network access server as Unspecified and click Next.
  4. Under Conditions click Add. Scroll down and select the Client Friendly Name condition and click Add…
  5. Specify the friendly name that you used when creating the RADIUS Client above. Click OK and Next.
  6. On the next two pages leave the default settings and click Next.
  7. Under the Specify a Realm Name select the Attribute option on the left. From the drop down menu next to Attribute: on the right select User-Name. Click Next again.
  8. Review the settings on the next page and click Finish.

Create a Network Policy.

  1. Right-click the Network Policy folder and click New.
  2. Set the Policy Name to something meaningful. Leave the Type of network access server as Unspecified and click Next.
  3. Under Conditions click Add.
  4. Add a UsersGroup condition to limit access to a specific AD user group. You can use a generic group like Domain Users or create a group specifically to restrict access.
  5. Add a Client Friendly Name condition and again specify the Friendly Name you used for your RADIUS client.
  6. Click Next. Leave Access granted selected and click Next again.
  7. (Important Step) On the authentication methods leave the default selection and add Unencrypted authentication (PAP, SPAP).
  8. Accept the default Constraints and click Next.
  9. Accept the default Radius Settings and click Next. Review the settings and click Finish.

Restart the Network Policy Server service.

  • This may not be necessary, but I did this at various points and cannot be certain the above steps work without restarting the service.

Test Your RADIUS Authentication

The ASDM utility includes functionality to test RADIUS Authentication.

  1. If necessary re-launch the ASDM utility.
  2. Return to Configuration -> Remote Access VPN -> AAA Setup -> AAA Server Groups.
  3. Select the new Server Group you created.
  4. From the Servers in the Selected Group section highlight the server you created. Click the Test button on the right.
  5. Select the Authentication radio button. Enter the Username and Password of a user that meets the conditions specified in the Network Policy created above then click OK.
  6. If everything works as designed you should see something similar to:

Save your Cisco Configuration

Don’t forget to save the running configuration to memory on your ASA. Otherwise you’ll lose all your settings the next time the device is rebooted.

Advertisements