Audit sensitive data to see who did what, when, where, and how ?

We need to have information about whats going on our SQL server and

  • What is being changed ?
  • When it was changed ?
  • Where its changed ?
  • How its changed ?

These all questions need to be explored to understand the auditing of SQL Server. As we know the financial data is critical for the regulatory requirements and it has to be audit with proper reports of a financial report of the company.

We were not aware about the standard used for audit purpose . But some of them are as follows :

  1. PCI DSS [ Payment Card Industry Data Security Standard ] 
  • Its designed to ensure All companies ensures security standard  that accepts, , process, store or transmit credit card information maintain a secure environment. 


2. HIPAA  [  Health Insurance Portability and Accountability Act ] 

  • The Health Insurance Portability and Accountability Act of 1996 (HIPAA) required the Secretary of the U.S. Department of Health and Human Services (HHS) to develop regulations protecting the privacy and security of certain health information.1 To fulfill this requirement, HHS published what are commonly known as the HIPAA Privacy Rule and the HIPAA Security Rule. The Privacy Rule, or Standards for Privacy of Individually Identifiable Health Information, establishes national standards for the protection of certain health information. The Security Standards for the Protection of Electronic Protected Health Information (the Security Rule) establish a national set of security standards for protecting certain health information that is held or transferred in electronic form. The Security Rule operationalizes the protections contained in the Privacy Rule by addressing the technical and non-technical safeguards that organizations called “covered entities” must put in place to secure individuals’ “electronic protected health information” (e-PHI). Within HHS, the Office for Civil Rights (OCR) has responsibility for enforcing the Privacy and Security Rules with voluntary compliance activities and civil money penalties.

3. FERPA [Family Educational Rights and Privacy Act ] 

  • The Family Educational Rights and Privacy Act (FERPA) (20 U.S.C. § 1232g; 34 CFR Part 99) is a Federal law that protects the privacy of student education records. The law applies to all schools that receive funds under an applicable program of the U.S. Department of Education.

4. SOX  [ Sarbanes-Oxley (SOX) Compliance ] 

  • The Sarbanes-Oxley Act (SOX) requires that all publicly held companies must establish internal controls and procedures for financial reporting to reduce the possibility of corporate fraud. Your entire IT infrastructure–from server and network security to IT practices and operations–must be reinforced and configured to maintain and demonstrate compliance in the event of an audit.


Now what needs to be audited for SOX requirement in SQL server and what not we need to learn more about it. We will keep posting on it in future post.

Aside | Posted on by | Leave a comment

Using the SQL Server Profiler

These SQL Server Profiler event classes can be used to help you find out if SQL Server is running as efficiently as it can. They include:

  • Execution Warnings: For very busy servers, it is possible that a query may have to wait a short time before there are available resources for it to run. This event class can be watched to find out if and how long queries have to wait for needed resources. This event class has two possible values. “Query Wait” is used to indicate how often a query has to wait. “Query Time-Out” is used to indicate how often a query has timed out waiting for the necessary resources. Obviously, you don’t want to see either of these events. If you do, and they occur regularly, then you need to reduce the load on your server, beef up the server hardware, rewrite the queries, select more appropriate indexes to resolve the problem, or some combination of all these actions.
  • Hash Warning: This event is used to measure hash recursions or hash bails that have occurred on your server. A hash recursion (event 0) happens when the input of the query does not fit entirely into memory, forcing SQL Server to split the input into multiple parts, which are then processed individually. A hash bail(event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion depth, which forces the query to run under an alternative query plan, one that is much less than optimal. Think of a hash bail as a hash recursion’s worst nightmare. As you can imagine, these two events can really slow down a query. Some options to correct these problem events include: insure that the Index Statistics are up-to-date, rewrite the query, experiment with optimizer hints, or add more RAM to the server.While the purpose of this warning is to let you know when hash operations go awry, it also serves another purpose. If you want optimum performance, you want to avoid hash operations of any type, even ones that work properly. So if you identify any hash warnings, you might want to try to find out why a hash operation is being performed in the first place, and try to fix this problem. This of course, will also fix your hash warning problem.
  • Missing Column Statistics: Tells you which columns in your query are missing statistics, such as the Index Statistics used by the Query Optimizer to help it evaluate the most efficient query plan. If one or more columns of statistics are missing, the Query Optimizer may not select the best query plan, hurting performance. To help prevent this problem, consider the following: Be sure you have “auto create statistics” turned on for your database, use the CREATE STATISTICS statement to manually create the statistics on the missing columns, or use the Index Tuning Wizard or Database Engine Tuning Advisor to identify and automatically create the needed statistics.
  • Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. To fix this, add a join predicate.
  • Sort Warnings: Tells you if sort operations can be done completely in memory or if the sorting operation has to be divided into two or more steps in order to complete. Obviously, a one step operation will be much faster. Potential solutions to this problem include reducing the number of rows to be returned and sorted, reducing the number of columns being sorted, getting rid of the sort, or adding more RAM to the server.

If you haven’t done so already, trace these events on one of your busier databases and see what’s going on.

Aside | Posted on by | Leave a comment

Windows Administrator Skills

  • Windows OS & AD Incident/Production Management
  • Network and VPN trouble shooting activities
  • Printer troubleshooting and management
  • Perform customer catalog and windows updates periodically
  • Upgrade software on a variety of hosted products
  • Installing, Configuring and Hardening of Windows 2008/2012 R2 Servers
  • Installing, Configuring and Managing AD / DNS Services
  • AD Replication monitoring and regular health checks to be performed
  • Creating Group Policies and implementation as per standard procedures
  • Coordinating with different lines of business with SLA
  • Controlled shutdown and restart of services and dependencies
  • Console operations – verify system availability
  • Working with VMware and Hyper-V environments through Console and RDP access
  • Daily Operating System alert handling and response to include backups
Aside | Posted on by | Leave a comment

Task for MSSQL Server DBA


  • Check the previous night’s SQL Server database and transaction log backups and SQL Server Agent jobs for errors.
  • Check all databases to make sure all are up and not marked as suspect.Check previous night’s DBCC CHECKDB for errors.
  • Check SQL Server and Windows application and system event log entries for warnings and errors and determine if any entries warrant further investigation.
  • Check mirroring status for all databases being mirrored.Check for service status for all Windows services that are required for operation (i.e. Windows Full-Text Indexing, Search Server, etc.)
  • Look for any security policy violations.Look for resources on the server, such as file sizes and disk space, and audit growth for long-term projections.Check system performance levels against established baselines.
  • Use long-running queries or tasks, Perfmon, etc. to generate the data.Double check that no configuration changes have been made on the server, and if so, document and investigate.
  • Ensure that all data replication tasks are operating normally.Record and compare last day of CPU activity with known baseline.


  • Check for full system backup status.
  • Verify that the MSDB database is being backed up.
  • Verify that index and statistic maintenance has completed.
  • Perform sample restores of database backups on preproduction servers to spot-check backup set integrity.
  • Examine SQL Server wait statistics to see if new ‘pain points’ have become large points of contention within SQL Server.
  • Audit job execution time for dramatic variances against baseline runtimes.
  • Investigate databases for objects that violate established rules.
  • Verify that SQL Server is sending database mail properly.


  • Compare SQL Server run book specifications against current configuration. Update run book if necessary.
  • Perform system maintenance, such as disk defragmentation, Windows updates, SQL Server cumulative updates
  • Verify that data access speed is running at normal levels.
  • Perform full system growth projection update as part of normal capacity management.


  • Perform a full index analysis for all databases. Determine unused indexes and disable if appropriate. Determine missing indexes and investigate process to update or add indexes to better cover data usage.
  • Review systems for tuning opportunities. Common tuning indicators include buffer cache hit ratio, page life expectancy, I/O and network performance, long running queries, average CPU utilization, etc. Perform tuning as necessary.
  • Perform full test of disaster recovery plan.
  • Perform full system restore against a virtual machine so that all core systems and services can be brought up in isolation and tested for integrity.
Aside | Posted on by | Leave a comment

Day-to-Day Tasks of the Average DBA

DBAs (Database Administrators) perform many different tasks, and one way to explain what a DBA is, is to describe the kinds of tasks they perform on a regular basis. The following lists some of the most common tasks performed by the average DBA, from A to Z.


An emerging task of the DBA is to identify which users are accessing, inserting, updating, or deleting data, and when. Auditing might only be necessary for limited time periods, for specific users, for very specific data, or it might be required 24/7 for all data. While performing this task, DBAs often have to work with both internal and external auditors.

Application Integration

While most organizations use third-party applications, very few of these applications work in isolation. In other words, disparate applications have to be made to talk to one another, often using the database as the means to share data. DBAs often get involved in figuring out the best way to integrate applications. This may include creating custom applications, Transact-SQL scripts, or SSIS packages.

Backup and Recovery

One of the most fundamental aspects of the DBA’s job is to protect the organization’s data. This includes making periodic backups of data and keeping it safe from accidental or intentional destruction. In addition, a well-developed recovery plan needs to be implemented and tested so that when problems do arise, data can be restored quickly.

Business Intelligence/Data Warehousing

One of the fastest growing areas for the DBA is Business Intelligence (BI) and data warehousing. This is because more and more organizations are seeking to mine all the information they can, in order to make better business decisions.

Capacity Planning

In most organizations, the number and size of databases grows rapidly. It is the responsibility of the DBA to watch data growth, and plan how best to deal with it. This may include archiving it, increasing the size of current hardware, or adding new hardware.

Change Management

SQL Server configurations, database schema, Transact-SQL code, and many other facets of the application ecosystem, change over time. It is often the responsibility to the DBA to perform impact analysis before changes are made, implement changes, test changes, and document them.

Database Application Development

Many DBAs are really application developers who specialize in writing code to directly access SQL Server. While this is most commonly done using Transact-SQL and stored procedures, it can involve writing other code that is used to access SQL Server data.

Data Modeling and Database Design

The foundation of all efficient and scalable databases is good database design. DBAs often create database designs by performing needs / requirement analysis, creating a logical mode, and them implementing the physical model.

Developing and Maintaining Best Practices

DBAs should be proactive in their work, and one of the best ways to be proactive is to develop sound database best practices and to implement them. The better organized and managed the database operations, the more efficient they will be. Ideally, an organization’s best practices will be documented for all to read and follow.

High Availability

A DBA needs to ensure that their databases are available to users when they need access to data. There are many different ways to help ensure high availability, including use of log shipping, clustering, database mirroring, and other technologies.

Installing, Configuring, Patching and Upgrading SQL Server Software

One of the most time-consuming of all database tasks is installing, configuring, patching and upgrading SQL Server instances. While installing and configuring new instances is relatively straight-forward, it can be time consuming.

Installing and Configuring Hardware

In some organizations, hardware (the server and I/O subsystem) is handled by dedicated hardware technicians. In others, the DBA is responsible for building, installing, and configuring their own hardware. In addition, DBAs may also perform regular hardware troubleshooting and maintenance.

Load Balancing

Over time, the load put on individual databases changes. DBAs are responsible for monitoring workloads and figuring out how to maximize hardware resources to get the best SQL Server performance. This may involve moving a database from a busy server to a less busy server. It can also involve server consolidation or virtualization.

Maintaining Documentation

Writing and maintaining documentation is probably the most boring and loathed task that a DBA will encounter. However boring it is, it is still a critical part of the DBA’s job. If there is no documentation, then there is no easy way to rebuild the current infrastructure should major problems arise.

Managing Managers

A manager needs to be a DBA’s ally, not an enemy. It is important for a DBA to develop and maintain good relationships with their manager, and with any other managers in the organization that they work with. Getting along with managers makes it easier for DBAs to get the resources they need to succeed at their job. The same is true for getting along with other managers, as many of them may control resources that DBA’s need in order to perform their tasks successfully.

Managing People

Many DBAs find themselves in management positions, such as a senior DBA who is in charge of junior DBAs. Some DBAs at large organizations do this full time, while others combine people management with other DBA duties.

Managing SQL Server-based Applications

DBAs are often responsible not only for managing SQL Server and its databases, but also any applications that access the database. These varies by organization, but in some places the DBA ends up spending more time managing applications than SQL Server itself.

Maintaining Servers and Databases

SQL Server, and the databases running on them, need periodic maintenance in order to run efficiently. DBAs are responsible for ensuring all maintenance gets done on a timely basis.

Managing Test Environments

In most, larger organizations, DBAs manage test environments that include test SQL Servers and databases, as well as test database applications. The purpose of this is to allow databases and applications (both in-house and third-party) to be tested before new versions of SQL Server (including patches and service packs), operating systems, or applications are rolled out into production.


This is a wide-ranging task that includes many subtasks, such as monitoring performance, monitoring server disk space, monitoring logs, ensuring jobs have run successfully, checking for errors, and so on.

Needs/Requirements Analysis

Whether a DBA is involved in development, or just supports third-party applications, they often perform needs/requirements analysis. This can include talking to users, finding out their needs and requirements, and determining the best way to meet them.

Negotiating Service Level Agreements

In many organizations, DBAs become involved in negotiating Service Level Agreements (SLAs). A SLA is an agreement between the customer (the owner of the business application accessing SQL Server databases) and the service provider (the DBA team managing the databases). This agreement sets out the criteria that define “acceptable service”.

Performance Tuning

It is the job of the DBA to monitor performance and to determine ways to optimize database performance.

Project Management

Oftentimes, DBAs will find themselves in charge of a large project involving many other people. This could entail writing a new in-house application, or managing the migration of a data center from one location to another.

Protector of the Data

While this is not a specific job task, I am including it here because it underlies so many of the DBA’s other tasks. DBAs are responsible for protecting the integrity of an organization’s data. This not only involves such obvious areas as backup, restores, and high availability, it also includes ensuring that applications don’t corrupt data, that hardware doesn’t corrupt data, or that user’s don’t corrupt data.

Replicating Data

It is very common for data to be moved from one server to another on a regular basis. A DBA will often research various ways in which data can be replicated from server to server, decide upon the most appropriate method, implement the replication, and then manage it once it is up and running.

Report Writing

With SQL Server Reporting Services, many DBAs find themselves writing reports against databases. This might just mean writing the Transact-SQL code to extract the data, or it could include the creation and formatting of physical reports.

Running Jobs

Virtually every SQL Server has jobs that run on it periodically. These jobs might include backups, data imports or exports, or rebuilding indexes. DBAs are responsible for determining what jobs are needed, creating the jobs, and managing them.


DBAs control who can access data and what they can do with it. This involves creating SQL Server login IDs, database IDs, assigning permissions, moving security between servers, and maybe even implementing data encryption.


DBAs often write their own Transact-SQL scripts to perform a wide range of tasks, including monitoring and maintenance tasks. In addition, with the advent of PowerShell, many DBAs are writing PowerShell scripts to enhance their productivity.


A very common task is to move data in and out of databases and at the same time perform some transformations on the data as it is moved. This is often done for BI applications, data warehouses, and application integration. SQL Server Integration Services (SSIS) is a popular tool DBAs use to implement Export/Transform/Load (ETL) operations in SQL Server.


DBAS perform all sorts of testing, all the time. This can include testing servers, testing databases, testing applications, testing management tools, and so on. DBAs test because they want to ensure that what they do will work, and that data integrity and high availability, is maintained at all times.

Training Users

Oftentimes, DBAs need to share their knowledge with other DBAs, developers, or end-users.


Virtually every day, DBAs are troubleshooting one problem or another. In many cases, when a problem occurs the DBA is expected to “drop everything” and focus on resolving the problem at hand.

Vendor Relations

Many SQL Server-based applications are provided by third-parties, so the DBA often becomes involved with maintaining relationships with these third-party vendors. The DBA will generally perform the initial installation of the third-party software, troubleshoot problems, and update the application and its database when new versions are rolled out.

Working with Teammates

Rarely will a DBA work alone. In most cases, DBAs will be interacting with a very large group of people, including other DBAs, developers, end users, product-knowledge specialists, vendors, accountants, hardware experts, and networking experts.

While this may seem like a long list, it is only the tip of the iceberg when it comes to describing the many tasks of the Database Administrator. If you are a DBA and would like to add to this list, please do so in the comments section below.

Aside | Posted on by | Leave a comment

Basic Application Support Skills

As  i have been involved in application support work in current company so i tried to find out basic skill set that i need to have to work in this domain. As i understood application support is about supporting a application which can involve below task :

  1. Installation/Setup of Application in new environment.
  2. Configuration of application for the use by business.
  3. Resolving the issues coming after the environment setup is completed.
  4. Refreshing the QA/DEV environment from production system.
  5. Giving access to users for access to the environment.
  6. Resolving issues related to the performance in the environment.
  7. Integration of the application with other interfaces for business functioning.
  8. Testing all the business cases after environment change is completed.
  9. Automating manual tasks for saving time .
Aside | Posted on by | Leave a comment

Investigation for Slow SQL Query : SQL Profiler & Server Side Traces

Its really very time consuming to identify the slow query running on the MS SQL server earlier we were not aware about the tools but after SQL Server Profiler we got some understanding for query analysis . We will be following below approach for carrying out the investigation :

  1. Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
  2. Import the trace results into a database table and analyse them to find the queries or stored procedures that are having the most impact on the server.
  3. Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
  4. Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
  5. Implement the changes and review the effects.

Server side traces can also be used in case of profiler as it consumes more memory on the busy SQL server. To collect the server side traces we will be tracing below events in Trace Script :

  • The SQL Server events that you would like to capture
  •  The data columns that you would like to capture for each event
  • Any filters that you would like to create, in order to omit from the trace any occurrences of the event in which you are uninterested. For example, it is common to capture only those events that are generated by a particulate user, application or database.

In order to generate the trace definition, I must first create a trace within Profiler that defines all of the events, columns and filters that I want. For identifying poorly performing stored procedures, the most important events are:

  • RPC:Completed (in the stored procedures event category) This event is fired whenever a remote procedure call completes. An example of a Remote Procedure Call would be a stored procedure executed from a .NET application where the SQLCommandobject’s command type is StoredProcedure.
  • TSQL:BatchCompleted (in the T-SQL event category). This event is fired whenever an ad-hoc SQL batch completes executing. An example of an ad-hoc SQL batch would be a query run  from Management Studio, or a query executed from a .net application where the SQLCommand object’s command type is Text.

The most important data columns are TextData, CPU, Reads, Writes and Duration. Other columns, such as the LoginName,ApplicationName and HostName, may be useful for identifying where the query comes from, but they are not essential for identifying which queries are performing poorly.



Aside | Posted on by | Tagged , , , , , , | Leave a comment