Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    21

    Unanswered: Separation of log and data files

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't.

    Do you know WHY you want to separate the files?

    What's your reason?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    21
    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?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not my point...

    Whay are you trying to separate the system database files?

    I can understand for high transaction databases...but not the system database, or even bother for low level trans dbs

    Do you have your sql server client tools installed?

    Have you had a look at books online?

    You need the CREATE DATABASE syntax

    BOL:

    Code:
    CREATE DATABASE database_name 
    [ ON 
        [ < filespec > [ ,...n ] ] 
        [ , < filegroup > [ ,...n ] ] 
    ] 
    [ LOG ON { < filespec > [ ,...n ] } ] 
    [ COLLATE collation_name ]
    [ FOR LOAD | FOR ATTACH ] 
    
    < filespec > ::= 
    
    [ PRIMARY ]
    ( [ NAME = logical_file_name , ] 
        FILENAME = 'os_file_name' 
        [ , SIZE = size ] 
        [ , MAXSIZE = { max_size | UNLIMITED } ] 
        [ , FILEGROWTH = growth_increment ] ) [ ,...n ]
    
    < filegroup > ::= 
    
    FILEGROUP filegroup_name < filespec > [ ,...n ]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Posts
    21
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [whack]
    holy flashback
    [/whack]

    Redo logs...man it's been a while...

    Yes they are VERY nice..no? What wa sit again...you could have up to 3 redo logs...and they thread out...the internals here are VERY simple..

    I don't know if I'd call them a tablespace...since you could create MANY tablespaces per database...in DB2 I make 1 per table

    Think of it this way.

    Each Database has it's own file structure....

    AND it can have many file structures (I guess like tablespaces here) that you can sepearte objects...even within the database file....

    Not to mention a spearate transaction logs.

    All of which could be spread across many different devices...

    BUT...before you do anything...you need to know how intense the database access is going to be.

    SQL server itself does a pretty good job at managing stuff

    I have one DB at 10GB with hunders of users...

    1 mdf, 1 ldf both on the same raid 5 drive....

    Not huge....but not to bad for an oltp db with a java web based front end

    Now when we had to do an enrollement system for peoplesoft for 60,000 users, who all wait to the last moment...we HAD to use Oracle on unix...

    Even Oracle on NT threw up.....

    So I ask, did you get the Client tools installed?

    And you should get a book

    Suggested Reading
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Posts
    21
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I am bonafide....(ever ss brother where art thou? funny)

    Listen...if you're just playing around, there's a lot more for an Oracle engineer to get accostomed to....

    Data and log file distribution are SQL 301

    You only have to be concerned with either the data or volume of transactions get massive....

    Good Luck.

    Got any DB2 OS/390 experience?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •