Sql-server – How does changing Windows SID affect SQL Server

Ok, can you open your services console and scroll down to S for SQL Server. You should now see the services. Please ensure SQL Server (SQLEXPRESS) is running and then try .\SQLEXPRESS instead of (local).

So as per your example:

Server type: Database Engine
Server name: .\SQLEXPRESS
Authentication: Windows Authentication

Hope this helps

Update: These instructions are because I assume you are running Express Edition not Dev/Std/Ent edition of SQL Server

Try ensuring the appropriate protocols are enabled:

1. Start the SQL Configuration Manager (ie: Start->Programs->SQL Server->Configuration Tools)
2. Expand the SQL native Client configuration
3. Click Client Protocols (you may have a 32-bit and a 64-bit, apply to both)
4. Ensure Shared memory, TCP/IP, Named Pipes are enabled in that order
5. Expand SQL Server Network Configuration
6. Ensure Shared Memory for either SQLEXPRESS and/or MSSQLSERVER is enabled
7. Click SQL Server Services
8. Restart any running services

You should now be able to login to the instance

If you find you cannot login at all you may need to follow these instructions to get SQL Server into single user mode. See here for the full instructions from Microsoft.

By default, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. If a second instance of SQL Server is installed, a second copy of sqlservr.exe is located in a directory such as

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\binn.

You can start one instance of SQL Server by using sqlservr.exe from a different instance, but SQL Server will start the version of the incorrect instance as well, including service packs, which may lead to unexpected results.

To avoid this, use the MS-DOS change directory (cd) command to move to the correct directory before starting sqlservr.exe, as shown in the following example.

cd \Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Binn

To start the default instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter the following command:

sqlservr.exe -m

Single-user mode can be useful for performing emergency maintenance when you do not want other users to connect to SQL Server, but any user can become the single user, including the SQL Server Agent service.

You should now be able to login to the instance and add yourself to the security tab and grant full access.

Alternate Method:

THere is a script here that claims to add the current user to the SQL Server sysadmin role. This may work in single user mode but I have not verified it

SQL Server 2000 and 2005 Workgroup and Standard (32 bit, I'm not sure about 64 bit) will only use a maximum of 2GB memory so having two instances would allow you to use the full 4GB. This would be true even if you're running 32 bit SQL Standard on x64 Windows, more so in fact since you'd want an instance per 2GB of memory.

In principle using a single instance with two databases is faster than two instances with one database each, though I'm unconvinced it's a huge difference. Having separate instances can be useful for management. For example if you use SQL logins, and there are different sets of logins for different databases, using separate instances can make it easier to keep track of logins.

So the answer to your question is "It depends" :-)

JR

Re Spence's comment: SQL Server Standard on 32 bit Windows can use a maximum of 2GB memory. SQL Server Enterprise can use 3GB if you use the /3GB switch. On Windows 2003 Enterprise with AWE up to at least 16GB memory can be used (possibly more) so you can get better value out of your memory by running more than one instance of SQL Server.

I'm afraid the answer is still "it depends". If you have lots of memory, i.e. 8GB or 16GB, then you want to put the biggest databases in separate instances so they can have 2GB (or 3GB with /3GB) each. If you only have 4GB then I'd probably use a single instance and the /3GB switch as the small amount of extra memory used by having two instances wouldn't be worth the overhead.

As others have commented below there can be other considerations. If you reference two databases at the same time, e.g. in a select query or if you insert from one database into another, then you want them in the same instance for speed.