Here's how I'd do it:
- Schedule some time where the database can be down for the amount of time to move the files
- Execute a series of '
alter database [foo] modify file (name = 'foo_1', filename = 'new location here'
) statements
alter database [foo] set offline
(you'll have to kill any active spids in the db or wait for them to finish their business)
- move your files
alter database [foo] set online
If you want to be really clean about this, you can stop the distribution agent while the database is offline and restart it when it's back online. But that's not necessary. Just so you know, I've also done this with a distribution database and would imagine that it would work fine with a publisher, too.
But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?
The original reason for splitting log and data files off onto seperate disks was 2 fold - latency and bandwidth on the drives.
SSDs don't remove these restrictions, but they do decrease/increase the limits quite significantly (7.9ms for a read with a single HDD vs 0.1ms for a read in a single SSD, roughly).
So ultimately yes and no - it doesn't apply AS MUCH as with HDDs, but those limits are still there and can still be met. It all depends on your workload.
Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?
Assuming that
- You have 3 physical SSD's
- You have 1 physical HDD
- You need the data to be redundant, but not necessarily the system itself
Your proposed setup would have a few issues (as mentioned before), and a single drive failing is the main one.
You could go for something like this.
Single 7200rpm drive - Windows OS
RAID 5 array (3 SSDs) - Broken down into 4 drives (D for Data, L for Logs, S for Swap and T for Temp)
OR
Single 7200rpm drive - Windows OS
Single SSD - Temp and Swap
RAID 1 array (2 SSDs) - Data and Logs
It's personal preference of mine offloading Windows onto a non-SSD drive when you only have a limited number, but this entirely depends on what the server is doing and how much of a risk you're willing to take.
Best Answer
Oh Boy! System Databases on C Drive are recipe for disaster !
Change the default database location away from C:\ drive.
You can do it using
ALTER DATABASE .. MODIFY FILE
Change the db file location
Stop log reader agent, distribution and merge agent (if they are running).
Offline the database using
alter database db_name set offline
Once offline, you have to PHYSICALLY move the data and log files to the new location that you put in in step 1.
Bring the database ONLINE
alter database db_name set ONLINE
Start the jobs that were disabled in step 2.