If you were trying to open si.blogs yesterday, you noticed it was down for a couple of hours.
But first, I have a confession to make. Si.blogs ran on Access database until yesterday. The database file was 300+ MB if I compacted the file it fall to around 200 MB. I know, I know, it is a terrible thing to do, but it worked. And it worked for a while with no trouble at all.
Well, yesterday I moved the database to Microsoft SQL Server 2005 Express Edition. It was a smooth process. I used Access’s up-sizing wizard to move the database structure and data but I had to port Access queries to SQL views by hand. I ran couple of queries from the code through SQL Server Profiler that suggested and created couple of indexes and I was done.
Then I moved other databases from MSDE to SQL Server Express I bumped into old trap.
If you have a login on old SQL server and you restore a backup of the database from that server on a new one you cannot create a new user on the server and login. Luckily I remembered there is a sp_change_users_login stored procedure that maps an existing user in a database to a server login. Two seconds per database and I was done.