Archive for the ‘MS SQL’ Category

After SQL was removed from Failover Cluster Manager I was unable to uninstall it. The following error was logged in the application logs:

Product: Microsoft SQL Server 2008 Database Engine Services — Error 25012. There was an error attempting to remove the configuration of the product which prevents any other action from occuring. The current configuration of the product is being cancelled as a result.

To correct this I needed to follow the steps below to tell SQL it was no longer in a cluster. Then I was able to uninstall normally.

1. Open Registry Editor (Start -> run -> type in ‘regedit’)

2. Navigate to the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ClusterState\SQL_Engine_Core_Inst

3. Change the value of this “SQL_Engine_Core_Inst” key from 1 to 0.

4. Uninstall as normal through Programs and Features

While it’s possible to scan through logs to determine when the SQL service was last restarted, that is not always the quickest option. There is a simple query which will display the date and time of the last restart of the SQL service.

select min(login_time) from master..sysprocesses

I needed a way to be notified by email when a SQL failover occurred. The simple solution for this is to setup a job to send an email and set this job to be triggered when SQL agent starts. The SQL agent service on the passive server starts when a failover occurs.

The instructions are for SQL Server 2008 but the steps should be similar in other versions.

First we need to setup a SQL mail profile to connect to a mail server. This mail service does not need to be installed on the same server as SQL. If this is already configured skip to the “Configure the SQL job” section below.

Setup SQL mail profile:
1. Open Management Studio, connect to your SQL cluster, and expand Management

2. Double click Database Mail to start the Mail Configuration Wizard

3. Select Set up Database Mail by performing the following tasks -> Next

4. Enter a relevant profile name then hit Add next to SMTP accounts

5. Enter the email address you want to use for these alerts. I highly recommend using an existing user and enabling basic authentication. Otherwise you may have to allow the SQL server to send through your mail server without authentication.

6. Click Next -> Next -> Next -> Finish

Configure the SQL job:
1. Expand SQL Server Agent

2. Right click Jobs -> New Job

3. Fill out the General page with relevant details then click the Steps page -> New

4. Enter an appropriate name for the step and set type to Transact-SQL script (T-SQL)

5. Enter the following in the Command field. Make sure to modify the values as needed.


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'My mail account name',
@recipients='alerts@mydomain.com',
@Body='SQL Agent has started on the passive server.',
@from_address= 'alerts@mydomain.com',
@subject='SQL Cluster Failover Alert';

6. Click OK

7. Click the Schedules page -> New

8. Name the schedule appropriately and set schedule type to “Start automatically when SQL Server Agent starts”

9. Click OK -> OK

10. Right click the new job and select “Start job at step” to test the new job and make sure it can send mail properly

You may run across the situation where SQL has been locked down to the “sa” user and the password was lost/forgotten. In this situation you can start SQL in single user mode which allows you to connect with Windows authentication again.

1. Open the Configuration Manager tool from the “SQL Server 2005| Configuration” menu

2. Stop the SQL Server Instance you need to recover

3. Right click the MSSQL Server instance and choose properties

4. Navigate to the “Advanced” tab, and add “;–m” to the end of the list in the “Startup parameters” option

* NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

5. Click the “OK” button and restart the SQL Server Instance

6. You will now be able to access SQL using Windows authentication to reset the “sa” user’s password.

7. Finally Remove the “;-m” from the startup parameters using the Configuration Manager and restart SQL

Sqluserfail

Run the following query on the database in question to “resync” this login information:

EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password'

Make sure to replace UserName and Password with the appropriate login information. If you don’t know the current password and the user already exists, simply run the command without the NULL and Password fields.