SQL Server 2008 R2 Data Location Question

[hennessy1]

I am just messing around with sql server 2008 r2 right now and had a quick question.

If I have the OS and SQL installed on one RAID and I want the database data and backup data on a seperate RAID. Would the following locations be where I change it to the other RAID or would I just leave it at the default location and then just move it later like in the my documents folder and change the location from there?

Would that be where I should change the location of the data and backup locations?

 

[KentState]

Yes, that is where you set the defaults for new database creation and tempdb. You can also select the location of the system databases by doing this. Going this route, the installation will only grant permissions to the SQL Server service account to the locations specified. Just take care of it in advance, and don't worry about moving the system databases.

 

[hennessy1]

Just change the sub directories correct not the root directory?

 

[KentState]

Just change the sub directories correct not the root directory?

I would change all of them.

 

[Emulex]

set them all to separate mount points - that way you can add more storage and map using mount points to optimize

 

[KentState]

set them all to separate mount points - that way you can add more storage and map using mount points to optimize

Not really. You can always add more storage or move databases. This is just setting the initial location of the system databases. It also specifies the default data and log location for user databases. Any time a new database is created, you can set the file group and location if you do not want to use the defaults. SQL Server 2008 and beyond now allows you to specify the location of system databases on install where you had to manually move them in the past.

 

[hennessy1]

So then just change the directory locations for those points don't set separate mount points?

 

[Nothinman]

So then just change the directory locations for those points don't set separate mount points?

There's no point, you get to pick the location of each database and it's logs as you create them.

 

[hennessy1]

What if I am restoreing them?

 

[KentState]

There's no point, you get to pick the location of each database and it's logs as you create them.

There is a point to it. Sometimes as part of application installs, the vendor will create the databases in the default location like WSUS for example. Yes, you can go back and change the default user locations at a later time, but this gives the option in advance.

 

[Nothinman]

There is a point to it. Sometimes as part of application installs, the vendor will create the databases in the default location like WSUS for example. Yes, you can go back and change the default user locations at a later time, but this gives the option in advance.

I meant there's no point to using separate mount points for each location.

 

[Emulex]

depends on your layout and type of database work you are doing. with developer/enterprise edition it can dispatch more i/o workers for every storage subsystem that is attached to each database - obviously you can do this later in the setup but it is kind of nice to have it all well thought out in advance. that way if you decide to keep raid-5 on core database but want tmpdb on ssd and log on raid-0+1 you can move things around rather quickly without having to take the server offline (much) - restriping raid block size can be specific to master/tmp/log/(other databases) - unless you have some sort of mad crazy tiered storage san that does it all for you (3par) but if you are doing it on the cheap you can maximize your storage/performance greatly.

of course you can move databases any time you want - but its just easier to set it up so they are in different directories should you wish to do a quick switcheroo.

 

[hennessy1]

When restoring a database that is already currently in sql server is it better to delete the current database or will right clicking then tasks and then restore database just replace that one anyway?