Category: SQL Server Monitoring

List of All Redgate SQL Server Related Tools or Products

Leave a comment
SQLServerF1

There are various tools or products of Redgate developed or brought from other company’s which are related to SQL Server or useful for various tasks for DBAs, Developers, Consultants, Management etc. Depending on the kind of tasks the product or software can be used for, the products are categorized accordingly. Below are some of the Redgate SQL Server Related Tools or Products available.

Redgate Database Lifecycle Management Products: More and more companies are recognizing that agile practices such as continuous integration and continuous delivery are as applicable to databases as they are to applications. Implemented correctly, they reduce risk, improve efficiency, and deliver value to customers sooner.
Quite simply, they enable organizations to proactively manage the processes involved in the application and database lifecycle, including planning, development, release, and maintenance. Some of the softwares available under this category include Database Lifecycle Management, SQL Developer Suite, DLM Automation Suite, DLM Dashboard (FREE), SQL Source Control.

Redgate Development Products: These tools are primarily useful for SQL Server or other products developers used for designing, developing and building projects. Some of the useful tools under this category include SQL Compare, SQL Data Compare, SQL Source Control, SQL Search (FREE), SQL Prompt, SQL Doc, SQL Data Generator, SQL Dependency Tracker, SQL Comparison SDK, SQL Test, SQL Lock (BETA), SQL Developer Suite, SQL Toolbelt.

Redgate Database Administration Products: These tools are primarily useful for SQL Server or other products developers used for managing or administering SQL Server instances and databases. Some of the useful tools under this category include SQL Monitor, SQL Backup Pro, SQL Multi Script, SQL Index Manager, SQL Scripts Manager (FREE), SQL DBA Bundle.

Redgate .NET Products: Some of the useful tools under this category include .NET Developer Bundle, ANTS Performance Profiler, ANTS Memory Profiler, .NET Reflector, SmartAssembly, PInvoke .NET (FREE).

Redgate Oracle Products: Some of the useful tools under this category include Schema Compare for Oracle, Data Compare for Oracle, Source Control for Oracle, Deployment Suite for Oracle, DLM Automation Suite for Oracle.

Redgate MySQL Products: Some of the useful tools under this category include MySQL Comparison Bundle, MySQL Compare, MySQL Data Compare.

Redgate Azure Products: Some of the useful tools under this category include Azure Management Studio, Azure Explorer (FREE), Redgate Cloud Services.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Basics of Dedicated Administrator Connection (DAC) in SQL Server

Leave a comment
SQLServerF1

What is Dedicated Administrator Connection (DAC) in SQL Server?
Microsoft SQL Server gives a Dedicated Admin Connection (DAC). The DAC permits a head to get to a running case of SQL Server Database Engine to investigate issues on the server—notwithstanding when the server is lethargic to other customer associations. The DAC is accessible through the sqlcmd utility and SQL Server Management Studio. The association is just permitted from a customer running on the server. No system associations are allowed.

To utilize SQL Server Management Studio with the DAC, unite with a case of the SQL Server Database Engine with Query Editor by writing ADMIN: before the server name. Object Explorer can’t join utilizing the DA

How to Enable Dedicated Administrator Connection (DAC) for SQL Server instance?
To enable DAC, Connect to the instance and open a new query window, run the following command

sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;
GO

How to verify if Dedicated Administrator Connection (DAC) is enabled on a SQL Server Instance?
We can verify or confirm that Dedicated Administrator Connection (DAC) is working by connecting to SQL Server instance using the following in the “Instance” field of SSMS login window

ADMIN:

How many Dedicated Administrator Connections (DAC) can be opened at a time for a particular SQL Server instance?
Only one connection can be made using Dedicated Administrator Connections (DAC).

When is the Dedicated Administrator Connections (DAC) to be used?
Dedicated Administrator Connections (DAC) can be used in cases where SQL Server is running but not responding to any regular connections due to resource usage. DAC connection has its own memory and scheduler and dedicated thread, which allows connection through DAC to run queries even when the SQL Server is not responding. It is not advisable to run a large or complex queries with DAC, rather it is to be used to identify the resource consuming queries causing SQL Server to hang or to not respond and kill any such offending sessions.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

SQL Server Instance Level Objects Backups

Leave a comment
SQLServerF1

One of the important and common responsibility of a SQL Server DBA is to make sure a perfect backup/recovery plan is in place and that the plan can be implemented as per the SLA’s. Periodic testing is important to ensure that the plan works in case of unexpected disaster. In most of the environments, the backups of SQL Server databases are performed which include system and user databases, however there can be many scenarios where in one of the database may be corrupted and has to be recovered, in which case the restore of the database backups works great, but in some cases it is also a possibility that certain server level objects have to be recovered and restoring system databases is not an option and a time taking option. In such cases have backup of server level objects separately will serve greatly.

Thus it becomes important for a DBA to also consider performing backups of instance level objects periodically, so that the level of restores or control on the objects which required to be restored will be available. Also this will act as a secondary option in case the primary option of system database backups does not work in some situations. Below are the important SQL Server instance level objects which are to be backed up regularly and can be used later when required.

– SQL Server Instance Configuration properties: these setting can be backed up by saving the output of sp_configure values or the values of select * from sys.sysconfigurations.
– Database properties: The database properties gets backed up by the database backups, however saving these settings in a text document will be useful to keep track of settings and can be verified or compared if the values are changed by someone and we had to identify what were the values DBA team planned for.
– DDL triggers: There are server level triggers which should be backed up and will be useful if someone deletes the triggers.
– Backup Devices: During a backup operation on a SQL Server database, the backed up data (the backup) is written to a physical backup device. This physical backup device is initialized when the first backup in a media set is written to it. The backups on a set of one or more backup devices compose a single media set.

– Endpoints: SQL Server endpoint is the point of entry into SQL Server. It is implemented as a database object that defines the ways and means in which SQL Server may communicate over the network. SQL Server routes all interactions with the network via endpoints and each endpoint supports a specific type of communication.
– SQL Server Agent Properties: These settings are important and the backup of these settings are useful in cases where these values are changed by someone and we want to know what are the correct ones.
– SQL Server Agent Jobs: Very important to have backup of the jobs, so that we can recreate them easily without having to restore the MSDB database. It is important to keep in mind that some jobs will run DTS/SSIS packages which have be backed up separately where the packages have to be saved separately.
Linked Servers: Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.
SQL Server Logins: Logins allow access to users to connect to SQL Server, so important to keep a backup, so that we can recreate the required logins if they are accidentally deleted.

Other SQL Server instance level objects which can be backed up include SQL Server Agent operators, SQL Server Agent alerts, SQL Server Server Roles, SQL Server Audits, etc

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Reviewing Security Configuration of SQL Server

Leave a comment
SQLServerF1

Security is one of the most important aspects for any technology. As SQL Server is an RDBMS product which stores users data which is important and critical, so it is common to see many attempts been made to compromise and get access to the important user or business related data. This brings responsibility of the DBAs, developers, management to plan and implement best security practices to avoid any such attacks from succeeding. It is important to periodically review the security settings to make sure that there are no gaps or possibilities of security breach. Below are list of items or objects which a DBA team may want to periodically review and verify to make sure that the security configuration is as per best practices.

Database Owners: By default, any users who creates the databases becomes the owner of the database. Mostly DBAs who create the databases become the owner of that database, so it is important to immediately change the database owner to SA or other standard account. Periodically review the database owners to make sure there are no user specific domain accounts being the database owners.
Windows or SQL Server Logins: Periodically review all the windows logins, SQL Server logins and windows groups which has access to the SQL Server instance. Remove any logins which does not require access anymore. It happens that some user leaves the company but the logins still remain. Sometimes access is given on temporary basis for a project work and would not removed after the completion of the project. Periodic review of the logins should resolve this issue of unauthorized logins being present at server level or at SQL Server level.

Server Level Permissions: There are different kinds of server level and database level roles provided by SQL Server. Make sure that only the required permissions are granted to the users. Providing more permissions than what is required will cause serious problems. It is important to periodically review and revoke any additional accesses granted to any users.
SQL Agent Jobs Owned by Windows Domain Accounts: The DBA who creates a SQL Server Agent job by default becomes owner of that job, so make sure to change it to SA
.SQL Server Client Protocols: By default SQL Server allows communication through ports(TCP/IP, Shared Memory, Named Pipes, VIA). Mostly it is not required for all these protocols to be enabled, some of them can be disabled when application does not use them, so that any one trying to gain access through that protocol would be blocked.

SQL Server TCP/IP port: Default instance of SQL Server uses port 1433 by default, so it is advisable to change to this some other port other than 1433 as per best practices. But making this change may cause applications to fail connecting to SQL instance, so care has to be taken to make changes to application connection string to use the port number used by SQL Server or to enable SQL Browser service. SQL Server Named instances can be configured to use either permamnent statis port or a dynamic port which may change after SQL or server restart. It is advisable to configure static port for SQL Server Named instances.

The above checks or recommendations are high level and there are mode advanced check that are to be performed for more critical servers. There are compliances like SOX HIPAA which put rules to be followed for critical servers. Also, care should be taken to implement the recommendations and proper testing has to be done prior to any implementation to make sure applications does not break due to the changes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Quick Health Check After Restart of SQL Server Instance

Leave a comment
SQLServerF1

As a DBA one of the roles is to verify, check and monitor critical or important SQL Server instances after restart or cluster failover or reboot of the server. Sometimes it is possible that there are some problems after the server is rebooted or SQL instance is restarted or failover is performed, so a quick check should verify to make sure nothing is wrong or broken. Also, sometimes there are unplanned server reboots or cluster failovers or SQL instance restarts, so keeping the items which are to be monitored after the restarts will be good.

Also, in many environments there are automated monitoring tools which detect any problems, but for critical servers, it is always good to manually have a quick health check to make sure there are no issues. This is because sometimes automated monitoring tools may not start properly or does not detect the problems due to bugs or other issues, so checking critical servers manually is always a good choice. Below are some of the important items which you may want to consider checking after the restart of SQL Server instance, either planned or unplanned.

– Verify the Database Status to make sure all the databases have successfully recovered and shows online status.
– On cluster servers, if you have configured Preferred owners for any SQL Server instance, make sure the SQL Server instances are running on node they are expected to run on.
– There may be jobs which might be running during the time of restart, they would have failed in middly, so it is good to check for any failed jobs and restart them if necessary.
– If there is database mirroring setup, monitor the Mirroring Status to make sure it caught up and is not in suspended state.
– Starting SQL Server 2012, if AlwaysON AG is setup, then make sure Availability Group Status is synchronized and there are no failures and databases or AG not in resolving state.
– If replication has been setup, verify and make sure Replication is functioning fine and all jobs are running as expected.
– Check SQL Server errorlog and SQL Agent log log make sure there are no errors.
– Check Event Viewer application and system logs to make sure there are no errors.

There may be other items to check as well depending on the features used or enabled on the SQL Server instance like Audits, CDC, MDW, Policy management, etc, so list down the features implemented and prepare the list specific to the servers.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Important SQL Server Alerts for Monitoring Incidents and Performance

Leave a comment
SQLServerF1

It is important for SQL Server DBA to configure alerting for the SQL Server instances so that an E-Mail or a alert is sent to the DBA team in case of any problems which are critical or warnings. Handling and fixing the warnings will avoid any critical events from happening. It is not possible to monitor all the servers and instances 24/7, so there should be either a custom solution or third party monitoring tool to detect and send alerts when certain condition is met.

When preparing a custom solution or enabling alerts on 3rd party tools, we need to identify the list of important alerts which are to be monitored and how frequent these checks are to be made and the threshold on which the alerts are to be sent to the DBA team. Also, need to understand which of these are critical alerts and which can be warning level alerts.

Below are some of the common and important alerts with their description, Severity and what should be the general threshold for these alerts to be chekecd on.

Critical Alerts – These alerts generally cause the system to go down or can cause an outage. These alerts should be identified and resolved quickly and in case the issue is not resolved, this should keep sending the alerts. Also there should be option to temporarily snooze or turnoff the alerts which DBA team is working on them and they expect that it may take some time to fix the issue, this will avoid unnecessary alerts to be sent continuously.

Below are some of the Critical Alerts which need to be monitored and resolved immediately.

SQL Server Service State – This check should occur every 5 minutes and should check weather the SQL Server service is running or stopped. This should check for all the SQL Server services and SQL Server Agent services, etc

SQL Server Connectivity – This check should occur every 5 minutes to check if connections can be made to the SQL Server instance.

SQL Server Cluster Failover Event – On cluster servers, we need to have check which can detect any failover events with the SQL Server cluster resources and also should provide the state of SQL Server cluster resources. This check should occur every 5 minutes.

SQL Server Errorlog Scanner – SQL Server Errorlog consists of various errors, warning messages and informational messages. As this contains a mix of critical, warning and informational messages, we need to have this check run every 5 minutes. There should be customization allowed to list the messages which can be ignored, so that the alert for informational and some warning messages can be suppressed and alerts need not be sent for those.

CPU Check – This is a windows level check which need to identify the CPU usage on the server and raise alert if the CPU condition is consistently high like above 90%. This check is to be made every 5 minutes.

Memory Check – This is a windows memory check to identify if there is any low memory condition on the server which can affect all the services running on that server. This check needs to be run every 5 minutes.

Low disk space – This check needs to identify the free disk space going down to very low like less than 10% free disk space. This can soon cause an outage as there will not be any more room for the database files too grow on the disk.

Blocking – Blocking in SQL Server is considered critical as it can cause disruption to the application and the users will face issues. This needs to be checked every 5 minutes and to be alerted. There should be mechanism to snooze the alert which DBA is looking into the issue.

Warning Alerts – These alerts generally do not cause any outage or no immediate action is required, but is important to troubleshoot and fix it as soon as possible to avoid it from escalating to an critical alert. The threshold for these alerts can be little relaxed compared to critical alerts.

Below are some of the Warning Alerts which need to be monitored and resolved in a timely fashion to avoid escalating in to Critical alerts.

Disk space below 20% Threshold – This should check of the free disk space on any drive falls below 20% free, then alert needs to be sent to DBA team. DBA can check and identify the cause of low disk space and try to resolve immediately and it can go further low and can raise a critical alert or can cause an outage if there is no more free space left.

Backup Checks – It is important for the DBAs to make sure there are regular backups of all the databases. This check can be made daily once or twice to check of the Full and Log backups are happening regularly. Also, separate check can be used to just check the log backups and the check can be scheduled to run more frequently on important systems.

Job Failures – This check should check for any failures with any jobs. This check can be configured to check every 5 minutes. Depending on the importance of the job, DBAs can look into it in case of any job failure.

Long Running Queries – This check should send list of long running queries which are taking more time like more than 15 seconds or any other threshold based on the baseline.

Suspended Sessions – Although it is not common, but some environments can have too many suspended sessions thus causing exhaustion of all the available worker threads thus leading to an outage. So, it would be good to check every 15 minutes to see if there are too many suspended sessions.

Errors or Warnings from EventsLogs – There can be some critical or warning events that may have been raised in the windows Application or System eventlogs, so it is important to send alerts for such errors. This can be configured to send the list of errors or warnings every day once or few times in a day.

There can be many other alerts that can be configured and may be specific to each environment, but above should be monitored on all the server running SQL Server instances.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.