Making SQL Queries statement act on the same database across multiple servers

Here’s a useful way to apply queries across a number of sql 2008 servers

1. Open Microsoft SQL Server Management Studio
2. Under Database Engine select Central Management Servers
   (It is on the tab "Registered Servers" next to the tab "Object Explorer"
3. Right Mouse click "Central Management Servers" and select Register Central Management Server...
4. Choose the Server that you want to add in the "New Server Registration dialog"
   This has the effect of adding a node directly under "Central Management Server". This is used to host the Server groups
5. Right Mouse click on the new node and select "New Server Group..."
6. Give a group name
7. Right mouse click on the new group and select "New Server Registration..."
8. Select the servers that you want to be part of the group

This completes the setup.

To create queries that act across the registered servers
1. Right mouse click on a server group
2. "New Query"

When you execute the query you will receive the results with an additional "Server Name" column giving the result of that query on that server

Setting up Transaction log shipping

We have a high performance physical sql server that we want to backup via netApp shares. To get this to work we setup a second virtual sql server that uses netapp shares which are backup using the Snap manager tool.

Data is sent to the virtual sql server using Transaction log shipping. Here is how to setup transaction log shipping

1. Create a Backup of the database making shure that:
   - Recovery model = Full
   - Backup type = Full
2. Restore this backup onto the virtual sql server choosing the second option:
   - Leave the database non-operational, and do not roll back transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY)
3. After the backup is complete there is a green upward pointing arrow on the database on the virtual server and the database has (restoring..) after it.
4. Goto the physical server, right mouse click the database and select properties
5. In the select page choose "Transaction Log Shipping"
6. tick the check box "Enable this as primary database in a log shipping configuration"
7. Click the button Backup Settings
   This will open "Transaction Log Backup Settings" dialog
8. In the Network path to backup folder give the path on the physical server where the tranaction logs are to be found eg
   \\Myserver\LOGSHIPPING$
   In the field "If the backup folder is located on the primary server, type the path to the folder"
   G:\LOGSHIPPING
   OK
9. In the Secondary server instances and databases press the Add button
10. Press the connect button and select the secondary database
11. In "Destination folder for copied files"
    G:\LOGSHIPPING
12. OK
13. Check that the backup and restore jobs created on both servers run successfully