Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2006
    Posts
    13

    Unanswered: Question about the account SQL runs under

    Hello everyone.
    I am pretty new to SQL Server, but I have been reading a lot lately.
    One of the things that I have gone over extensively lately is backups (I didn't understand it til recently.)

    I have a question: right now, I need to be able to backup my SQL database to a file server; both the DB and TLogs. I don't have room for them on the local drive right now, so this is my last option.

    Here is the catch.
    The SQL server is not part of our domain. The file server box IS part of our domain.

    In order to backup across the network, I need to change the account that SQL and the agent runs under.

    Would this work:

    Create a new account on the SQL box and make it part of the administrators group. Make the SQL server and SQL agent run under that account.

    On the File server, create a local account. Make it the same (username and password) that was created on the SQL box. This should allow me to backup my DB to the share.

    Would that work?

    Lastly, by changing the account SQL runs under, does that change anything in the way that SQL runs? Does it affect the way users authenticate to SQL (Right now, they authenticate using SQL authentication)

    Still new and learning. I appreciate the help

  2. #2
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    I'm not a MS domain hero (to be honest not very fond of it either) but regarding to the last question: I can't imagin that it will make any difference. It's a server setting and that will not be effected by the account it runs under ... asuming that account has enough permissions on the local server to actualy run the SQL Server.

    HTH ... a little.

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  3. #3
    Join Date
    Dec 2006
    Posts
    13
    Quote Originally Posted by Yveau01
    I'm not a MS domain hero (to be honest not very fond of it either) but regarding to the last question: I can't imagin that it will make any difference. It's a server setting and that will not be effected by the account it runs under ... asuming that account has enough permissions on the local server to actualy run the SQL Server.

    HTH ... a little.

    Gr,
    Yveau
    Appreciate the follow up. I will give it a go and post my results.

    Thanks.

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Domains aren't my domain either but changing the account under which a SQL Server instance runs does not make a difference for your users.

    A word of caution though: be carefull about creating a potential security leak. Any user with rights to execute extended stored procedures will be able to do whatever the account under which the SQL instance runs has authorization to (e.g. by using xp_cmdshell).

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by thecoffeeguy
    I have a question: right now, I need to be able to backup my SQL database to a file server; both the DB and TLogs. I don't have room for them on the local drive right now, so this is my last option.
    I advise against doing this. Backups run much slower to a network than to a local drive, and if your network connection fails your backups fail.
    Drive space is cheap. Get a spare drive for backing up your databases, and then copy the data to a network for offsite storage.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by blindman
    Backups run much slower to a network than to a local drive.
    Sorry to disagree but we're using a SAN for our data and log files (the logical SAN-disks are mounted as drives on the server) and NAS for our backups. This is actually four times as fast as using the local disks of the server.

    Ofcourse we are relying on the high availability of our network and problems do occur sometimes but overall we're very satisfied.

    Grtz, Lex

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Lexiflex
    Sorry to disagree but we're using a SAN for our data and log files (the logical SAN-disks are mounted as drives on the server) and NAS for our backups. This is actually four times as fast as using the local disks of the server.

    Ofcourse we are relying on the high availability of our network and problems do occur sometimes but overall we're very satisfied.

    Grtz, Lex
    Hold on. You are saying that a backup is 4x faster..but you are asking how to do it? Sounds like you haven't done a backup across a network (bad idea in the first place) so how can you say that?

    I don't get it.
    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.

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by Brett Kaiser
    Hold on. ... I don't get it.
    That's because I'm not the one asking the question. thecoffeeguy is!

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Lexiflex
    Sorry to disagree but we're using a SAN for our data and log files (the logical SAN-disks are mounted as drives on the server) and NAS for our backups. This is actually four times as fast as using the local disks of the server.

    Ofcourse we are relying on the high availability of our network and problems do occur sometimes but overall we're very satisfied.

    Grtz, Lex
    Its four times as fast for you to send data over a network to be backed up on a drive than to back it up directly to a drive? Sounds to me like this is a matter of the hardware choices you have made.
    Its all a matter of preference of course, but I keep five days worth of backups on my local drive, and the copies on the network drive are archived to tape. Redundancy, redundancy, redundancy. My backups succeed whether or not the network is available, and perhaps more important in case of a disaster, I can RESTORE a database without relying on the network drives.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by blindman
    Its four times as fast for you to send data over a network to be backed up on a drive than to back it up directly to a drive? Sounds to me like this is a matter of the hardware choices you have made.
    Well, the SAN consists of dozens of physical disks and the data is sort of striped on all of them. The parallel writing gives us the extra speed (and the 1Gb network helps).

    Quote Originally Posted by blindman
    Its all a matter of preference of course, but I keep five days worth of backups on my local drive, and the copies on the network drive are archived to tape. Redundancy, redundancy, redundancy.
    From what I've heard redundancy is no problem. The SAN mirrors itself on the same location. Both the original and mirror are replicated to a location a few klicks away. And ofcourse multiple tape backups are made. It is calculated that 1GB has footprint of 11GB!

    Quote Originally Posted by blindman
    My backups succeed whether or not the network is available, and perhaps more important in case of a disaster, I can RESTORE a database without relying on the network drives.
    Yep, that's a drawback but the philosophy is that when the network isn't working, the databases are of no direct use.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes but don't you find that there is inherent risk that there might be a "hiccup" in the network that would cause the dump to be corrupted?

    Do you restore every back up to test it?
    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.

  12. #12
    Join Date
    Dec 2006
    Posts
    13
    Quote Originally Posted by blindman
    but I keep five days worth of backups on my local drive, and the copies on the network drive are archived to tape. Redundancy, redundancy, redundancy. My backups succeed whether or not the network is available, and perhaps more important in case of a disaster, I can RESTORE a database without relying on the network drives.
    Ultimately, that is something I would very much like to do. Maybe not 5 days, but 2 would be nice.

    One of the predicaments I am in is the available space on the local disk.

    I have two SQL boxes; one is production and one is a test box.

    On the production box, I have close to about 80gigs free of space. However, when I look at the actually files for our single DB that is used (besides master, tempdb etc.), the DB size is around 19gigs and the TLOG's are around 37gigs.

    This project was just turned over to me. I have literally been reading all day about SQL and how to properly maintain it. One of the horrors that I found out was the originally contractors setup this DB (it was part of a software package we bought...long story) is all the things they setup WRONG and how improperly how backup was setup. It literally kept me up at night.

    What i found out was they were doing full backups of the DB, but we have NEVER backed up the tlogs. I have been scrambling since then, doing my best to make sure I can get our data backed up to a tape so I can have SOME backup.

    I've been looking for options and been asking for help.

    Now, with my situation, what would be some recommendations that others would do?

    I am extremely thorough (have to be in this line of work) and I continually research everything to make sure I understand it. I feel more comfortable doing something if I understand it, than if I don't.

    here is what I was thinking, and please chime in with suggestions.

    1.) Perform a FULL backup of our Database right away.
    2.) Perform a backup of the TLogs after the DB backup is finished.
    3.) Push these two to a tape device
    4.) Work on a method that will backup my tlogs every 30 minutes, then sweep these too tapes.

    I am toying with the idea of getting a SAN to hold this data, but that might be out of my budget right now.

    I appreciate the help.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Backup your tlogs right away. That is why they are so large.
    Once you dump those you should have room for at least two days worth of data on your drive.

    A good standard is to backup your database nightly, and backup your logs hourly during business hours. If your data is less volatile you can get away with weekly backups, or go the differential backup route, but since backups are pretty fast I've never seen any problems with performing them nightly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by thecoffeeguy
    I am toying with the idea of getting a SAN to hold this data, but that might be out of my budget right now.
    What is your company's budget for disaster recovery, in the event that all of the data in your databases is irretrievably lost? I'm guessing that would cost more than a spare 500gb....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2006
    Posts
    13
    Quote Originally Posted by blindman
    Backup your tlogs right away. That is why they are so large.
    Once you dump those you should have room for at least two days worth of data on your drive.

    A good standard is to backup your database nightly, and backup your logs hourly during business hours. If your data is less volatile you can get away with weekly backups, or go the differential backup route, but since backups are pretty fast I've never seen any problems with performing them nightly.
    Should I backup the DB first, then followed by the TLOGS? There has been some backing up of the DB in the past (to what, no clue)...I think I read somwhere that you want to make sure you backup your TLOGs after you backup the DB....is that right?

    Thank you for your help.

Posting Permissions

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