Tag Archives: SQL Server 2005

Rewiring the users and logins in SQL Server

As a developer I find that I’m frequently backing up and restoring SQL Server databases between servers for development and testing purposes. However, each time I do the link between the login (a server concept) and the user (a per database concept) gets broken.

There is a stored procedure in SQL Server to wire it all back up again and I keep forgetting what it is. So here it is (my aide memoir):

sp_change_users_login: It maps an a database user to a SQL Server login.

The quick and easy way is as follows:

sp_change_users_login 'Update_One', 'myUserName', 'myLoginName'

 

Developing with SQL Server 2008, deploying on SQL Server 2005

I received the following by email today:

Hi Colin! I found your blog after googling for a bit about SQL Server. I had a question for you… As someone fairly new to .NET development, would it be easier to stick with SQL Server 2005 for now, or just install SQL Server 2008 express? I ask because I don’t yet know enough about the differences between the two to know if there will be any issues when developing small web applications with 2008, but then deploying them to a hosting provider with 2005. Hope that makes sense. 🙂

Cheers,
Sean

I don’t recommend moving to SQL Server 2008 if you will ultimately be deploying on SQL Server 2005.

If you don’t know the difference between SQL Server 2005 and 2008, yet your hosting provider only supports 2005 then you would be better off sticking to working with SQL Server 2005 on your system. The reason for this is that you don’t want to accidentally stumble into features of 2008 that are not supported on 2005.

Also, even if you were intimately aware of the differences between the two versions it is still a good rule of thumb to develop on a system that is as close to the eventual live system as you can. That way you won’t get any unexpected nasty surprises when you do deploy the application and suddenly realise that things are not quite as expected.

If you have already developed the applications then you can set the compatibility level of the database to mimic SQL Server 2005:

ALTER DATABASE [MyDatabaseName] SET COMPATIBILITY_LEVEL = 90

That way you should have a similar (although not necessarily quite the same) experience as if you were developing on a real SQL Server 2005 system. There will still be things that you cannot do. I don’t think backing up your 2008 database and restoring it on a 2005 system will work.

SQL Server / Visual Studio Install Order

Yesterday I paved my laptop in order to upgrade to Windows Vista. I’ve now started to reinstall everything from scratch again. However, one thing that didn’t work out was the installation of SQL Server 2005. No matter what I tried I could not seem to get it to install the SQL Server Management Studio – somehow it was convinced that it already existed. I eventually figured out why.

I’d installed Visual Studio 2008 first, and as part of that installation it installed SQL Server 2005 Express edition. The express edition does not come with SQL Server Management Studio. When I went to install SQL Server 2005 it refused to install the management studio saying that more up-to-date versions of the tools were already available on the machine. (Well, I suppose some of them were, at least the ones installed by Visual Studio 2008’s installer). Running the Service Pack 2 upgrade did not help either. It concluded that the client tools were not valid as part of the upgrade and refused to install them.

Eventually I came to the conclusion that it would be quicker, given my recent wiping of my laptop to just start afresh again and install things in the correct order. I suppose I was lucky to have that option. I am also lucky that I don’t activate Windows until I’m sure everything is installed correctly – after all I do have 30 days to activate Windows. I’d hate to have lost an activation of Windows because of a dodgy install.

So what is the installation order I’ve now used that works:

  • Windows Vista SP1
  • Windows Update (my install required 33 updates)
  • SQL Server 2005
  • SQL Server 2005 SP2
  • Visual Studio 2008

 

PLEASE NOTE: The above is what worked for me. I’ve also heard that it has worked for others too. It comes with no warranties of any kind.

If you are having difficulty installing your SQL Server you may like to ask a question on one of the many fine forums that are available for asking questions of that nature. I tend to hang out on Code Project and may be able to help there. If I’m not around then one of the many other great members can possibly help you on their database forum.

Installing SQL Server 2005 on Vista

Perhaps this is not so much an issue with installing SQL Server 2005 on Vista, but of the way I install SQL Server 2005 on Vista, or even more accurately install it on my laptop on Vista.

A SQL Server will expect to run constantly on the machine that it is installed on. However, not on my laptop. I generally have SQL Server turned off on my laptop because it uses resources that it does not need. I don’t frequently use SQL Server on my laptop but I do need it sometimes. Because of this, during the installation I customise it so that the SQL Server services do not start up automatically as they normally would.

There is a slight problem with this approach I’ve discovered when installing on Vista. SQL Server 2005 came out before Windows Vista and they don’t actually get along out of the box. You have to install SQL Server 2005 SP2 (or so several dialogs claim) before you can start working with SQL Server 2005 on Windows Vista.

At the end of the process for installing SP2 it will let you know that admin users on the Vista box will not be admin in SQL Server unless you are explicit about which users to add. It then launches a “SQL Server 2005 User Provisioning Tool for Vista” to allow you to set up the admin users. However, if the SQL Server services are not running it cannot do this – the User Provisioning Tool will run, but when you apply the changes it will popup an error message and quit. So, it would seem that what I should have done is let the installer get on with running SQL Server 2005 when it finished so that the admin users could be set up. However, I didn’t and it failed. So, without any users set up on the SQL Server I could not log in.

After hunting around on disk for this User Provisioning Tool I discovered that the SQL Server 2005 Surface Area Configuration tool will allow me to launch the tool by pressing the “Add New Administrator” link in its dialog. So, with the SQL Server services (all of them indicating in the User Provisioning Tool, in my case the Database Engine and the Analysis Services) running I add myself to the list of users, click OK and…. A moment later everything seem to work. There is no confirmation, the dialog just goes away without any error messages. To test it worked I opened up the SQL Server Management Studio and attempt to log in. It works. I’m happy.

Now, finally, I go and limit the amount of memory I’m prepared to allow SQL Server to use. See my post on managing SQL Server’s memory usage. It is a laptop after all…

Update:
Please note that this was using SQL Server 2005 Developer Edition.