- 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
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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”.
It is the job of the DBA to monitor performance and to determine ways to optimize database performance.
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.
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.
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.
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.
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.
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.
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 :
- Installation/Setup of Application in new environment.
- Configuration of application for the use by business.
- Resolving the issues coming after the environment setup is completed.
- Refreshing the QA/DEV environment from production system.
- Giving access to users for access to the environment.
- Resolving issues related to the performance in the environment.
- Integration of the application with other interfaces for business functioning.
- Testing all the business cases after environment change is completed.
- Automating manual tasks for saving time .
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 :
- Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
- 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.
- Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
- Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
- 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.
We are working to find out the best root cause analysis of performance troubleshooting for MS SQL Server .
1.Incorrect plan used by Stored Procedure
3.Inefficient queries – Bad estimates causing has joins and index scan
5.To much data retrieval . Not using WHERE clauses
6.DBAs relying on Perfmon rather than SQL Server Profiler to troubleshoot SQL Server Performance Problem
7.Using cursors rather than set based T-SQL
8.Slow performance on SQL Transaction Logs causing serious bottlenecks
9.SQL Data file autogrow causing transactions to stall
10. Starving the OS of memory due to poor memory configuration on SQL Server
11.Placing all databases on the same drives causing IO delay issues
12.Executing bulk imports at Production peak times causing performance issues.
13.Using SQL Server triggers and following best practises, particularly using nested or recursive triggers 14. DBA not performance testing a new disk subsystem
15.DBA relying on the “Kill it by Iron” approach. Not attempting to find the bottlenecks
16.Queries not hitting indexes and forcing scans, when using VARCHAR(max)
17.AntiVirus scanning database files
18.Screensavers with intensive CPU usage
19.High levels of blocking and deadlocking
20.High row count estimates causing hash joins with table/index scans , result:high CPU
To resolve the issues related to performance issues MS SQL server we need to be aware about the below tools for resolving the issues :
- Dynamic Management Views (DMVs) and System Catalog Views
- Profiler and Server Side Traces
- Windows Performance Monitor
- Built in performance reports in SSMS
- Query Plans
- Database Tuning Advisor
We need to learn these tools to collect the performance related data for resolutions.