SQL DB Administration

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL DB Administration

12 posts, Page 1 of 2

1 2 »»

SQL DB Administration

Rate Topic

Display Mode

Topic Options

Author

Message

tinausa

Posted Friday, September 21, 2012 2:41 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:22 AM
Points: 42, Visits: 96

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

Post #1362994

Lowell

Posted Friday, September 21, 2012 2:48 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 11,792, Visits: 28,081

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1362999

Suresh B.

Posted Saturday, September 22, 2012 12:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:54 AM
Points: 1,076, Visits: 5,128

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

Post #1363076

tinausa

Posted Saturday, September 22, 2012 5:25 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:22 AM
Points: 42, Visits: 96

Thank you very much. I greatly appreciate it.

Post #1363095

SQLmountain

Posted Saturday, September 22, 2012 8:03 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:29 AM
Points: 436, Visits: 718

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.

Post #1363104

tinausa

Posted Saturday, September 22, 2012 8:47 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:22 AM
Points: 42, Visits: 96

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

Post #1363109

tinausa

Posted Saturday, September 22, 2012 8:49 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:22 AM
Points: 42, Visits: 96

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.

Post #1363110

Suresh B.

Posted Saturday, September 22, 2012 8:58 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:54 AM
Points: 1,076, Visits: 5,128

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.

Post #1363111

GilaMonster

Posted Saturday, September 22, 2012 10:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 38,118, Visits: 30,403

Please read through this - Managing Transaction Logs

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1363123

tinausa

Posted Sunday, September 23, 2012 6:26 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:22 AM
Points: 42, Visits: 96

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.

Post #1363219

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions