A repository for info that otherwise would have leaked out of my synapses.

Tag Archives: Integration Services

With the impending release of SharePoint 2010, I have decided to try to get our backing SQL infrastructure up-to-date, and also to re-architect our system to use SQL mirroring. This project has been easier than I expected, with a few inevitable bumps along the road:

The SQL documentation states that when setting up a new mirrored database connection, you must take a backup of the existing database, and restore it to the mirror server before configuring the mirror. It is a bit vague on the particulars. I found out the hard way that you must perform the recovery and not restore the database to a running state, otherwise the mirror setup will fail. The worst part is that the mirroring wizard will not give a helpful error… it will claim that ‘no network endpoint is available for the mirror connection’, or some such rot, leading you to believe that you have a problem with your firewall. So, make sure you do the following when setting up the database for mirroring:

Backup the database and transaction logs separately

Restore the database backup with the “WITH NORECOVER” option.

Restore the transaction logs with the “WITH NORECOVER” option.

Concerning SQL Express: SQL Express is approved for use as a Witness server in a SQL mirroring configuration. However, SQL Express will not work as a Witness out of the box. To get things running, you will need to:

You also may need to configure the instance to use the static TCP port “1433” for SQL Services (also in the Configuration Manager. On my installation, SQL Express defaulted to the use of dynamic ports for SQL, which made for a good deal of trouble in configuring the Windows firewall.

You may need to enable the SQL Browser service in “Configuration Manager”, and open UDP port 1434 to your management station. Without the browser service, I could not get SS Management Studio to talk to SQL Express. Boo

If configuring databases on Windows Server 2008 or 2008 R2, you will have the option to use the much-improved Windows firewall to secure your SQL instance. I have chosen to restrict TCP access to SQL services to only those hosts that need connectivity (at least for now). Unfortunately, SQL 2008 does not create any firewall rules or firewall rule templates for you, so you will need to do it yourself. Here are the required ports:

UDP Port 1434 – For the SQL Browser service… not needed in most cases, but required to enable connectivity to SQL Express instances in my case (see above).

TCP Port 5022 – The Mirroring Endpoint default port… required open on all SQL servers that will be used in a mirror configuration. SQL clients do not require connectivity to this port… only primary, mirror, and witness servers in a cluster.

TCP Port 135 and Program-based access to “%ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.exe” – Required to establish SQL Server Integration Services remote connectivity. These are DCOM-based services that use those annoying Dynamic RPC ports. SQL clients should not be allowed to connect to this port. I have it open only to the systems from which I plan to plan to perform SQL management operations.

Concerning Maintenance Plans: MPs are made somewhat more complicated on systems using mirroring. Clearly you want your Maintenance Plans to run regardless of where your primary mirror happens to be at any given time. However, DBCC and backup operations can be performed only on the primary database in a mirror set. So how do you ensure that your maintenance plans will always run on the primary? A common solution seems to be to create identical maintenance plans for both mirror servers, but to use the “Ignore databases where the state is not online” flag in the SS Management Studio database selector dialog (“WITH SKIP” T-SQL argument). This also brings up the question of how you can replicate those tediously configured Maintenance Plans between your different mirrors. The answer involves SSIS packages and the SQL Server Business Intelligence Development Studio (an optional component in a SQL Server installation):

Use SS Management Studio to connect to “Integration Services” on the SQL instance where you have configured your maintenance plans.

Navigate to “Stored Packages\MSDB\Maintenance Plans”… hey look… all of your maintenance plans are just SSIS packages!

Right-click the plan you want to copy, then select “Export Package”.

Select “File System” for your package location

Browse to the location where you want to save your package and click “OK”.

You could now use the BI Development Studio to edit the package file, but the smarter thing to do is to open the file using a text editor. I like “Notepad++”. Find and replace all instance of the name of your source SQL server with the name of your destination SQL server. If you fail to complete this step, your Maintenance Plans will fail with connection errors. Why? Because the SSIS export function preserves the local server connection strings, causing your destination system to attempt to backup databases on a remote computer instead of itself. Boo!

Connect to Integration Services on the destination SQL server. Navigate to the same “Maintenance Plans” directory, right-click and select “Import”. Again select “File System” for the package location. Browse to the file you just edited, and click “OK” to import.

Verify that the imported package is configured correctly using the Maintenance Plan editor.

You will need to update the schedules for your plans because schedules are not an integral part of an MP. This is “metadata” that does not get exported.

Remember to repeat this procedure every time you update your MPs on either server in your mirror configuration.