How to install SQL Configuration Manager

When moving Sharepoint Foundation 2010 databases, the official advice is to set up a SQL Alias so that the Web Application Servers point at the new SQL server while using the old SQL Server name.

In order to do this, you need to have installed SQL Server Configuration Manager on the Web and Application Servers.

It's very easy to install SQL Server Configuration Manager without installing all the rest of SQL Server: just install the 'Client Connectivity Tools'. Ensure you install the version of the tools which matches the version of SQL Server installed on your database server.

• Choose Install -> New installation or add features to an existing installation.
• Choose all defaults until you get to the Feature Selection tab
• Select Shared Features -> Client Tools Connectivity
• Choose all remaining defaults and confirm installation

And that's it. No restart required. I did find that CPU on the server shot up to 100% for much of the install in our QA environment, so I don't recommend doing this in anger during office hours. Leave it to out of hours if you can.

TSQL - Who is a sysadmin on this server anyhow?

SELECT  p.name AS [Name] ,
        r.type_desc ,
        r.is_disabled ,
        r.create_date ,
        r.modify_date ,
        r.default_database_name
FROM    sys.server_principals r
        INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
        INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE   r.type = 'R'
        AND r.name = N'sysadmin'

Which is a heck of a lot easier than faffing about clicking things...

Oh what a horrible error!

RAISERROR('Error performing SQLBackup backup.', 16, 1)SQL Backup v7.3.2.12                                                                                                                                                                                                                                             ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ERRSQB: 5240 (SQB service did not acknowledge receipt of data. (WAIT_TIMEOUT)) ()

And the log backups completely and utterly failed. Trawling round the internet gave me one post in a Red Gate Forum, and that looked scary. Removing mutex objects? Restarting the server (not exactly an option)?

However, simply restarting SQL Backup itself solved the problem. As to why it happened? I'm not sure. The cluster hadn't failed over, or received any patches. It might be co-incidence that I'd just applied the licence key after having run out of evaluation period. It might not.

We now have log backups again. Phew.

So how do I enable SQLCMD Mode?

Well, in SSMS.exe, you click on 'Query' then 'SQL CMD Mode'. Simples!

But wait! There's a catch if your script has variables, or os prompts:

Because you are not starting SQLCMD from the command line, there are some limitations when running Query Editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

What then?

Open up the Command Prompt, and load your script in thusly:

sqlcmd -S servername -U username -P password -i C:\Folder\Script.txt

And then it'll all work nicely, assuming that the script itself is sensible. For more parameters for the SQLCMD utility, see here.

Exporting employeeStartDates and employeeEndDates to SQL in FIM

Twice in the past six months, I've gone looking for this answer. The second time round, I came across my own post to Technet, which was a little embarrassing in a sitting-alone-at-my-desk manner.

Anyhow. Before I go looking a third time, this is how to get an employeeEndDate and an employeeStartDate out of the FIM Metaverse and into SQL date or date-time fields.

In VB.Net. Other languages exist. This is just the code for the Advanced Flows that you'd set up from FIM Synchronization Service: it relies on there being nothing more than the relevant dates. You may wish to build in more checks.

Select Case FlowRuleName
            Case "exportNonEmployeeEndDate"
                If (mventry.ObjectType.Equals("person")) AndAlso mventry("employeeEndDate").IsPresent Then
                    Dim NonEmpEndDateTime As DateTime = Convert.ToDateTime(mventry("employeeEndDate").StringValue)
                    Dim NonEmpEndDate As String = NonEmpEndDateTime.ToString("yyyy-MM-dd")
                    csentry("enddate").Value = NonEmpEndDate


                End If

            Case "exportNonEmployeeStartDate"
                If (mventry.ObjectType.Equals("person")) AndAlso mventry("employeeStartDate").IsPresent Then
                   Dim NonEmpEndDateTime As DateTime = Convert.ToDateTime(mventry("employeeStartDate").StringValue)
                    Dim NonEmpEndDate As String = NonEmpEndDateTime.ToString("yyyy-MM-dd")
                    csentry("startdate").Value = NonEmpEndDate


                End If

        End Select

As with all code on the internet, check it in the Development System before you throw it into the Live environment. And take backups before you get going. Just in case.

Also, has it really been a year? Sheesh. Sorry about that. 

What if you've been a fool with log shipping?

And managed to set your primary database as your secondary database, because, when you scripted out the T-SQL to create log shipping, you forgot to change server half way through... (yes, I did it twice today in a bit of a DR scenario).

First, disable the Log Shipping Copy and Log Shipping Restore Jobs on the Primary Server. You won't be able to do this by right clicking on the SQL Agent job and chosing 'disable'. You will if you go into the properties for the job. You won't, at this point, be able to delete the job.

Then, check the msdb secondary log shipping tables.

SELECT *
FROM msdb..log_shipping_secondary_databases

SELECT *
FROM msdb..log_shipping_monitor_secondary

SELECT *
FROM msdb..log_shipping_secondary

Verify that the database(s) is indeed in the list here.

Run the following SQL to delete the erroneous secondary databases

DELETE
FROM msdb..log_shipping_secondary_databases
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_monitor_secondary
WHERE secondary_database = 'DBName'

DELETE
FROM msdb..log_shipping_secondary
WHERE primary_database = 'DBName'

After you have run this script for each database, the LSAlert job will stop erroring.

You will also, now, be able to delete the erroneous LSCopy and LSRestore jobs from SQL Agent.

As always, use at your own risk.

Collation

Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid = b.employeeid

If the collation on employeeid in table a does not match the collation for employeeid in table b, there will be a nasty error, along the lines of "Cannot resolve collation conflict for equal to operation"

Easy enough to deal with - tell SQL to use what *should* be the default for the database(s) in question. This worked when I was dealing with two databases with totally different collations on the same server - the default server collation didn't trickle down to a database imported from another, older, server.

SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid COLLATE DATABASE_DEFAULT = b.employeeid COLLATE DATABASE_DEFAULT

This is a lot simpler to implement than any solution that involves trying to re-collate one of the columns to the other table/database/column's collation. It's vastly more generic.