As a Microsoft SQL Server Database Consultant, I have to visit multiple companies with variety of SQL Server versions and patch level. Generally speaking, we need to analyze the environment of these clients and get information about the infrastructure. To do so, we have to run multiple scripts and tools on almost all the machines at client. Though it’s a part of our job but at times it takes hours to get even the basic information about the SQL Server versions, patch level, configuration and small details which even client does not have.Read more »
As SQL Server database administrators, we should all know that, most of the time, a database user is linked to a SQL Server login. We do this to tell SQL Server that a SQL login LoginA has access to database Db1 using the context and permissions of UserA database user.Read more »
CHECKSUM is an option for page verification that is available at the database instance level and we can see what level of verification each of our databases are currently using by the following query:Read more »
Standalone Managed Service Accounts, introduced a long ago with Windows Server 2008 R2, were a ray of hope for the database administrators. They promised to provide automatic password management and simplified SPN management, meaning that the time-consuming task of maintaining passwords would be a thing of the past (not to mention the required downtime for this).Read more »
Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server.Read more »
If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you.
Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded?Read more »
Have you ever been in a situation where you had to manage hundres of MSSQL Servers? Well, I am right on track here and believe me it does not get easier, even minor tasks take quite the time to do in large enterprise enviornments. One of the solutions I like and I’m using to ease the administration of standard maintenance jobs across my servers was ‘master – target’ jobs. Within the article to follow I will introduce you to the concept, the prerequisites and the limitations they have.Read more »
SQL Server Policy-Based Management was introduced in SQL Server 2008, to make it easy for database administrators to define and enforce SQL Server best practices and company standards in the form of policies. This feature is available in both Enterprise and Standard SQL Server Editions.Read more »
One of the most popular complaints from developers to DBAs involves alerting, whether from third party tools or alerting built by other developers or DBAs in the environment. Building or using alerts for important applications, data layers, or processes within a SQL Server environment offer everyone benefits, but can become noisy if they’re architected poorly, or the purpose isn’t considered. In this article, we look at considerations for building effective alerts that tell us when something is wrong without creating situations where we learn to disregard them. We want to make sure that we respond when we need to, and not always be on high alert when there is no issue.Read more »
In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.Read more »
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 »
In this article, we will explain step by step how to connect remotely to a SQL Server Express instance. Remote access is the ability to get access to a SQL Server from a remote distance in order to manipulate data which are located on that SQL Server.Read more »
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 »
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 »
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 »
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 »
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 »
If you are a DBA and you have one single server with SQL Server to administer, backup the database is a simple task. However, there are several companies with hundreds and some with thousands of Servers with SQL Server. To handle multiple SQL Server backups at the same time is a nightmare.Read more »