How to detect SQL Server performance issues using baselines – Part 1 – Introduction

In the following series of articles we will discuss what baselines are, how they work and how to apply them to everyday SQL Server performance monitoring. This article will provide a brief overview of baselines and the statistical calculations behind them. Later we’ll apply this to real information gathering techniques to allow DBAs to create their own baseline statistics and we’ll also show how to baseline performance “out of the box” with a 3rd party tool

How to ensure continuous auditing of SQL Server with zero audited data loss

An optimal continuous SQL Server auditing approach must include:

  1. Continuous auditing
  2. Real time data collection
  3. Ability to generate meaningful reports
  4. Alerting on unwanted activities
  5. Tamper proof store of audit data

In many cases, the primary requirement that must be fulfilled is that auditing must be performed with zero auditing data lost.

How to implement check out and lock policies in the shared database source control development model

In a multi-user database-development environment, avoiding conflicts and overwrites with edits, and ensuring all changes are audited and recorded is important. Until recently however, effective tools for SQL development management have lagged well behind their client developer equivalents, like Visual Studio. In this article, we will look on specific database source control challenges and a way to address them use new SQL developer tools that make meeting these straightforward and easy

How to analyze SQL Server database dependencies in client code e.g. C#

ApexSQL Clean has, among others, a useful feature that most people are not even aware of. It provides a client SQL code analysis in C#, Delphi, VB.NET, XAML, XML, ASP.NET, HTML, CSS code etc., and detects which SQL objects are actually being used and which ones aren’t in the referenced database. The SQL code analysis is performed by inspecting the code file for objects in the SQL database. This feature helps keeping SQL databases clean and organized

How to automate batch SQL object scripting and committing to source control

Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.

In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.

How to schedule a database backup restore in SQL Server

When recovering from a SQL Server database failure, a database administrator needs to restore a set of SQL Server database backups in a logically correct and meaningful restore sequence. With this in mind, to the goal is to devise a disaster recovery strategy by creating a solid backup plan, as well as a proper database restore plan in SQL Server. This article will describe 2 different solutions for creating and scheduling a database restore in SQL Server.

How to create a simple database recovery solution using SQL Server log shipping

To prevent accidental data loss, it is always good to ensure that there is a disaster recovery solution available. This can be easily achieved by having a standby copy of a primary database on another SQL Server instance, which can be achieved via log shipping.

SQL Server Log shipping is a solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server, hosting a live database, and one or more secondary servers that host database copies. The process is fairly simple – a database is backed up and restored from a primary server to the secondary server(s). At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary servers to keep the databases in sync.

How to create a database cleanup report in SQL Server

A SQL Server database can have stored procedures, tables, defaults, views etc. that aren’t being used anymore, and unless you determine which of your objects are truly unreferenced you will be stuck with them, or risk breaking your database if you delete a wrong object. This is where ApexSQL Clean can help. ApexSQL Clean’s main features are already described in SQL code analysis – full body scan of a SQL database, but here we would like to point out and present one more useful feature: creating a sql database cleanup report. In order to have a complete database cleanup report and dependencies analysis procedure, ApexSQL Clean has a reporting mechanism, which allows keeping track of selected objects and their dependencies.

How to clean up unused objects from a SQL Server database

It is not a rare case that a DBA inherits SQL Server databases with many unused SQL objects. By using ApexSQL Clean, it is easy to clean a SQL database from these unneeded objects and prevent extra objects from making an impact on development by slowing it down and increasing the maintenance workload (e.g. all unused objects still have to have their permissions set, be conformant with coding standards, etc.)

How to remove SQL Server database objects safely

ApexSQL Clean can help determine all dependencies in a SQL database; it also analyzes the impact of potential changes and deletions on SQL database, and determines object interrelationships within the database, between different databases, SQL scripts and even applications