SQL Server Wave

I very much liked the idea of Contained Databases as it would make it much easier to move databases across different servers and even use this feature as part of a disaster recovery strategy. Here is really good article that explains how Contained Databases work in Denali:

http://www.sqldbadiaries.com/2011/02/01/contained-databases-in-sql-server-denali/

While very excited about this feature, I came across a big issue as it is not supported on databases that are either part of Replication, Change Data Capture or Change Tracking.

Now, as most of the databases I have in production are replicated in one way or another, it simply will not be possible to use it.

I was looking for an easy way of testing new versions of SQL Servers and Windows Servers without having to invest on another machine or taking the risk of breaking my desktop computer.

I then came across VMware player and was surprised to see how easy it was to install it and it did allow me to do all the testing without ever requiring another machine. In a nutshell, it installs another virtual computer within an existing computer.

These are the steps I followed:

1. Download VMware Player (free) from here:

http://www.vmware.com/products/player/

2. Download an Operating System:

I downloaded a trial version of Windows Server 2008 R2 which last 180 days, more than enough for my purpose.

3. Install VMware player

The installation is very much straight forward. While installing, it will ask for an Operating System installation file.

Job done. There is now another machine available in my desktop computer and I can test anything I like. I installed SQL Server Denali CTP3 and enjoyed playing with it without having to worry about breaking my existing setup.

One thing to bear in mind is that the machine on which VMware Player is installed needs to have enough resources to share. When installing VMware, it automatically allocated 40gb of disk space, 1Gb of RAM and 1 CPU. Those setting are entirely customisable.

 

I needed to set a variable to YYYYMMDD.

When using the Month() and Day() function in Expression Builder, it returned a single character back when the value was less than 10. So the value I was getting back was YYYYMD using the following expression:

(DT_WSTR, 4) YEAR( GETDATE()  ) + (DT_WSTR,2)DatePart("m", getdate()) + (DT_WSTR,2)DatePart("d", getdate())

The workaround is to use the RIGHT() function:

(DT_WSTR, 4) YEAR( GETDATE()  ) + RIGHT("0" + (DT_WSTR,2)DatePart("m", getdate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("d", getdate()), 2)

The above will return the right format: YYYYMMDD.

I recently had to delete records from a table regularly, every 10 minutes. I first obviously thought of setting up  a SQL Agent Job, easy!!!

Now here is my problem, I am using SQL Server Express Edition, which doesn’t come with SQL Server Agent features. So the SQL way wasn’t an option. I then realised that I could setup a scheduled task in Windows (XP) that would run a batch file every 10 minutes, job done!

Here is an example of the batch file:

@ECHO OFF
cd C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
SQLCMD -SserverName\instanceName -Uusername -Ppassword -Q"delete from DatabaseName.dbo.TableToDelete"
@ECHO OFF

Save it as something like Query.bat and call it via a scheduled task.

A business critical SQL Server job failed during the weekend without anyone knowing about it. When some errors are not predictable, a good system should at least send notifications when things go wrong. In this case, it would have been appropriate if an email was sent to the relevant person with as much details possible about the error.

In SQL Server, we need to setup the following two features:

1. Firstly, Database Mail; it should be enabled and configured. This has been covered in detail here.
2. Secondly, an Operator that will be used by SQL Agent jobs to notify the relevant person or group.

Let’s create a new Operator:

In the next step, all we need is specify the email address of the person or group to receive notifications

Now that the Operator is ready,  any sql agent job can use it

Last but not least, let’s make sure that SQL Server Agent is able to use the Database Mail profile

That’s it.

I was creating a web service that processes data and saves into relevant tables. Should there be any error in the process, an error record was created. Although there are many ways to be alerted when an error occurred, I felt that in this case it was more appropriate to use a Stored Procedure to send an error email.

In SQL Server 2005/2008, the Database Mail feature need to be setup. The Database Mail feature allows SQL Server to send emails to end users. The only pre-requisite is to have an SMTP server available.

Database Mail is now configured and ready for use. Let’s send a test mail to confirm that it is configured properly.

Now that we have Database Mail working, we can use the following syntax to send email:

EXEC [msdb]..[sp_send_dbmail]
@profile_name = 'testProfile',
@recipients = 'To@email.com',
@subject = 'Error Message',
@body = 'Include error message',
@body_format = 'HTML'

I have recently upgraded SQL servers from 2000 to 2008. SQL server doesn’t automatically upgrade the databases to the latest version ie 100.

So I had to change the version of each database manually using the following script:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

To update all databases at once within the SQL instance, the following script might be useful.

select 'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 100'
from sys.databases
where name not in ('master','tempdb','model','msdb')

That’s it.

Best wishes and lots of success for 2010!

Cheers.

I recently upgraded one of my Data Import SSIS 2005 package to 2008. That package ran fine in VS2005. Visual Studio 2008 happily upgraded it to the latest version with the usual Provider upgrade warnings, Provider=SQLNCLI10.1; there was no particular mention of the above error.

The package contains a connection to a 2007 excel file, here is how it looks like:

So here is the error popup I got when I clicked the preview button, the Excel Connection Manager was failing to connect to the file:

Solution:

Download the latest “2007 Office System Driver: Data Connectivity Components”  from here and install it.

After the install, I could happily preview the data in the excel file.

Hope this helps.

While working on sql server consolidation, I was required to transfer all logins to the new sql server instance. I was worried about not knowing the passwords for a few Windows domain logins. I then realized that passwords are not required when transfering them.

All one needs to do is run the scripts available at the following link:

http://support.microsoft.com/kb/918992

The script will generate the script for creating all logins under the sql instance. For example:

– Login: Test
CREATE LOGIN [Test_Login] WITH PASSWORD = 0x01006B867DAEEEBC1207A2232FA7B155A7F7670543341FFF012F HASHED, SID = 0x0393B11B9324C04F930A7F951F05EFFF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see in the above example, the password is included albeit encrypted.

The good thing is that you are not required to know the password. The bad thing is that you will end up with a new login having a different sid, therefore not matching with the user of the same name in the database. In that case, all you need to do is to run the following script (courtesy of sqlservercentral.com) to find and remap them:

/**************************ORPHANED USERS****************/
/*** To be run against each db ***/

DECLARE @UserName nvarchar(255)

DECLARE Cursor_OrphanedUser cursor for

SELECT NAME FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0×01)
and suser_sname(sid) is NOT null ORDER BY name

OPEN Cursor_OrphanedUser

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + ‘ Synchronization of Logins in Progress’

EXEC sp_change_users_login ‘Update_one’, @UserName, @UserName

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

END

CLOSE Cursor_OrphanedUser

DEALLOCATE Cursor_OrphanedUser

/************************END ORPHANED USERS********************/

Hope you will find this useful.