Not so good news for MCM Aspirants

Few days back, late Friday (30th August 2013) MS has made an announcement – that they are cancelling the MCM Certification, not so good news for MCM Aspirants. This announcement has upset many in the SQL Community including me.

I have my own reasons, and belief about not attending any of the certification exam until few month back. In my 14+ years of career not even a single employer of mine have asked me to go and get certified. I can say, I am lucky, but all I know that I have worked hard – a lot. I have tried to learn a lot, from friends, from colleagues, from online web forums, books online, books and MS Evangelists, and still learning. And, that pays me well, I participate in community activities, speaks, blog and have been able to put my thoughts together to publish a book on SQL Server Cluster – this way I am surviving as a professional.

To be honest, during my career I have interviewed a lot many people and many of them are certified but they don’t have exposure and practical troubleshooting skills which was expected; apart from this, there are dumps available that can made it easy to clear an exam. Please, Neither I am not criticizing those who has passed nor I am saying all who haven’t passed aren’t good as professional, but I am just trying to make a point why I haven’t attempted one. With this kind of experience it never interest me to pass the certification.

And, then, MCM was introduced. I have heard a lot about Oracle exams which has lab test as well and MCM is similar kind of exams. Brent Ozar ( Blog | Twitter ) blogged about What is MCM in 2010. I have followed all his posts about MCM and has developed my interest in attending MCM training followed by an exam. Event though the exam and training fees are way too high for me, I have made my mind that I will utilize the money my employer provide each of us for training + some from my pocket to get train and earn this title – but, sadly, it is gone now.  Since MCITP is a pre-requisite for MCM, I have made my mind, attempted and failed. However, I was sure, I was reading more and will clear it in next attempt. But, I am not sure, whether I will be taking an MCITP now.

I will be more than happiest person, if this decision for MCM exam are reversed or some relevant / at par exams are introduced. In our country, it is said that “whatever happened has happened for the good, and whatever that is going to be happen in future will be for good”.  I am hoping that this saying will hold true.

At the end, I would like to say, if you are believer and aspirant of this exam just like me please take some time and vote on the MS Connect site for reconsideration.

Thank you

Hemantgiri S. Goswami (http://www.sql-server-citation.com)

Read More

Roles and Responsibilty of SQL Server DBA

• What is a role of a DBA in an organization?
• What are the daily activities of a DBA?
• What shall I check on regular basis?
• What all processes shall I automate ?
• What are the precautions, I have to take additionally?

These are the common question being asked in-common on may forums, here is what my listing is

• Design and implement Disaster Recovery

• Design, development, upgrade and Migration of Database Server.

• Providing support and handling most critical situations in vast variety of database systems.

• Database Optimization / Query Performance Tuning.

• Design and implementation Automated Database & Application fail over server setup.

• Capacity Planning, Change Management and Admin. Documentation.

• Review of existing design and specifications of the system.

• Design and documentation of operational specifications of the system.

• Monitoring of the development process, in order to confirm optimum performance of the system.

• Preparation of standards and follow up rules for the back – end system, to ensure a fully secured and a robust system.

• Design and implementation of the logical & physical structure of the database.

• Monitoring of performance of database servers and providing tuning measures.

• Performing database / application wide query tuning operations.

Try automate every process that needs to be performed on daily basis and/or requires manual intervention.

--  Hemantgiri S. Goswami (http://www.sql-server-citation.com)

Read More

Resource for new SQL Server DBA and beginners in SQL Server

"Give me some good resource that I can refer as new dba or beginner or new learner " this is the question asked by beginners and new learners in various forums I participate, thus I decide to post a blog referring the same, they are:

Here are some good resources for SQL Server beginners and new DBA(s)
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
http://www.academictutorials.com/sql/
http://www.developer.com/db/article.php/718491

SQL Server Administration and best practice
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
http://vyaskn.tripod.com/sql_server_2000_fast_answers.htm
http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm
http://www.sql-server-performance.com/self_taught_dba.asp

Also suggest to keep on reading and posting in technical websites/ web forums like
http://www.sql-server-performance.com/community/forum
http://forum.sqlknowledge.com/
http://www.sqlserver-qa.net/community/forum/
http://forums.microsoft.com/
http://www.experts-exchange.com/
http://www.sqljunkies.com/

Read More

Download System View Map for MS SQL Server 2005

Download the System View map for SQL Server 2005, this would definately helpfull to DBA(s).
http://download.microsoft.com/download/0/d/f/0dfe488e-a335-4480-8a8a-b405e32f4368/SQL2005_Sys_Views.pdf

Read More

ELSASOFT giving out SQLSPEC for FREE to MVP

Dear All MVP(s),
SQLSPEC is a software that helps DBA(s) to document Database Objects, It is a tool from
Elsasoft LLC; they are giving out SQLSPEC for free to MVP(s) and significant contributors to SQL Server Community, drop a mail to sqlspec@elsasoft.org, with below information
Your full name
Proof of your contribution to the database community
Proof of your Microsoft MVP, Microsoft MCT, or Oracle ACE status if applicable

There's more detail here: http://www.elsasoft.org/mvp.htm
You may also have a look at Software Spotlight at http://www.sql-server-performance.com/software_spotlights/da_sqlspec.asp

Read More

What is a Log Shipping?

High Availability is a hot topic today and need for a business critical applications, as a DBA we always try to cope up with such situation where High Availability and Continuity comes into picture their are technologies that you can use for; Log Shipping is one of them.
Have you ever had a chance to set up Log Shipping? What are the benefits of using Log Shipping over Clustering or Replication? How ever it *depends* upon your environment and requirement at given time. Refer below link for Log Shipping...

Log Shipping Article

Read More

What is Log Shipping?

What is Log Shipping?

Log Shipping is one of the solutions for High Availability for business critical application. Log Shipping is first introduced in SQL Server 7.0. Log Shipping; as name defines does the shipping of Transaction Log to another server; it does exact replica of the database; it does backup of Transaction Log and restores it on a Secondary /Stand by Server which keeps database on secondary server in sync with the database on Primary Server. In case of failure work loss happens only for the time being copy of T-Log backup and restoring back to Secondary/Stand by Server. Stand by / Secondary server which can act as a primary server in case of failure. SQL Server DBA has to perform some manual tasks to bring it online for business.

Why to do Log Shipping?

There are other options like Clustering and Replication for High Availability of Business Critical Application, so why do we go for Log Shipping?

Well, there are reasons for choosing Log Shipping against Cluster and Replications:

1. 
   
   
   Setting up: It is very easy to create a Log Shipping, only thing you need to identify is you must have two servers with SQL Server Enterprise Edition and a database(s) which is critical to your business environment.
2. 
   
   
   Manageability: It is easy to manage Log Shipping comparing to Replication and Cluster Server, generally Log Shipping works well and you don't feel panic with its monitoring or maintenance work.
3. 
   
   
   Act as a Reporting Server: Stand by or Secondary Server can act as a Reporting Server in your organization, your database which is being log shipped can be used for Read Only queries or I would rather say it can act as a Reporting Server. The thing need to be consider here is it will be unavailable at the time of Restoration of Transaction Log e.g. if your Transaction Log backup takes 10 minutes of Restoration Time and you does Log Shipping every 60 minutes then your Reporting/Secondary/Stand by Server will be un-available for 10 minutes.
4. 
   
   
   Can use multiple database(s)/server(s): You can use multiple server(s) as a Stand by or Secondary Server for different purpose, e.g. you can create two Stand by server(s) one for Reporting and another for High Availability
5. 
   
   
   Cheaper then cluster: If you go for a Cluster Server you will have to meet software and Hardware requirement; I mean you need identical hardware to setup clustering where as in case of Log Shipping you don't need to have exactly the same set of hardware for Primary and Secondary or Stand by server.
6. 
   
   
   Best solution for physical dispersed location: Data Center: In your environment you are having a cluster server configured at your Data Center for high availability so that if in case of any failure or damaged you can recover your data back, imagine what if your data center itself got damaged!!!!!! In this type of scenario Log Shipping does good job you can configure Log Shipping to Dr Server which is at different physically location and if your local Data Center / Server got damaged you may still recover your data.
   You can do this thing using Replication too; in case of replication again you have to select Snapshot/Transactional/Snapshot Replication, now as you may use Log Shipping you would rather go for Snapshot Replication, you have to monitoring it regularly whether it is running or not!!! Where as with Log Shipping it generally runs smoothly and even if it caught an error it will tell you exact where it breaks so fixing a Log Shipping is not difficult.

How to do Log Shipping?

Here, I've tried describing Prerequisites and steps for setting up Log Shipping.

Prerequisite

    

1. 
   
   
   Database must be in Full or Bulk-Logged Recovery Model.
   It is required that Database has to be in Full or Bulk-Logged Recovery Model,
   One can perform T-Log backup only when Database Recovery Model is set to Full or Bulk-Logged, here is a brief on recovery model.
   
   

Recovery Model	Benefit	Work Loss	Type of Recovery
Simple	Keeps T-Log in small size, can perform bulk copy operation	If needed you have to restore Full backup or Differential backup	Can done up to the last backup time
Bulk-Logged	Minimal log space occupation, can perform bulk operation	If the log is damaged or bulk operation is done after last T-Log backup	Can done up to the last backup time
Full	No work lost due to damaged data file, Point-In-Time recovery	Normally none, only if you lost your most recent T-Log backup	Point-In-Time

1. 
   
   
   Windows Account used to setup Log Shipping must have SQL Server System Admin rights on both servers.
   It will not break Log Shipping if you the password of Account has changed, because it needs to change at the same time on both the servers else Log Shipping will fails.
   
   
2. 
   
   
   Create a share on Primary Server and Secondary Server with change and write permissions for Windows Account User which is used for SQL Server Agent.
   
   
3. 
   
   
   You may Restore Full Database on Secondary Server manually or it can be done via Log Shipping installation itself at the time of setup process (Ensure this database on Secondary Server must be restored with NORECOVERY or STANDBY option).
   It is required to Restore Database with NORECOVERT | STANDBY because if the database restored in recovered/write enabled mode you can not apply another T-Log.
   
   
   
   
   
   
   
   

Optional Component : Monitoring / Witness Server

Identify your Monitor Server which will look after for your Primary Server and Secondary Server's Activity.

It is better to use separate server which monitors the activity of Primary and Secondary Server so that you can get the alert even if server itself won't works.

Primary Server

1. Create a Share with read/write permission for domain user which we'll be using to startup SQLServerAgent

1. 
   
   
   Create a Full Database backup for
   LogShDB
   
   
2. 
   
   
   Backed up T-Log on the folder you have created earlier to store the log backup
   You can create a maintenance plan to take a T-Log backup for regular interval and then add it as a Step in job to automate this process

1. 
   
   
   Please ensure that the duration must enough to copy the log backup file to Secondary Server.
   Suppose we are taking T-Log backup each 1/2 hour and if the backup file is big enough which takes more then 1/2 to copy over the network share, it will again creates a new T-Log backup which will then occupies recourse unnecessarily.
   
   
2. 
   
   
   Copy T-Log backup file to Secondary Server's shared folder
   
   
3. Delete the T-Log backup file after it copied to the Secondary Server's shared folder.

1. 
   
   
   Ensure you have selected Allow Database to assume primary role option so that if in future requirement of role reversal can be possible easily.
   If in case we need to change the role of primary server or secondary server to act as Primary or Secondary this option should be used while setting up Log Shipping.
   
   
2. 
   
   
   Ensure that you have set up proper threshold values for Destination Database for Out of Sync, Load Time Delay, File Retention Period and History Retention Period option.
   These options are self explanatory: Out of Sync=used by witness server to send alert if the specified time has been exceeded between Last T-Log backup on primary and secondary server, Load Time Delay= Destination Database waits before it restores the T-Log, File Retention Period= Time elapse till file gets deleted and History Retention Period= Time till we retain History details
   
   
   
   
   
   
   
   

Secondary Server

1. Check if the Database is already exists on Secondary Server, if it is already their drop it.

1. Restore a Full Database Backup for LogShDB

1. Restore a T-Log for LogShDB on the secondary server which is copied on the shared folder of Secondary Server;

1. Ensure you have given STANDBY or NORECOVERY option while restoring T-Log backup for LogShDB.

Summary:

You may find log shipping is a very use full yet simple to configure, use and manage, it is cheaper and easily manageable solution for High Availability of your data. It is a good solution over clustering (because it is costly; requires identical hardware to setup cluster) and easily manageable, easy troubleshooting against replication as it clearly says where it breaks. Generally Log Shipping works very smooth once it is configured. Even if you have to re-configure it from the scratch it is easy job to do.

 

Read More