Considering a Hyper-V VPS

[OneApplications]

Hi Scott / Sebastian,

All seems to be working however it looks like you've installed SQL Express as a named instance rather than a default instance. It just means that if it's installed as a default instance I can connect as localhost and also the server will use the default static port instead of a dynamic one.

Any chance you can change this??

[OneApplications]

Actually guys, just leave it as it is for now and I'll have a go at it myself. I have an install config file that will install as a default instance so I can always just uninstall it and re-install using the config file I have.

If it all goes wrong I'll give you a shout!!

[eUKShane]

Hello,

Ohh ! Ok. But no worries, we can definitely change SQL express instance to default.

Feel free to get back to us we would be glad to assist you.

[OneApplications]

Hi Sebastian,

Can you reconfigure it as a default once it's been installed as a named or do you need to just reinstall it? Obvioulsy if it can just be changed "as is" then that might be easier.

Like I say, I have been through this whole named or default instance thing before with SQL Express. I'm sure you're aware of the "bug" in the installer that installs it as a named instance even when you select a default instance etc etc.

I've just removed the standard FTP service and installed 7.5 so that's all up and running now. Just in the process of uploading my custom SQL Express installer / config file so I can try a re-install but if you do know of an easier method to simply change the existing install over to a default instance please let me know...

Cheers!

[eUKShane]

Hello,

Sure, I have done this for SQL express 2005 but never tried for SQL express 2008. I will give a try and will get back to you.

[eUKShane]

Hello,

I have tried changing SQL express instance using following query :

sp_dropserver old_name
GO
sp_addserver new_name, local
GO

But not succeeded, so now trying to change same from the registry. If this will also not work then we will have to reinstall SQL server management studio.

I will keep you updated.

[OneApplications]

lol. Oh the joys of SQL Server!!

I'll wait your reply...

[eUKShane]

Hello,

Apologise for the delay in response.
I have reinstalled SQL server 2008 Express Edition with default instance. You can connect to it remotely using sa user and Administrator password.

Please feel free to initiate a live chat session for further assistance. We would be glad to assist you.

[OneApplications]

Hi,

Now I am confused!

The server still seems to be a named instance (WIN-KEGKZI6IFCU\SQLEXPRESS). Also if you check the startup log you can see that it's listening on port 49193 which is a dynamic port.

The method I'm used to (and tell me if this is completely wrong) is that during the install you need to explicitly set the INSTANCENAME and INSTANCEID as 'MSSQLSERVER'. This will force a "true" default instance. I.e. server name will be MSSQLSERVER and remote port will be TCP 1433.

If you want to have a look at my ConfigurationFile.xml it's on my desktop under FTP Folder/SQLServerInstall directory.

Like I say, I may be talking nonesense here and there could possibly be a reason why you can't install it this way on Server 2008??

[OneApplications]

...and when I say XML file I really mean ini file!

[eUKShane]

Hello,

Rock (Jack L) is working on it and he will get back to you soon.

[OneApplications]

Thanks Sebastian,

Sorry about all this messing about. I feel like i'm requesting something that isn't normally asked for (I'm good at doing that! lol).

[Rock]

Thanks Sebastian,

Sorry about all this messing about. I feel like i'm requesting something that isn't normally asked for (I'm good at doing that! lol).

Hi.. MSSQL Server 2008 Express installed with 'MSSQLSERVER' as InstanceID/InstanceName & listens on port 1433. Feel free to reset the SA password for remote connections, they are all set. Let us know if you need any further assistance.. Good luck & have a nice one..

[OneApplications]

Thanks Rock! All seems to be working as expected now.

So am I right in thinking that installing SQL Server Express as a default instance isn't normally what you do? Only, when I first asked about this when I bought the server a few days ago I was told that it would be installed as default but this quite clearly wasn't the case.

Am I correct in thinking that you weren't aware of the bug in the MS installer whereby you need to explicitly set the INSTANCEID and INSTANCENAME? I'm not saying you should have known about this (Microsoft should just fix the installer!! lol) but if you weren't aware of it then this would explain all the confusion.

[eUKShane]

Hello,

Glad to know that everything is working fine now

SQL Server Express always installs a named instance (SQLExpress) unless you select a default instance. This behavior differs from SQL Server 2005, which installs a default instance unless a named instance is selected.

If you select Default instance and a default instance already exists on the machine, SQL Server Setup will upgrade the default instance. A computer can host only one default instance.

The default instance name is MSSQLSERVER; it does not require a client to specify the name of the instance to make a connection.

[OneApplications]

Hi Shane,

Yes all still up and running fine! Thanks for all your (and the others) help.

The point I was getting at is that there is a known bug in the SQL Server Express 2008 installer where by if you just select the 'Default Instance' radio button it will still install a named SQLEXPRESS instance.

See this KB... FIX: The default instance name is set incorrectly to SQLEXPRESS when you install SQL Server 2008 Express

Having said that, I'd of thought this bug would have been fixed by MS by now??

[compserv]

Having said that, I'd of thought this bug would have been fixed by MS by now??

They have. It's fixed in SP1. However, unless you can slip-stream SP1 into the main installation, I don't see how you can fix it. If I recall correctly, to install a second instance of SQL Server you have to go through the install process again. So assuming you've done the first install and upgraded it to SP1, does the 2nd instance install with or without SP1? If it installs with SP1, you could install a named instance, upgrade to SP1, then install the default instance. And then, can you uninstall the named instance?

Give me Access any day.

[OneApplications]

"Give me Access any day" I can't see many people agreeing with you on that score compserve!! lol.

Yeah, I read about the SP1 fix but like you say, unless they patch the original installer that they offer for download then I can't see how this will be of much use to most people. I downloaded a fresh copy of the "with tools" exe only a few weeks back and this still wasn't able to correctly set a default instance unless you explicitly set the instance name.

I just get my customers to install my own version of the installer. I say "my version" I basically just mean a folder containing the installer exe, a config file that sets the INSTANCENAME & INSTANCEID properties (and turns on TCP support), and also a batch file that starts the installer using my config file.

Works a treat!

That works fine for customers who are running their own on-site server but this Hyper-V server I've just setup with eUK is for multi-site customers that require client terminals at different sites and hence I can now offer them annual database hosting. Fingers crossed it should all run smoothly!!

[compserv]

"Give me Access any day" I can't see many people agreeing with you on that score compserve!! lol.

Thinking purely of simplicity, of course .

Fingers crossed it should all run smoothly!!

Good luck. I hope it all works out for you and your customers.

[OneApplications]

Thanks!

Yeah, don't get me wrong, the software I sell at the moment uses Access as a back-end! It's just once you start using the system with multiple client terminals and concurrent DB transactions that Access can get a bit flakey... hence my migration over to MSSQL.

It's a bit of a learning curve though!