I'm kind of new to sql server (but experienced in Oracle) and I've got a couple of questions I wanted to bounce off you guys.
I'm implementing a SQL server cluster right now (2 node on Win2K3, shared EMC DASD for databases). We're at the very preliminary phase of this. I did an install and had my resource group set up with all of my disks on it. When prompted for the data file drive, I gave it one, but it put all the tlogs for the 'out of the box' database on that same drive as the data files (i.e. master, model, tempdb, etc.). The doc is a little vague in some of these areas (i.e. it says separate logs and data files on different disks, but then never actually tells you how to do that).
Now, I know how to specify the default paths for data and transaction logs for any NEW database I create and that's not a problem. However, my question is, how do I 'move' the tlogs from the databases created during the install? I've tried a detach, move tlog to separate physical drive and then reattach the db, but whenever I do this, SQL server wants to create a new tlog for the db on the same old drive as the datafile. I also can't delete the original tlog from a particular database even after I've created an additional tlog on another disk.
Any help is much appreciated. I'm more or less looking for the strategy any of you might take to set up this initial phase.
There are several good reasons to do this with any DBMS. First and foremost being balancing of I/O. You don't want your logs to be in the same place as all of your data. While the database is trying to write out transactions to the log, your users may be trying to query data from the same device. In many situations this can be managed, but why set yourself up for potential problems later on. Secondly is recoverablility. Having both logs and data on the same device means you'll lose everything if that device fails or becomes corrupted. If you lost the data and still have logs, you can use the logs to roll forward from the last good backup. If you lost the logs and still have data, well you're fine once you can start writing logs again (but you better take a good backup immediately). If you lose both at the same time, well then it's back to tape for everything and you've lost any work from the time of the last backup. Microsoft (and many other vendors) recommend making this separation, so I intend to try where appropriate, but I still don't have an answer as to how. Does anyone else out there use separate devices for these files?
Okay Brett, I'm beginning to see where I'm lacking a bit. In Oracle, the 'redo logs' (or archived redo logs, if you will) are responsible for transactions over the entire instance (system and non-system essentially). SQL server likens it's databases to what Oracle calls 'tablespaces'. So when you separate the logs from the data files in Oracle-land, you separate ALL of the logs. Are you saying I don't need to do that for the system database (e.g. master, model, tempdb, etc.)? I'm not trying to talk one system or the other up or anything, just trying to understand the architecture. Would it then be sufficient that as I create user database for various applications to put their logs and data files on separate devices?
Thanks a ton for your help so far. This is exactly the kind of feedback I was looking for.
Yep, client tools are installed and I have several books, mostly training manuals from the Microsoft classes that we were given (although they were nearly a year ago). We've has SQL server in-house for about that same length of time, but haven't done much with it. I know the basics, just that this particular issue never really came up because previous installs were on very small machines with only locally attached storage and no real uptime requirements to speak of, hence we didn't give a lot of priority to load, recoverability, fault tolerance, etc.
I actually asked a Sybase dba here about this issue since the structure of the systems are virtually the same. He confirmed pretty much what you've said to this point Brett, so thanks again for the help.