Last weekend I visited New York City (NY) and Edison (NJ) to attend database architecture meeting with a big environmental technology firm. It was very interesting to meet CEO and few of the lead database administrators. Lots of database related things were discussed.
I will list few of the points discussed in the meeting here, due to privacy policy I will be not able to write many of the interesting details I have learned there. Please let me know if you are interested in any of the particular topic. I can elaborate more on the topic which interests everybody.
1) Database Backup
a. Schedule Backup Routines
b. Backup medium and redundancy
c. SQL Server 2005 T-SQL improvement with regards to backup
2) Index Optimization
a. Implementing Primary Key on non-standard columns for improving performance in certain cases
b. Index statistic optimization
3) Maintenance Tasks
a. Index Re-building
b. Database Consistency Check alerts
4) Replication
a. Snapshot replication optimization (use of new copy database feature)
b. Log-shipping failover set up
5) Architecture Designs
a. Setup Replications over database mirroring
b. Database Normalization advantages and performance
c. TempDB optimization
d. Datafile (.mdf) and Logfile (.ldf) filegroup placement
e. Foreign key relations
f. Re-factoring database schemas based on statistics collected from profiler
g. Analysis of hardware (disk, CPU, connections etc)
f. Security of Database
6) System Tables
a. Use of system tables and views to retrieve system related information
It was really interesting to meet all prominent personality of biggest technology firm in one room. I was provided tour of their laboratory and was provided admin privileges to their complete database farm (yes it is really farm, not few computers) for the days, I was in their facility. CEO invited me to their home and I had most delicious home made food in US.
I just received email from CEO that they see performance improvement after implementing few suggestions and their backup is safe on redundant disks. They also restored them with point in the time restoration of logs.
I wonder how many companies have proper backup system (tested with restoring data frequently).
Reference : Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
GooD Luck ..keep rolling
Hello Pinal,
Happy to learn about your attendance for the architectural meeting of a big firm. I would appreciate if you can elaborate on the following topics:
2. Index Optimization
3. Maintenance Tasks
4. Replication
5. Architectural Designs
I’m having little knowledge on the above topis and others mentioned in your article but would like to absorb real life scenarios which you might have observed during your tour.
Looking forward.
Thanks and have a great day!!!
Hi Pianl,
I am working in sql server from last 3 years.i have good knowledge of pl/sql programming.i am looking to have a dba position in future,can you advise me it is mandatory to have mcdba certification.If i go for MCTS then this will help me to achive my target.
I have some doubts reagrding database administration can you send me some of your articals on administration.
Thanks and a great day ahead……….
Best Regards,
Vijay
Can you elaborate more on Backup please and Index Optimation
Hi ,
It would be great pleasure of mine to reply your query,
There are two exams;
1) Exam 70–536
2) Exam 70–528
And for these certifications nothing is mandatory, if you do will be an add on.
And for administration part I would prefer you to first go through with architecture and then move to administrator .The best way is to go through Microsoft books online will definitely inline your need , after then you can refer to more stuff.
For more information about certification follow the link.
https://www.microsoft.com/en-us/learning/default.aspx
Cheers
Praveen Barath
I forgot to mention , It was for you Vijay .
Hi Pianl,
I am working in sql server from last 3+ years.i have good knowledge of pl/sql programming.i am looking to have a dba position in future,can you advise me it is mandatory to have mcdba certification.If i go for MCTS then this will help me to achive my target.
I have some doubts reagrding database administration can you send me some of your articals on administration.
Thanks and a great day ahead……….
Best Regards,
murugan
Hi pinal,
I have two sql 2005 databases in two locations , and i am running merge replication. I want to run some maintainence tasks to improve efficiency of both servers since they are accessed through a .net application. On what server should i run the maintainence tasks like index rebuilding and all? Publisher or subscriber. Please pardon me if you think this is a stupid question.
looking forward for your reply.