One of the new enhancements in SQL Server Management Studio 2016 (SSMS) is the Scroll Bars tab, in which can be set various of settings for vertical scroll bar.
Read more »
Query optimizer changes in SQL Server 2016 explained
June 17, 2016Description
SQL Server 2016 includes a variety of query optimizer enhancements. Some of these have existed since the first previews while others were added later. This is an opportunity to discuss, test out, and validate the behavior and benefits of these changes!
Read more »Best practices to configure the index create memory setting in SQL Server
June 15, 2016Introduction
The Index Create Memory setting is an advanced SQL Server configuration setting which the SQL Server database engine uses to control the amount of maximum memory which can be allocated towards the creation of an index. In this article, we will take a look at the steps to resolve the below mentioned error message.
Read more »The system msdb database, introduction and tips
June 14, 2016Introduction
In a previous article, we talked about the tempdb system database. In this article, we will talk about the msdb database.
Read more »SQL Server network configuration
June 14, 2016SQL Server Network Configuration involves enabling the protocols that manage the connection to the SQL Server and configuring the available options for these network protocols. It also provides the means to encrypt the communication between the SQL Server instance and the client applications and hide the SQL Server instance from being browsed. SQL Server Network Configuration can be managed using the SQL Server Configuration Manager tool.
Read more »New feature in SQL Server Management Studio 2016 – Quick Launch
June 14, 2016What is Quick Launch?
Quick Launch is a new feature that is added in SQL Server Management Studio (SSMS) 2016. Quick Launch is search tool, but it doesn’t search code/contents of the script/files. Instead it searches SSMS itself including menu items, options and file names. Quick Launch is enabled by default when SSMS is installed and it is located at the top right corner of the menu bar (see below):
Read more »How to migrate users to a partially contained database in SQL Server
June 13, 2016Introduction
Microsoft introduced the Contained Database feature in SQL Server 2012. In this article we will take a look at how to migrate SQL Server Logins to a partially contained database in SQL Server to remove dependencies from the Instance of SQL Server where Contained Database resides.
Read more »Is this the end of SQL Profiler?
June 13, 2016Introduction
SQL Server Profiler is still a tool used to monitor our relational databases and our multidimensional ones. We used for performance and security purposes. However, in the SQL Server 2016, they announced that the SQL Profiler will be deprecated in future versions.
Read more »Querying remote data sources in SQL Server
June 10, 2016A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.
SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.
Read more »Troubleshooting the CXPACKET wait type in SQL Server
June 8, 2016The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using the parallel plan.
Read more »How to handle excessive SOS_SCHEDULER_YIELD wait type values in SQL Server
June 8, 2016The SQL Server SOS_SCHEDULER_YIELD is a fairly common wait type and it could indicate one of two things:
Read more »Revision history of an object change in a SQL database using Subversion
June 7, 2016In previous articles, I have already covered the revision history for Git and Team Foundation Server. Similarly, this article covers the revision history of committed changesets using Subversion as the source control system.
Read more »Installing SQL Server 2016
June 7, 2016Introduction
Microsoft SQL Server 2016 provides faster transactions and queries, deeper insights on any device, advanced analytics, new security technology, and new hybrid cloud scenarios. Along with these features, comes the new installation setup. Although it is similar to the installation of SQL Server 2014 and other older versions, there are few notable differences:
Read more »Useful DBCC log commands
June 1, 2016Background
When we’re architecting or troubleshooting issues in an environment, understanding how the log is set up and its history helps us identify if improving it will carry significant impacts overall. We have some built in tools that we can use, such as DBCC LOGINFO and DBCC SQLPERF and in this tip we look at how we can use them when we’re debugging issues, designing our logs for scale, or monitoring our current setup. We also look at some ways we can retain the information these provide so that we can use this for historic measurements and benchmarking, as well as using them for potentially alerting.
Read more »SQL Server 2014 contained databases
June 1, 2016SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.
Read more »Single package deployment in SQL Server Integration Services 2016
May 31, 2016SQL Server 2016 has some exciting enhancements to SQL Server Integration Services. In this article, we take a look at how the issue of single package deployment into an SSIS Catalog has been resolved with this SQL Server release.
Read more »Revision history of an object change in a SQL database using Team Foundation Server
May 31, 2016Similarly, as described previously in this article, where the revision history is covered for the Git source control system, we’ll present the workflow of reviewing the history of committed SQL database objects using Team Foundation Server (TFS) source control system. In order to use TFS and have SQL database objects being version controlled, Visual Studio is required, as well as TFS server, either installed on a machine or TFS through Team Services, which is actually TFS “in the cloud”.
Read more »Creating the perfect schema documentation script
May 27, 2016Description
System views allow us to gain access to information about any objects within SQL Server, from tables to extended properties to check constraints. This information can be collected and used for many purposes, one being the need to document our objects without the need to click endlessly in the GUI or to incur an immense amount of manual work.
Read more »Monitoring changes in SQL Server using change data capture
May 27, 2016Background
In multi-user environments, changes may occur frequently to the architecture, data, or overall structure that creates work for other users. In this series, we look at some ways that we can track changes on the data and architecture layer for pin-pointing times, changes, and using the information for alerting, if changes should be kept to a minimum. SQL Server comes with some built-in tools that allow us to monitor changes, and depending on the architecture, we can create tools that allow us also to monitor and identify changes near the time that they occur.
Read more »Introducing schema documentation in SQL Server
May 25, 2016Description
We often have a need to view object definitions in SQL Server, whether they be tables, triggers, or foreign keys. The built in tools are great for an object here and there, but are very cumbersome if you’re looking to generate create statements for a large number of objects.
Read more »Some replication architecture errors and their resolutions
May 16, 2016Background
From time to time, I’ve run into replication issues in inherited environments that I did not architect and some of these environments experienced errors in replication because of how it was constructed from the beginning. In this tip, we look at some of the basics in replication architecture and then at solving some of these problems. Some of the replication issues I’ve seen are caused by misunderstanding what is impossible and possible with replication.
Read more »How to create and customize SQL Server Templates
May 16, 2016SQL Server Templates are SQL scripts, containing SQL code, frequently used by developers and DBAs in their daily work (e.g. Create Table, Add Column, Drop Column, Create Database, Create Logins etc.):
Read more »Managing SQL Server transaction logs
May 13, 2016SQL Server databases consist of two physical files types; the data file in which the data and the database objects such as tables and indexes are stored, and the transaction log file in which SQL Server stores records for all transactions performed on that database.
Read more »
Azure Cosmos DB from zero to 10 minutes
May 10, 2016Introduction
In our last chapter, we talked about Cosmos DB, which is available in Azure and explained that it is an Azure Database used to store NoSQL documents.
In this new article, we will show how to create and query the database from zero. The only requirement is to have an Azure Account. If you have an Azure account, this tutorial will take you just 10 minutes (if not, you can complain in the comments section below 😉 ).
Read more »Troubleshooting some waits issues
May 10, 2016Background
On occasion, I’ll see waits that exceed what I expect well above normal and a few of them have some architecture and standards to consider following when troubleshooting, though like most waits’ issues, there can be other underlying factors that are happening as well. In this article, I investigate the three waits ASYNC_NETWORK_IO and WRITELOG. In general, waits vary by environment and server, so before reading this article an immediate question to ask is, “Do you know what’s normal for yours?” When a wait suddenly spikes, or if the architecture is designed in a manner that should prevent a specific wait from consuming time, and yet you see that the wait does, I would be concerned. In addition, because applications and environments differ by architecture, you may want to consider other troubleshooting steps, as these may not apply to your situations.
Read more »