How to Get Multiple Simultaneous Connections to MS SQL Server Working

0

You may not believe it, but I have usually had some kind of problem with SQL Server. It always cost me at least an hour of googling, and sometimes I even had to give something up just to end up with something less than perfect. The last time this happened was less than a week ago, when I installed a fresh Windows 8 and suddenly could not access SQL Server through the SQL Server Express 2012 instance. It was giving me a real headache until I solved it by using LocalDB.

That worked right away, but only until I started connecting to the DB with multiple connections – up to 3 at once (in VS Express for Web (etc.), localhost (full IIS) and localhost (IIS Express)). Only the first connection created worked; all the others reported that the DB file was already in use.

And since I had this W8 on a partition of only 25.4GB and had already used up all the free space on it, I had to back everything up and create new partitions, this time 120GB for W7/W8.1 and 80GB for W8. Hopefully this will be enough for me now. And now for the procedure I chose:

  1. The first developer program I installed on W8 was SQL Server 2012 Express with Advanced Services x64. I left everything at the default values, including the instance name, so it stayed MSSQLSERVER. The only thing I changed was that I can log in to SQL Server both with Windows Authentication and with a login/password. So I entered my password for sa there.
  2. Next I installed all the developer IDEs I use, namely VS Express 2012 for Web, VS Express 2012 for Windows 8 and VS Express 2012 for Windows Desktop.
  3. This was followed by the installation of Visual Studio 2012 Update 2.
  4. Then I ran SQL Server Management Studio under the Administrator account in Windows and attached the DB located in the folder D:\MSSQLSERVER under the name sunamo.cz.mdf. Because SQL Server Management Studio added it under its full path, including the .mdf at the end, I changed the name to “sunamo.cz”.
  5. In the application I wrote this Connection String: Data Source=N;Database=sunamo.cz;Integrated Security=True; where “N” is the name of your PC and “sunamo.cz” is the name of your DB.
  6. Next I installed full IIS through the Control Panel, including the components for ASP.NET 4.5.
  7. I tested both a web and a desktop application with this setup and everything works!
Categories

Leave a Reply