Location of SQL data files
-
My customer uses a 3rd-party .NET web app with a SQL back-end. There is also a MS Retail Management System (RMS) db and a RightFax db on the same server.
When the server was upgraded to 2005, the vendor placed the db files for their web app in a folder one level above the customary "Data" folder, in
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\
instead of
E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
which is where all the other db's are located.
Any ideas why they would do that? BTW the 2000 installation used D:\SqlData\ for everything, so it's not as if they needed to maintain the same drive & path.
-
Not sure why the customer did that but the default path for data and log files is under a folder named data in the installation path which can be re-configured to other path.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Heh... I'd have to say they just did it out of ignorance or the path is hardcoded in the installation routine (whatever that may have been), Bill. I've seen your other posts... you probably already know how to move the file to where you want it... that's if you want to move it
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff. I was thinking either ignorance or sheer stupidity.
There are four players in this scenario - the customer; the vendor that installed & configured the new Windows server with SQL 2005; the web app vendor; and me. The customer doesn't have any on-staff IT, so I'm the SQL guy among other things. Lots of finger-pointing in that kind of situation.
There were no maintenance plans whatsoever for SQL, several SQL logins were fried, and now I find one db in the wrong place. The customer is trying to determine the "guilty parties" if you will, and decide if they should require them to fix it as part of what was contracted for, or just pay me to fix it.
I'm thinking that the server vendor should have at least created backup jobs for the system db's, and the app vendor should have created backup jobs for their app, plus the system db's once they found there were none.
-
Heh... Maintenance plan? What's that?
Doesn't everyone know that a database is just a place to hold data for the GUI? And, why should the app vendor create backup jobs? Isn't that up to the end user that doesn't know anything about data or servers? 
Nah... sounds like a typical nightmare that so many customers go through... they don't really know anything about what they need, they just know what the want. They hire "experts" to do server setup and buy an app to be setup by those "experts". It's kinda like hanging drywall and plastering... everyone wants in and out as fast as they can (including the customer) and then they wonder why the floor has been ruined. Takes time to lay down that type of "floor protection" and they either didn't know how, didn't want to, or thought it wasn't their job.
Once the customer figures out that they were pretty much screwed by everyone involved, except you, the customer will be smarter... they'll use you more often
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Shhhhh! My ol' friend Serqiy will hear you!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Let's hope not.
Anyway, there is one more player I didn't mention, since they are new on scene - the online store vendor.
The customer is basically a trade association. At their office they have a store where members can purchase related supplies, that's what RMS is for. Local members get the lowest price; if they're a member elsewhere they pay a higher price; the general public pays the highest price.
The online store vendor wanted them to flip the tiers, so the "base price" would be the lowest for the general public; if you sign in as a member then the higher prices would apply. They actually sent over some SQL to do that. Why? Because their app was set up that way. Red flag! I had to get on the phone with the Controller and this vendor to set them straight, and I'm still not convinced they "get it". Imagine walking into Costco and paying a higher price if you're a member. Sheesh.
If all these outside vendors were IT departments within the customer's own organization, people would have (should have) been fired.
Maybe I should have titled this thread "How to do a lousy job and still get paid for it".
-
(If all these outside vendors were IT departments within the customer's own organization, people would have (should have) been fired.)
Actually no because the database was placed in what VS2005 calls AppDataFolder in IIS which is the default location of the ASPNETDB which manages many Microsoft provided built in services in Asp.net 2.0. Developers skilled in data can make decisions to move the databases out of that folder but most employers don't pay for both skills. So it is you get what you pay for because you can run most small web sites with that setup without issues.
Kind regards,
Gift Peddie -
If I read into what you're saying, VS2005 automatically puts the database in a folder that is inconsistent with the customary SS 2005 folder structure. So the root cause is a Microsoft problem? Imagine that.
-
I will not say Microsoft is the cause of the problem because technically it is not a problem it simplifies the development of web application because the AspnetDB can be used to manage users through the Membership service, Profile service and other provider services. So that folder was created to make things easier for web developers who knows declarative languages and expressions but not complex data. If your customer paid for one skilled data person who also knows web you may never have seen it because the location can be changed at the time of deployment.
Kind regards,
Gift Peddie -
From the standpoint of server administration, each instance of SS should have all of their database files in the same folder IMHO.
From the .NET side, how big of an effort would it be to move the database into the "Data" folder? It seems from what you've said, simply doing a "detach-move-reattach" would break the web app, is that correct?
-
No I am not saying that, the AppDataFolder let developers use MDF to create databases and add tables and other objects during development in Visual Studio. During deployment the database can be moved by a skilled person which means it is not an issue but when the only developer doing development is a web developer the person copy the AppCodeFolder and AppDataFolder and deploy which means the databases are deployed in IIS like the code folder. I think it was some thing created by the Visual Studio team in Microsoft.
In big companies with Data team and development teams it is not an issue a developer just write code build the .NET code and pass the data code to VSS or data team because deployment is not the developer's business.
Kind regards,
Gift Peddie -
If you go to the location below in your C drive you will see all the ready to use databases Microsoft Asp.net team created for web developers that they can add to the default ASPNETDB which you can create with an exe based wizard and you can use it with SQL Server 2000 also with minor adjustments.
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
Install SQL Common,
Install Membership,
InstallPersist SQL State,
Install Personalization,
Install Profile,
Install Roles,
InstallSQLState,
InstallSQLState Templates
Install Web Event Provider
Kind regards,
Gift Peddie -
Now I feel like chicken little saying "the sky is falling!".
The server vendor tells me that "we installed and configured the [Symantec] Backup Exec SQL Agent...We have full backups of your server and associated databases going back to the installation date."
Do you have any knowledge of this product? Or has anybody else on the list used this. I looked around Symantec's web site but couldn't find much info besides the normal product hype. Except for mention of some hotfixes.
I wonder if this is as configurable as what is built-in to SQL Server. And where exactly are the backups, on tape? Wouldn't a backup to disk be more reliable?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply