Resource Governor in SQL Server I

        In Production environment DBA's come across the scenarios, When there should be sudden spike in CPU and memory utilization and it lead to slow response to query results.In enterprise  some of running applications are business critical and they shouldn't be disturbed by other applications.Because of this there were problems for DBA about how to allocate specific hardware and resource to the critical applications.

When I involved in project that is about School management there is huge number of data  in the database and several users interact with my database using several applications.There are web users, internal users like staff, students, teachers parents etc. To make this product we have to use low performing machine as a server.Therefor when all users send requests to the same time it response is very low. Some critical functions running on Application in staff and Principle.but all of them become very lazy.

Let know what are the solutions available for DBA

1. Move database to a different high performing server.But it costly 
2. Use SQL Server techniques to takeover this problem

What are the Techniques?(I will produce whole post about this later)

•  Mirroring

                 In mirroring database server maintain two copies of single database, that must reside on different server of SQL Server database engine.Typically these server instances reside on computers on different location.If the principle database server unavailable due to some reason the mirror server takeover the role of principal server and brings its copy of the database online as the principal server. (for more details Click Here)

 

• Log Shipping

                      Log shipping is a one of fail over solution comes with SQL Server 2000.Fail over server substituting primary server with a backup server if primary hardware is unavailable.There is two fail over methods.

         Automatic Fail over
                  Backup server detects when the primary server is not available and take over without any intervention from DBA.

         Manual Fail over
                 In manual fail over when the primary is not available DBA  have to perform some steps to bring standby server online.

                
(for more details Click Here)

• Database Snapshot

           It is a read only static view of a SQL Server database.Snapshot is transactionally consist with the source database as of the snapshot creation. A database snapshot always resides on the same server instances as it source database. As the source database updates the snapshot is updated.

Note :- The longer a database snapshot exists the more likely it is use up available disk space.Because Each snapshot persist until it is explicitly dropped by the Database owner.

(for more details Click Here)

• Replication

            Replication is set of technology for copying and distributing data and objects from one database to another then synchronizing between database to maintain consistency.

(for more details Click Here)

Click Here For find out Resource governor  Demonstration Quires