Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL DB Administration Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 2:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:18 AM
Points: 56, Visits: 127
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
Posted Friday, September 21, 2012 2:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 12,963, Visits: 32,512
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
Posted Saturday, September 22, 2012 12:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Saturday, September 22, 2012 5:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:18 AM
Points: 56, Visits: 127
Thank you very much. I greatly appreciate it.
Post #1363095
Posted Saturday, September 22, 2012 8:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:27 PM
Points: 481, Visits: 782
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
Posted Saturday, September 22, 2012 8:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:18 AM
Points: 56, Visits: 127
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
Posted Saturday, September 22, 2012 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:18 AM
Points: 56, Visits: 127
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
Posted Saturday, September 22, 2012 8:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Saturday, September 22, 2012 10:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Sunday, September 23, 2012 6:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:18 AM
Points: 56, Visits: 127
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 »

Add to briefcase 12»»

Permissions Expand / Collapse