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 »
Nowadays many customers are spread globally and need to manage the requirement for users to connect from/to any location, perform an activity (insert, update, delete), and the databases should be kept synchronized across multiple sites.Read more »
In earlier chapter, we explained 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 »
Whenever a query is executed in SQL Server, its execution plan, as well as some useful execution data are placed into the plan cache for future use. This information is a treasure trove of metrics that can allow some very useful insight into your server’s performance and resource consumption. Much of this information would be difficult or impossible to acquire otherwise.
Understanding how to access and use the metadata about query execution will provide us the tools we need to answer questions about our server and gain fascinating performance data. I’ve found myself spending more and more time writing, tweaking, and using queries against the plan cache lately and look forward to sharing these adventures with you!Read more »
Why is my database so slow? This query used to be so much faster. Why does it take so long to rebuild my index? How come it was fine last month? Every day I am asked these types of questions by clients. Every day! A lot of database developers and application developers do not realize that indexes are ever changing entities within your database or rather they need to be monitored closely and managed periodically to remain efficient. I cannot even count the times someone tells me “but we have index’s on this or that column and it was fine last month” and so on. All while they fail to realize or even tell me that the database just took on, updated or deleted 1,000,000 records for example, which would definitely change the footprint of the data, making the index’s unsound or in need of help. Even adding 50 new users that use the data differently could require new indexes. That being said, I decided to automate a quick and easy data gathering and reporting job that helps to answer these questions. Most of the time query performance questions can be answered by determining the fragmentation levels of index’s, if there are missing index’s, duplicate index’s, unused index’s and what are the heavy hitters in regards to queries and are queries running in memory or to disk and how many executions. My favorite thing to do with SQL Server is automate, automate and automate the tasks that are asked of me over and over.Read more »
In this new article, we will create a SQL Server Report using SQL Server Reporting Services from a SQL Server machine installed in an Azure Virtual Machine. We will show step by step how to connect from a local machine to a VM Azure Database and generate a SQL report.Read more »
In my previous article, we went over the reasons why DBAs should use version control, as well as the benefits of Mercurial as a specific choice. We also gave three examples of instances where source control can come in handy (though to be honest they were picked from a much longer list). In this article, I’m going to go a step further and actually walk you through setting up your first repository (locally for now, we’ll go into setting up a remote one later), making your first commit, and making (and viewing) changes to your newly tracked files. Let’s get started!Read more »
Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.Read more »
In this article we will review migration from disk-based tables to in-memory optimized tables. This article assumes that you already understand the pros and cons about In-Memory Technology, for more articles about this, please refer here.
There are some options available on SQL Server 2014 and SQL Server 2016 that will help you to identity, discover and track the tables and stored procedures that can be good candidates to be ported to memory. Furthermore it’s possible to check if the table meets to all the pre-requisites established. Here is the list of the features that we will take a look into.Read more »
As a DBA I am often asked to provide lists of all active users ID’s or groups for a specific server or database. In addition to this, I am also asked to provide a list of failed logins. These requests are frequent enough that I decided to automate the process of gathering this data and provide two nicely formatted HTML emails. I am not going lie, manager types love these reports, and anything that makes managers love my work I am all about! I’m also all about automating anything that makes my job easier. Call me lazy or call me prepared, I hate having to do something over and over that I could easily throw into an SSIS package or Agent Job and just schedule it to do it for me. This entire process consists of using SQL Server Integration Services (SSIS), 4 tables and a SQL Agent Job containing 2 reports (Failed Logins and Active SQL Server Users). The SSIS package, along with all of the queries and scripts are attached at the end of the article.Read more »
If an environment chooses to use snapshot or transactional replication, one useful exercise is to ask the technical end user (or client) what they think replication does. If you have access to a white board, you can even ask them to demonstrate what they think replication will do for their data. Generally, these technical end users will plot something similar to the below image, where we see a table with data being copied to another table with data.Read more »
When you have thousands of SQL Servers, it is very hard to administer all of them. This article will show some tips to help you with these types of tasks.
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 »
This problem demonstrates a very common scenario. When a backed up database is restored to a different location or server, the restored database users that exist inside of it, lose their association to the new server login objects.
This problem occurs because the server id in the user data is still the old server and must be changed to the new one.
The DBA task is then to restore the connection between the login and the database user object and to link them together again.Read more »
SQL Server Management Studio is a software application that helps users to easily access the SQL Server database engine and manipulate objects and data stored on SQL Server databases. The first version was released with Microsoft SQL Server 2005, replacing Enterprise Manager as the primary interface for Microsoft SQL Server.Read more »
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 »
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 »
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 »
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 »
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.
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 »
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 »
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 »
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 »