A nice little change in SQL Server 2005 has been the ability to change the collation of the server.  Back in the day, circa SQL 2000, changing collation and reinstalling SQL Server were pretty much the same thing.  By pretty much, I mean the exact same thing.

In SQL Server 2005 you can now do this in a few easy steps, the following is from MSDN:

1. Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
2. Export all your data using a tool such as bulk copy.
3. Drop all the user databases.
4. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
   start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
5. Create all the databases and all the objects in them.
6. Import all your data.

Well, the steps aren’t exactly easy and it might be a lot easier to install SQL Server with the correct collation to begin with.  But when a friend calls with this questions, this is the answer.

PASS announced next year’s summit the other day.  Be like me and start thinking ahead for next year’s summit since it should be interesting with it being shortly after the launch of SQL Server 2008.

The 2008 PASS Community Summit will be held:
November 19-21, 2008
Seattle, Washington
Washington State Convention & Trade Center

Don’t miss the largest event dedicated to SQL Server & Business Intelligence education in the world! 
The Summit will draw over 2,000 users, SQL Server MVPs, developers, and Microsoft experts.  There will be more than 130 technical sessions on SQL Server & Business Intelligence, as well as an Exposition that will showcase all of the latest technologies and solutions related to SQL Server & BI. 
Mark your calendar for PASS Summit 2008 – and join us at the only event hosted for users, by users.

PASS is the Professional Association for SQL Server & Business Intelligence
PASS is more than a Summit or a website – we are a community of more than 25 thousand Database Administrators, Business Intelligence Experts and Application Developers from all over the world. 
We are looking for like-minded individuals like you… 
We are looking for people who have a PASSion for technology, innovators who push the limits and make full use of technology in developing creative business solutions (Microsoft SQL Server/Business Intelligence). 
We are looking for those special people who are driven to learn, and enjoy engaging with colleagues on topics of technical interest, as well as those who like to challenge themselves to excel in their own professional and personal development. 
If this sounds like you – join us – and help us develop new ways of thinking about the technology we use and the contribution that we all make to the industry. 
Come and see why PASS is a Community you need to be a part of!

Now the big question is can I come up with a decent topic to submit and then remember to submit it.

For those unfamiliar with encryption in SQL Server, this article is a nice introduction to symmetric encryption in SQL Server 2005.  I’ve only used encryption a few times and it’s definitely been the godsend that the article states. 

I referenced the article recently when I had to restore a database on a new server that had data that was encrypted on the existing server.  I was actually surprised in the end how easy this task was to do.

1. Backup the database on the [SOURCE] server.

2. Backup the database master key on the [SOURCE] server.

BACKUP MASTER KEY TO FILE = ‘C:\MASTER.KEY’

ENCRYPTION BY PASSWORD = ‘password’

3. Backup the service master key on the [SOURCE] server.

BACKUP SERVICE MASTER KEY TO FILE = ‘C:\SERVICE.KEY’

ENCRYPTION BY PASSWORD = ‘password’

4. Restore the database master key on the [DESTINATION] server.

RESTORE MASTER KEY FROM FILE = ‘C:\MASTER.KEY’

DECRYPTION BY PASSWORD = ‘password’

ENCRYPTION BY PASSWORD = ‘password’

[ FORCE ]

5. Restore the service master key on the [DESTINATION] server.

RESTORE SERVICE MASTER KEY FROM FILE = ‘C:\SERVICE.KEY’

DECRYPTION BY PASSWORD = ‘password’

[FORCE]

6. Restore the database on the [DESTINATION] server.

And viola… I was now able to decrypt the data.  These steps, of course, only work if there isn’t a service master key already on the new server.  And some day I’ll likely run into that situation.  But I’ll blog that then…

EDIT:  My bad, restoring the database master key is bad.  Fortunately the time I had to do it there was only a single database on the test server so there was no negative impact at the time.  Unfortunately, I blogged badly following that.