SQL Server maintenance

Shawn Melton

Perform volume maintenance tasks security policy

April 4, 2016 by

Introduction

You may see it more commonly referenced as Database Instance File Initialization (DIFI). If you are not familiar with the file initialization, this is the process SQL Server has to go through when it is creating the data files for a given database, and also during an expansion event (either manually or from auto growth) for a database. It only pertains to the data file(s) of the database, as log files are not affected by this security policy. SQL Server will “zero out” the file, basically fill it up with a bunch of zeros to allocate the amount of space requested. If you are a new DBA, this configuration actually goes all the way back to SQL Server 2005.

Read more »
Daniel Calbimonte

How to execute jobs on multiple SQL Servers

February 18, 2016 by

Introduction

In earlier chapters, we explained how to administer multiple servers using the ApexSQL Backup and how to run queries in multiple SQL servers using the SQL Central Management Server. In this new chapter, we will show how to propagate a job from a SQL Server Master Agent Job to a target server.

This feature is called Multiserver Administration. In a multiserver administration, you need a Master Server and one or more target servers. In the master server, you create a copy of the job and then it is copied and executed in the target servers.

Read more »
Daniel Calbimonte

How to run multiple queries using the Central Management Server

January 25, 2016 by

Introduction

When you have thousands of SQL Servers, it is very hard to administer all of them. These article, will show some tips to help you on these types of tasks.

In other article, we showed how to backup a database on multiple SQL servers at the same time using ApexSQL Backup. In this new article, we will show how to run T-SQL scripts against multiple Servers using SQL Server Management Studio (SSMS). To do this, we will use the Central Management Server. The main idea of this feature is to administer multiple servers in a centralized way using queries or policies. This feature is available in SQL Server 2008 or later versions and cannot be applied in older versions.

Read more »
Ed Pollack

Removing the risk from important maintenance tasks in SQL Server

January 18, 2016 by

As database professionals, we are often in very close proximity to important processes, data, and applications. While we adopt the mantra of “Do no harm”, many maintenance or reporting tasks that we create carry unseen risks associated with them.

What happens when a drive fills up during daily differential backups? What if an index rebuild job runs abnormally long and interferes with morning processing? How about if a data load process causes extensive resource contention, bringing normal operations to their knees? All of these are planned events, yet can cause considerable disruption to the very processes we are trying to safeguard.

Read more »
Miroslav Dimitrov

Tips and tricks for SQL Server database maintenance optimization

January 11, 2016 by

Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.

Read more »
Ahmad Yaseen

Monitor the Query timeout expired message from a SQL Server Agent job

January 11, 2016 by

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

Read more »
Miroslav Dimitrov

SQL Server Transactional Replication Moving Distribution Database – step-by-step guide

November 27, 2015 by

SQL Server replication is a relatively old high-availability solution part of the Microsoft world. In fact, there have not been any significant changes to this solution in the recent versions SQL versions, but it is still a widespread mechanism for distributing objects from one database to another and synchronize them. Replication is very useful when you have remote and mobile users accessing your data. Let’s cut to the chase and give you more details about our specific case. 🙂

Read more »
Derik Hammer

Multiple Server Management – Finding the Database Server

May 6, 2015 by

Every organization has at least one of those guys. You are the guy if you have been with the company long enough that you are very familiar with all of the servers in multiple environments. You can easily translate the SharePoint server into its server name, CL-DB-001-B\SP_2010 and you know all of the DNS aliases pointing to that server as well. Your super power also makes you a magnet for everyone in IT to come and ask you where this database is and what is on that server.

Read more »
Kaloyan Kosev

How-to: Proactive capacity management

December 26, 2014 by

Capacity management is one of the most important tasks you have to perform as a DBA. Usually we rely on threshold alarms for the disks volumes and the databases, however I find it very suitable for me to take actions in advance – this way I am sure the SQL Servers under my supervision will have the needed space to operate without issues. And yes, there is the auto-grow, however it’s a safety mechanism which role is to ensure that even in critical situations you can have your database operational, however this does come with a negative performance impact which I try to avoid for my key production systems.

Read more »
Daniel Calbimonte

SQL Server Replications

November 30, 2014 by

Introduction

This article is for people who need to replicate the SQL Server Databases. SQL Server includes several types of replications to synchronize the databases across different SQL Servers.

Sometimes we need to replicate the data to have a Backup. Sometimes we need to synchronize Servers that are located in different cities or countries.

In this article, we will introduce you to the SQL Server Replication Theory and then we will show a step-by-step tutorial to replicate a Table using the Merge replication.

Read more »
Ivan Stankovic

SQL Server Policy Based Management – Categories and Database Subscriptions

May 21, 2014 by
In the recent articles (see below) about SQL Server Policy Based Management, we have explained the terms used by the feature, how it can be used in different environments and for various scenarios. We have also described how its basic purpose, evaluating declared rules against specific SQL Server instances and their objects, can be extended on other aspects of SQL Server such as SQL Server Audit is. Read more »
Ivan Stankovic

SQL Server Policy Based Management – applying policies to non-compliant targets

May 8, 2014 by
SQL Server Policy Based Management provides means to declare certain states and properties for SQL Server instances and their objects in a form of policies. These policies can be evaluated against a set of SQL Server instances, or against a specific SQL Server instance and a set of its objects that can be narrowed even more using additional policy conditions. However, even an evaluation of declared policies against their targets (SQL Server instances and their objects) produces viable results, in a form of evaluation reports that shows which target does and which one does not comply with the policy, this is not enough. After non-compliant targets are identified, they need to be altered in order to comply with the evaluated policies. Read more »
Ivan Stankovic

SQL Server Policy Based Management – best practices

May 5, 2014 by
In SQL Server Policy Based Management, best practices represent guidelines in a form of Policy Based Management policies that are in common SQL Server use scenarios considered as the best way to configure SQL Server instances and their objects. For example, it is considered a best practice for most SQL Server environments to use and enforce Windows Authentication mode. Violations of these policies can indicate SQL Server configurations that can result in low reliability, poor performance, increased security risks, unexpected conflicts, or other potential problems. Read more »
Ivan Stankovic

SQL Server Policy Based Management – The On Change evaluation mode

April 25, 2014 by
The Policy Based Management feature we described in the series of articles (see below) provides an efficient method to declare and enforce policies related to SQL Server instances and their objects. The main purpose of the feature is to facilitate maintenance of a single and, what’s more important, multiple SQL Server instances across the enterprise. Policies can be created on one SQL Server instance, and applied to other SQL Server instances afterwards, as explained in details in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances article. Read more »
Page 2 of 3123