Parallel Nested Loop Joins – the inner side of Nested Loop Joins and Residual Predicates May 22, 2017 by Neeraj Prasad Sharma This article is the second part of the Nested Loop Join Series. In the first part, Introduction of Nested Loop Join, we gave a brief introduction to Native, Indexed and Temporary Index Nested Loop Joins along with CPU cost details. Read more »
What are SQL Server deadlocks and how to monitor them May 19, 2017 by Jefferson Elias Introduction As a DBA, I’ve been, more than , confronted with performance problems. Sometimes, poorly written code or lack of indexes will generate blocking conditions that we refer to as a“Deadlock”. Due to their nature and like any similar blocking situation, deadlocks can have a direct impact on user experience and the more they occur, the bigger the impact. Read more »
How to migrate MySQL tables to Microsoft Azure SQL database May 18, 2017 by Daniel Calbimonte Introduction Today, it is a common practice to migrate our local data to Azure. Sometimes, we migrate files, sometimes databases and sometimes virtual machines. All the service in the cloud including Azure help a lot to simplify the administrative effort and reduce costs. Read more »
How to install SQL Server on Ubuntu May 18, 2017 by Stefan Veselinovic Introduction With an increasing number of new servers running on Linux, and even a third of Microsoft’s Azure virtual machines running Linux, they have made a choice to bring SQL Server to Linux in order to increase their market share and attract new customers. Read more »
Monitoring SQL Server with Dynamic Management Objects – Requests May 17, 2017 by David Alcock In my last post, Monitoring SQL Server with dynamic management objects – Sessions and connections, I introduced the concept of using T-SQL queries to monitor SQL Server using Dynamic Management Views. The article demonstrated how we can use two views, namely sys.dm_exec_sessions and sys.dm_exec_connections to view activity on our instance of SQL Server and we achieved this by utilising some of the functionality available to us within T-SQL like joins, aliases and functions, all of which helped us develop a query to return monitoring information to us. Read more »
Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol May 16, 2017 by Prashanth Jayaram In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux. Read more »
How to Spoof Production Environment Resources for Performance Tuning May 16, 2017 by Ahmad Yaseen Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems. Read more »
Discovering database specific information using built-in functions and dynamic management views (DMVs) May 15, 2017 by Gerald Britton Introduction In the last two articles on dynamic management views in SQL Server, Discovering SQL server instance information using system views and Discovering more SQL Server information using the built-in dynamic management views (DMVs), we used DMVs to discover a fair bit of information about the SQL Server instance we’re connected to. In this article, we’ll begin diving in to database specifics. There is a lot of territory to cover! We’ll also use several of the built-in functions that come with SQL Server. Read more »
Monitoring SQL Server with Dynamic Management Objects – Sessions and connections May 12, 2017 by David Alcock A fundamental task of Database Administrators is monitoring SQL Server performance. Whilst SQL Server does give us a user interface inside management studio that enables us to view current activity (in the rather aptly named Activity Monitor) this article is going to focus on querying Dynamic Management Objects with T-SQL to return various pieces of useful process information that we can use to monitor server usage. Read more »
Implementing Different Calendars in Reporting May 11, 2017 by Ed Pollack Introduction There is a common need in reporting to aggregate or return data that is crunched based on date attributes. Calendars themselves can vary and provide differing insight into a business, data, and finance. They also assist in taking the intrinsically messy Gregorian calendar and making it easier to work with. Read more »
SQL DateTime data type considerations and limitations May 11, 2017 by Jefferson Elias Introduction In this article, we will explorethe SQL Date Time data type and its limitations. Read more »
Installation of SQL Server vNext CTP on Linux Distribution CentOS 7 May 10, 2017 by Prashanth Jayaram It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time. Read more »
How to Avoid Package Design Flaws When Sourcing Data From Flat Files May 10, 2017 by Sifiso W. Ndlovu As developers of SQL Server Integrations Services (SSIS) solutions, we have more than likely configured Flat File Connections as sources in our Data Flow Tasks. Whilst any unforeseen changes to the structure and formatting of flat files will invariably cause SSIS packages to fail, as developers we can still do a lot in reducing unnecessary SSIS package failures relating to data coming out of flat files. In this article, we offer recommended development practices to some flat file source errors that occur as a result of poor SSIS development practices. Read more »
How to connect and use Microsoft SQL Server Express LocalDB May 9, 2017 by Marko Zivkovic Microsoft SQL Server Express LocalDB is intended for developers, it is very easy to install and doesn’t require any complex configuration task to create an instance or to use the database. The Introduction on the installation of Local DB be found on “How to install Microsoft SQL Server Express LocalDB” article. This article will show different ways of connecting to LocalDB and how to create and use an instance of LocalDB. Read more »
How to install Microsoft SQL Server Express LocalDB May 9, 2017 by Marko Zivkovic Microsoft SQL Server Express LocalDB, a solution primarily intended for developers, is a lightweight version of SQL Server Express. It is very easy to install and set up. The installation copies a minimum set of files which are necessary to start SQL Server Database Engine. LocalDB supports the same T-SQL language and has the same limitations as SQL Server Express. Read more »
How to set and use encrypted SQL Server connections May 8, 2017 by Daniel Tikvicki As the standard for securing the host-server interaction, Secure Sockets Layer or SSL is implemented in a Web environment. However, the SSL can provide the encrypted connection and data transfer between a particular SQL Server instance and a client application. A trusted SSL certificate validates the SQL Server instance when the client application requests encrypted connection (or vice versa), while the SQL Server must be configured to follow the certificate authority (CA). This means that a certificate must be “signed” by a trusted source. Read more »
Introduction to Nested Loop Joins in SQL Server May 8, 2017 by Neeraj Prasad Sharma A relational database system uses SQL as the language for querying and maintaining databases. To see the data of two or more tables together, we need to join the tables; the joining can be further categorized into INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN. All these types of joins that we use are actually based on the requirement of the users. Read more »
How to use Columnstore Indexes to improve your Data Warehouse Staging Environment May 4, 2017 by Sifiso W. Ndlovu My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the traditional B-tree index (rowstore). Whilst most of the existing documentation relating to columnstore seem to focus on the benefit of columnstore against data warehouse workloads, in this article I argue that the usage of columnstore index should not be limited to facts and dimensions instead let’s introduce it in our data warehouse staging environments too. Read more »
Different Azure Storage types (File, Blob, Queue and Table) May 3, 2017 by Minette Steynberg There are several advantages to using Azure storage irrespective of type. Azure storage is easily scalable, extremely flexible and relatively low in cost depending on the options you choose. Read more »
Understanding Database Backup Encryption in SQL Server May 3, 2017 by Prashanth Jayaram Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business. Read more »
Adaptive Query Processing in SQL Server 2017 April 28, 2017 by Musab Umair The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner. Read more »
Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes April 28, 2017 by Koen Verbeeck In this article, we’ll discuss some tips and best practices regarding the design of OLAP cubes in Analysis Services Multidimensional (SSAS). Most tips – if not all – are applicable for SSAS 2008 to 2016 (and later most likely). Since Analysis Services Tabular – the in-memory columnstore OLAP database from Microsoft – is a completely different design experience, it will not be included in this article. Read more »
Five ways to protect your data in Azure SQL Database April 28, 2017 by Minette Steynberg When storing data in the cloud the main concern companies generally have is whether or not their data is safe. And what can be done to ensure that the following 4 scenarios are addressed: Read more »
Security considerations for your Azure SQL Databases April 27, 2017 by Kaloyan Kosev You have to agree with me, when public clouds were introduced your thought was that you will never put your production data in there. Our idea was that your data is exposed, insecure and vulnerable. This was not the case back then, it is not case at the moment as well. Read more »
How to add a TDE encrypted user database to an Always On Availability Group April 27, 2017 by Ahmad Yaseen SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption. Read more »
