To connect to your SQL Server 2008 Express edition over network you need to enable the following options:
First: open SQL Configuration Manager (SQLServerManager10.msc):
Double click on ‘Named Pipes’ and enable them.
Then click ‘OK’ on the bottom. Now we have the access set up, now we need to start the service.
Now open Services (services.msc) and double click on ‘SQL Server Browser’:
Set the startup type to ‘Manual’ or ‘Auto’. That’s up to you. I have it on manual for security reasons. Then you can click start. But we have one more step to do. In the window copy the path to the executable (‘C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe’).
Then go to your firewall in your control panel and add the sqlbrowser path to allowed programs.
You’ll eventually see this:
You can also check the public, but that’s not necessary for me. If you set the sqlbrowser service to manual, you’ll need to start it each time you want it (just by making a shortcut to the path we copied above.) If not, it’ll start at startup 🙂
Good luck and post your questions below!
I’m going to try replication between 2 SQL Servers Express in one of these days and I’ll post the results on that too!
Let’s say you used Microsoft Web Platform Installer to install SQL Server 2008 Express Edition you aren’t presented with an option to set your database location. While this is not a problem if you use filebased DBs in your project, this can be a problem when you create the DBs in SQL Server Management Studio.
You can move the database folder to another folder/partition/…
To do this open SQL Server Management Studio. Connect to your instance:
And then do a right mouse click on your SQL Server instance and click properties.
This will open the following window: Click Database Settings on the left.
You can see (on the lower end of the pictures) the ‘Database default locations’. Change these to whatever you want. Be sure to change the log’s location too, you never now when they might come in handy.
While this doesn’t preserve your permissions (if you reinstall the instance,and attach the DBs yourself you will have to take ownership!), this will preserve your data!
A while ago I wrote about me having a 64-bit system with SQL Server 2008 Express edition 64-bit and Visual Studio 2008 SP1 not beeing able to create a service based database because of some wrong registry keys. Installing a 32-bit version of the database should have solved the problem but that didn’t work for me. So I reverted to Sql Server 2005 Express Edition.
Now Microsoft has released a hotfix for this. To get the hotfix you need to create a support case online (99$, which should be refunded) or use an MSDN support case voucher. Or you can use Google as I did and find the direct link to the hotfix 😉
I AM NOT RESPONSIBLE FOR INSTALLING THIS HOTFIX, IT WORKED FOR ME, BUT IT MIGHT NOT FOR YOU. THERE IS A REASON WHY MICROSOFT DOES NOT MAKES THIS HOTFIX PUBLIC. USE WITH CARE.
A while ago I wrote this (Dutch) article on how to perform LINQ to SQL for your SQL Compact databases, since Visual Studio did not understand this, we had to do it manually, as described in the article just mentioned.
But since the release of .NET Framework 3.5 SP1, and the therein included ADO.NET Entity Framework, it is now possible to generate a model out of a SQL Compact database!
Unfortunately this is not (yet) possible for Smart Device applications (Windows Mobile), but who knows what the future might bring!
As described in this connect article there is an issue with the integration of SQL Server 2008 Express Edition ans Visual Studio 2008 if you are running on an x64 system (the OS itself! Not runing 32-bit Vista on a Core 2 Duo) (although some report this bug on a 32-bit OS).
This issue is currently unresolved, but you still can open the database in the server explorer if (and only if) you modify the connection string NOT to use a User Instance.
You can do this by rightclicking the database in the server explorer, modify connection and set ‘User Instance’ to ‘False’ (last item)
User Instances are not allowed anymore since SQL Server 2008, as described here.
In order to make it work as it should be you can either install a 32bit SQL Server 2008 Express (which I do) or do as described here.
Update: Back to SQL Server 2005 Express Edition, all the rest fails as described.