MS SQL Server backup optimization January 19, 2016 by Kaloyan Kosev Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is a whole new world in regards to optimization you can do to make them faster, better and … smaller. But why bother? Considering that the modern databases grow at such fast pace you may face a situation where you are not able to fit in your maintenance windows or service contract obligations. Let us take a look on the three main areas where we can work on: Read more »
Removing the risk from important maintenance tasks in SQL Server January 18, 2016 by Ed Pollack 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 »
Connecting PowerShell to SQL Server January 13, 2016 by Shawn Melton Introduction PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work. There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time. Read more »
Contained databases in SQL Server January 12, 2016 by Rajendra Gupta As we know there are two types of authentication available in SQL Server Windows authentication and SQL authentication. In Windows authentication we use Active directory authentication to connect with SQL Server which makes the most secure authentication method as it can have complexity, group policy configured at AD level applied to all domain servers while in SQL Authentication SQL users are created inside SQL and provided required permissions. The Permissions includes server wide and database wide. The logins can have certain permissions at the database level might be read or write etc. Read more »
Step by step installation of SQL Server Management Studio (SSMS) 2012 January 12, 2016 by Stefan Daniel Plačkov SQL Server Management Studio (SSMS) is the official Microsoft application used for managing all components within Microsoft SQL Server. It is a powerful tool which allows users to control any objects in the SQL Server. It also provides script editing and GUI tools which are easy to use and user friendly.SQL Server has multiple editions including: Enterprise Edition, Enterprise Core Edition, Business Intelligence Edition, Standard Edition, Web Edition, Express and Developer Edition each with its own features and functions. Read more »
SQL Server non-clustered indexes with included columns January 11, 2016 by Ahmad Yaseen SQL Server indexes are created to speed up the retrieval of data from the database table or view. The index contains one or more columns from your table. The structure of these keys are in the shape of B-tree distribution, enabling SQL Server to find the data quickly. Read more »
Tips and tricks for SQL Server database maintenance optimization January 11, 2016 by Miroslav Dimitrov 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 »
Monitor the Query timeout expired message from a SQL Server Agent job January 11, 2016 by Ahmad Yaseen 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 »
SQL Server lessons from a TFS installation January 8, 2016 by Sifiso W. Ndlovu Introduction More often than not (at least in places I’ve worked at), the job of installing, configuring and subsequent administration of Team Foundation Server (TFS) is performed by different individuals to those administering enterprise applications such as Windows Server, SQL Server, SharePoint etc. This is because TFS, unlike other enterprise applications, often has to be administered from both the server side (i.e. TFS Administration Console) as well as the client side (i.e. using Team Explorer in Visual Studio) – it is thus not surprising that a TFS administrators may once had been a developer. The benefit of having a TFS administrator with a software development experience is that it may be easy to get developer-buy-in into the tool. However, the disadvantage to this is that developers usually get accustomed to doing things in a certain way – like connecting to SQL Server using (local) convenience names. Read more »
Data boundaries: Finding gaps, islands, and more January 6, 2016 by Ed Pollack One of the more difficult challenges we face when analyzing data is to effectively identify and report on boundaries. Data sets can contain any number of significant starting and stopping points that may indicate significant events, such as missing data, important business events, or actionable changes in usage. Regardless of the use case, knowing how to quickly locate and manage data boundaries is extremely useful. Knowing how to design solutions that can effectively avoid these scenarios can also be helpful in the long run. Read more »
Creating a gap in sequences – TSQL Stored Procedure advisor January 6, 2016 by Eli Leiba Introducing the Problem Gaps existence in automatic sequenced columns occurs all the time. Missing identity values (or other sequencing values) occur for a variety of Reasons. The most common reasons include: roll backed transactions, failed inserts and Deletes, large row deletes after delete commands that occur after many inserts to a sequenced table and so forth. Read more »
Backup SQL Server database to and restoring from multiple files January 6, 2016 by Ahmad Yaseen Introduction: The SQL Server backup provides an important solution for protecting critical data that is stored in SQL databases. And in order to minimize the risk of data loss, you need to make sure that you back up your databases regularly taking into consideration the changes applied to your data. It is a best practice to test your backups by restoring random backup files to a test environment and check that the backup files are not corrupted. Read more »
Introduction to SQL Server database continuous integration January 5, 2016 by Marko Radakovic What is SQL database CI? Continuous integration (CI), in the context of databases, refers to the practice of isolated database changes to be integrated, as soon as they are made and pushed to a source control repository. In the early days of CI, daily integration was a rule of thumb to follow. However, today, it is more common, than not, that database changes are integrated several times a day. Each change needs to pass an established testing plan that executes automatically on detected changes committed to the repository and if everything is ok, changes will be automatically merged using the build script. with the remaining code, into a new build that will be pushed to other environments (i.e. production, QA). Read more »
The tempdb database, introduction and recommendations January 5, 2016 by Daniel Calbimonte Introduction Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb. In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it. Read more »
Multiple options to transposing rows into columns January 4, 2016 by Sifiso W. Ndlovu Introduction One of the primary functions of a Business Intelligence team is to enable business users with an understanding of data created and stored by business systems. Understanding the data should give business users an insight into how the business is performing. A typical understanding of data within an insurance industry could relate to measuring the number of claims received vs successfully processed claims. Such data could be stored in source system as per the layout in Table 1: Read more »
How to run a backup on multiple SQL Servers December 31, 2015 by Daniel Calbimonte Introduction 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 »
SQL Server: Lock settings to use to enhance partitioning capability December 30, 2015 by Pinal Dave When talking about performance and scalability inside SQL Server, I don’t see anyone missing out on the fact to mention how locks get involved. I often see DBA’s complain to developers that their code is not scalable and they are experiencing heavy locks in the system because of the way the code has been written. The more I work with SQL Server, the more I start to understand some of these nuances. Read more »
How to install SQL Server 2014 Management Studio December 30, 2015 by Stefan Bozovic There are multiple editions of SQL Server 2014 (Enterprise Edition, Enterprise Core Edition, Business Intelligence Edition, Standard Edition, Web Edition, Express and Developer Edition). SQL Server 2014 has a free edition – Express and while the server itself lacks some options that other editions have, SQL Server 2014 Management Studio is the same and can be used with each of these. Read more »
Construct a special multi-statement table function for checking SQL Server’s health December 24, 2015 by Eli Leiba The Problem Checking the many SQL Servers health conditions is one of the DBA basic tasks. Monitoring many servers and databases, day after day can be a very tedious job. There are many aspects to look for when checking the server performance figures. Such aspects include among others: CPU, Read and Write Disk counters, Various memory counters and locks performance counters. The DBA needs a tool that checks the most important counters and output a report that states whether the server is considered to be healthy or not. For every aspect, a counter is chosen to represent it to be included in the overall report. Read more »
Creating an automatic View to an In-line table function conversion stored procedure December 22, 2015 by Eli Leiba Introducing the Problem We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses. When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded. Read more »