March 11, 2013 at 10:37 am
Okay, this one has me stumped (or I have missed something about instance installations!)
I have added some more instances on our server at work:MIRROR, STAGE and DEVELOP (these are in addition to the first installed instance SQL_EXPRESS). Each one has a different data folder.
Installations worked fine, however they now all show the same list of databases as SQL_EXPRESS.
I have checked MSDN and have checked the startup parameters (-d for c:\path\master.mdf) of the services for the other instances and they are correct. I know it's checking them as I gave a false path and it raised an error. I also checked the registry settings for each instance.
The only thing I can think is that during installation I specified the same Instance Root Directory - I would test this theory but I can't do a reboot. I would have thought it would have told me if I had misconfigured? Surely each instance has the option listed because each instance can run from a separate master.mdf and therefore data folder?
Thanks in advance 🙂
March 11, 2013 at 12:00 pm
the master database can only be used by one instance. Each instance would have its own master database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2013 at 3:07 pm
They do, they are sitting there right in the DATA folder under each instance path, that's what I don't understand?!
-- Running this on two of the instances:
SELECT physical_name FROM sys.database_files
SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) AS path
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1
-- INSTANCE -> DEV-PC\SQLEXPRESS
--physical_name
C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\master.mdf
C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\mastlog.ldf
--path
C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA
-- INSTANCE -> DEV-PC\ARCHIVE
--physical_name
C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\master.mdf
C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\mastlog.ldf
-- (These should start with C:\SQL_DATA\ARCHIVE\MSSQL11.ARCHIVE\MSSQL\DATA)
--path
C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA
The ARCHIVE service for SQL Server has the following path as a startup parameter "-dC:\SQL_DATA\ARCHIVE\MSSQL11.ARCHIVE\MSSQL\DATA\master.mdf" and if I edit this so it's invalid I get an error in the Event Log, so I know it is reading the property on startup. It just doesn't apply it?
If there's any other queries I can run to help diagnose the issue, just let me know!
March 11, 2013 at 3:12 pm
I have also checked the registry as per http://technet.microsoft.com/en-us/library/ms143547(v=sql.105).aspx
Everything checks out correctly there as well.
March 11, 2013 at 3:24 pm
Just have to rule out the obvious: Are you sure you ran those queries on different instances? What does @@servername say?
March 11, 2013 at 3:35 pm
Doesn't harm to rule out the obvious!
Ran it on both instances both came back with DEV-PC\SQLEXPRESS on both.... even if I right click DEV-PC\ARCHIVE and choose new query... it even shows DEV-PC\ARCHIVEin the bottom bar where it says "Query executed successfully".
It lies!
I'm guessing that @@SERVERNAME gets the value from somewhere like sys.servers, which understandably as it has loaded the wrong master.mdf it's going to give some wrong values. (I'm not as strong on the inner workings of SQL Server yet... I mostly do the T-SQL and index optimisation!)
Definitely ran the query on the right instance though!
March 11, 2013 at 3:41 pm
You don't happen to have an alias for that archive instance do you?
In services control panel, do you have the services for both instances running?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2013 at 3:42 pm
This is a weird one.
Do you see also the users from your SQL_Express instance?
What happens if you stop the SQL_Express instance?
Or what happens if you go to your "supposedly" new instances data directory and attempt to rename the master.mdf file (it'll give you an error if in use).
March 11, 2013 at 4:06 pm
No aliases. Service for both instances are running
Yes, very weird 🙂
Users appear in \ARCHIVE and the same ones again in \SQLEXPRESS (and ARCHIVE should only have sa and the normal system accounts)
Tried renaming the master.mdf that should be running for \ARCHIVE and it's locked by SQL Server. But perhaps SQL Server locks out the whole DATA folder when starting an instance so it's not conclusive that it's locking it because it's using it.
I have also checked the properties of master.mdf in \ARCHIVE and it says it has the wrong file (the one for \SQLEXPRESS) loaded.
:ermm:
Just to be sure about something in my original post - can two different instances can share the Instance Root Directory that you specify during installation? I assumed so that seems to be where the binaries and DLLs go, no settings appear to be kept in there?
March 11, 2013 at 5:03 pm
When you start SQL Server, it is the data file that is locked and not the folder.
When an instance is started, the master db is locked by the process of that instance.
Have you tried stopping the service for either instance and renaming the master.mdf (with obvious intent to rename it right back?
Also, can you get the service startup parameter command for each instance from Configuration Manager?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2013 at 3:11 am
Right I tried renaming both files, helpfully windows tells me the service that has it locked.
master.mdf in the SQLEXPRESS folder is locked by "SQL Server (SQLEXPRESS)"
master.mdf in the ARCHIVE folder is locked by "SQL Server (ARCHIVE)"
When the services are stopped I can rename the respective master.mdf file.
Startup parameters for SQLEXPRESS
-dC:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\master.mdf
-eC:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\Log\ERRORLOG
-lC:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\mastlog.ldf
Startup paramaters for ARCHIVE
-dC:\SQL_DATA\ARCHIVE\MSSQL11.ARCHIVE\MSSQL\DATA\master.mdf
-eC:\SQL_DATA\ARCHIVE\MSSQL11.ARCHIVE\MSSQL\Log\ERRORLOG
-lC:\SQL_DATA\ARCHIVE\MSSQL11.ARCHIVE\MSSQL\DATA\mastlog.ldf
Binary path, Data Dump directory and all other references to instance name are correct in both services.
Thanking you for the help so far, I'm (in a strange way) glad I hadn't missed anything obvious or simple! 😉
March 12, 2013 at 3:27 am
Go to configuration manager, stop the SQL Express instance (leave the others running) and try to connect again to those 3 new instances. Restart SQL Browser too.
Also, go to the properties of each of the instances, check what ports they are running on (before you stop express).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2013 at 3:50 am
Thank you GilaMonster... the force is obviously with you judging by your avatar.
Worked out the fix. Your note to check the ports prompted me to find that.
1) I hadn't turned on TCP/IP for the other instances (forgot that by default it's off).
2) Changed ARCHIVE to 1434 (making it different from SQLEXPRESS, on 1433)
3) Opened up 1434 on the Firewall 😉
3) Restarted and connected, all is correct.
I'm guessing that the TCP/IP traffic was coming in on 1433 it was being redirected to SQLEXPRESS. So it was a transport issue rather than a database engine issue.
Many thanks for the time of everyone above 🙂 Hopefully nobody else has this issue, but if they do the Google Gods smile favourably on this thread. 🙂
March 12, 2013 at 4:00 am
gregrobson (3/12/2013)
Thank you GilaMonster... the force is obviously with you judging by your avatar.Worked out the fix. Your note to check the ports prompted me to find that.
1) I hadn't turned on TCP/IP for the other instances (forgot that by default it's off).
2) Changed ARCHIVE to 1434 (making it different from SQLEXPRESS, on 1433)
3) Opened up 1434 on the Firewall 😉
3) Restarted and connected, all is correct.
I'm guessing that the TCP/IP traffic was coming in on 1433 it was being redirected to SQLEXPRESS. So it was a transport issue rather than a database engine issue.
Exactly what was happening. That was my conclusion, I was just unsure of the cause.
Also worth noting, with SQLExpress on 1433, it's essentially the default instance, you could connect to it without the instance name. I would not suggest 1433 or 1434 for any of your SQL instances, if you need to hardcode the ports, use higher port numbers.
The thing is, two instances cannot use the same database, the first instance locks the file exclusively, if a second instance tried to use the same master.mdf, the startup would fail with this error:
Error 32(failed to retrieve text for this error. Reason: 15100) occurred while opening file 'C:\SQL_DATA\MSSQL11.SQL_EXPRESS\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2013 at 4:11 am
Thanks Gail for the full explanation, so much better to have a forum thread that ends with helpful information 🙂
I will try connecting without a port number in the future... it doesn't matter too much if I do need it - it's only a testing rig for internal use.
Only just gone 10am here in the UK and I have already learnt something new about SQL Server. Every day's an education! 😀
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply