Post reply

SQL DB Administration

  • September 21, 2012 at 2:41 pm

    #262037

    I inherited a small SQL Server DB which then morphed into a larger DB.

    I am not a DBA and have no IT or other help.

    I was wondering if someone can point me to a brief summary of what do I need to make sure the server and DB are healthy and working. I am doing programming, QA, DB administration and all life-cycle steps.

    I am sure that there are a thousand docs on this site but I was hoping to get a pointer to some set of steps to do the administration. I want to make sure that I do not put cart before the horse.

    Thanks a million,

    Tina

  • September 21, 2012 at 2:48 pm

    #1540980

    this is a great reference that gets mentioned in a lot of posts here;

    you can download the PDF for free.

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    another one that i like a lot is Brent Ozar's 60 minute Blitz!, where you are thrown at a SQL server and you wnat to get a handle on it in one hour:

    http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 22, 2012 at 12:30 am

    #1541049

    Following are important steps

    1) Check database integrity

    2) Backup database

    3) Backup Transaction Log

    Additional points:

    1) Backup both user and system databases.

    2) If the data and log files are on same drive, move them to different drives

  • September 22, 2012 at 5:25 am

    #1541062

    Thank you very much. I greatly appreciate it.

  • September 22, 2012 at 8:03 am

    #1541070

    I would like to add that once you have done the above (and those transaction log backups are critical to database health) you should think about getting a test instance going. Restore your production database(s) to the test server and use this server to experiment with administrative techniques before you establish them on the production server.

  • September 22, 2012 at 8:47 am

    #1541074

    Thanks much, the one thing that I have been doing is periodically restore the DB and make sure it works OK.

    I have a bunch of ASP.NETT t code as the UI and I have not tested that UI with the restored DB. So this is an excellent suggestion. I will definitely test that.

    Also note that this whole operation is under IT radar, so I Have no IT support. I back up the DB files almost daily (unless there is no change), and hide copied on other machines and servers. And to top that, my mini SQL Server has two DBs and one of them is about to get rather large.

    Thanks much,

    Tina

  • September 22, 2012 at 8:49 am

    #1541075

    Can you please briefly explain why the log file should be on the different machine?

    As I said to someone else, this is under IT radar, running on Windows 7 64 bit machine.

    Thanks much for your tips.

  • September 22, 2012 at 8:58 am

    #1541076

    No. Not on different machine. On different Drives.

    It is a best practice to place data and log files on different drives for following 2 reasons:

    1) DR (Disaster recovery). If one of the drives fails, you can recover the database without loosing commited data.

    2) Performance. Data file is written randomly. Log file is written sequentially.

  • September 22, 2012 at 10:09 am

    #1541086

    Please read through this - Managing Transaction Logs[/url]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 23, 2012 at 6:26 am

    #1541161

    Thank you I will reconfigure the DBs. I have to upgrade from SQL Express to SQL Standard next week, so that's when I will do it.

  • September 23, 2012 at 6:27 am

    #1541162

    Thank you GilaMonster, this is very informative. Greatly appreciate it.

  • September 23, 2012 at 6:55 am

    #1541164

    Thank you GilaMonster, this is very informative.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply