SQL Server–have another instance redirecting to existing SQL Server

I was in a need to have an SQL instance renamed.

This is because I have SQL instance on my server and wanted to have it on other computers as well so I don’t need to change the Connection Strings each time I deploy.

I first tried to rename it but to no avail. Then I found this little neat trick to have another instance that can “redirect” to the original server (e.g. you have (local)\Instance and want it to redirect to (local)).

To do this follow the instructions bellow:

1. Go to Start Menu => Microsoft SQL Server 2008 => Configuration Tools => SQL Configuration Manager.

2. In the SQL Configuration Manager expand the SQL Native Client 10.0 configuration node

3. Right click Aliases node and select “New alias”

4. In the Alias – new popup, in the Alias Name field type the name you want to have for redirection, in the server field type the server the alias will redirect to:

5. Go to management studio and type the new instance name in the server name text box to test the connection:

It worked pretty well for me, hopefully it will work for you as well!

Limit Microsoft SQL server memory usage

I had to work with a fairly large database on my PC.
It became very unresponsive so I checked what's going on.
I clicked on the Performance tab in Windows Task Manager and noticed that the RAM was more then 7 GB (too much for a personal computer, don't you think?).

I realized that 4 of those 7 GBs were used by the MSSQL server.
I went to management studio, right clicked the server and choosed options.

Then I clicked on the Memory page and saw that MSSQL Server was allowed to take up to 2048 TB of memory (couldn't even imagine that):

I limited the server to 1 GB and my PC was healthy again (the queries I do may take a bit longer but at least my ALT + TAB key combination is working again :).




Simple thing that improved my experience a lot.