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 »
T-SQL as an asset to set-based programming approach April 27, 2017 by Jefferson Elias Introduction This article is the third and last one of a series of articles that aims to introduce set-based programming approach inside SQL Server, so using Transact SQL. In first article “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve compared procedural and set-based approaches and came to the conclusion that the second one can facilitate developer or DBA’s life. In second article “From mathematics to SQL Server, a fast introduction to set theory”, we’ve made the parallel between mathematical definition of set theory and its implementation in SQL Server. Now, we’ll discuss about some other features from T-SQL that can’t be left aside when considering sets. Read more »
What is the SQL Server Virtual Log file and how to monitor it April 26, 2017 by Musab Umair Microsoft SQL Server has many important features when it comes to file structures. Generally speaking, DBAs often ignore file structures and growth configurations. More specifically, the SQL Server Log files are often left at default values and never touched, even when they begin to cause problems caused. These Log files are important and critical part of the database, especially when it comes to large systems. Read more »
T-SQL for DBAs – Three ways of using T-SQL for quick data analysis April 26, 2017 by Timothy Smith Background As a database administrator, sometimes you need to identify details about a problem as quickly as possible and being able to build and analyze data for analysis will help you solve the problem. From getting information about the latest backups, to saving information about waits or indexes and comparing that to other captured metrics, we will run into issues where being able to get, store and analyze data are important for decisions to solve urgent problems. Read more »
SQL Server Code Review Checklist for Developers April 26, 2017 by Samir Behara In a software development life cycle, Code Review plays an integral role in improving the product quality. Having a Code Review Checklist is indispensable since it ensures that the best practices are followed and reviews are performed consistently. It is essential for developers to be aware of the coding guidelines while working on their code changes. Catching a bug early in the process is inexpensive and easier to resolve, than compared with a bug caught later in the game. Having all the common mistakes added to the checklist document is a great way to create awareness and ensure good code quality over a period of time. Read more »
From mathematics to SQL Server, a fast introduction to set theory April 25, 2017 by Jefferson Elias Introduction In the previous article of this series “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve seen from a simple example that we could find actual benefit from learning set-based approach when writing T-SQL code. In this article, we will carry on in this way by having a look at what a set is and what we can do with it in a mathematical point of view and how it’s implemented and provided to us in SQL Server. We will also have a look at more “realistic” examples using Microsoft’s AdventureWorks database. Read more »
How to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS April 25, 2017 by Daniel Calbimonte Introduction In this article, we will demonstrate how to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS. We will give a brief introduction to MySQL and create a table. If you do not have any experience in MySQL, this tutorial will teach you your first steps. After that, we will migrate the created table and existing tables. We will also mention common problems when we migrate tables. Read more »
Managing SSRS security and using PowerShell automation scripts April 24, 2017 by Craig Porteous So much has changed with Reporting Services 2016 but in terms of security it’s the same under the hood and that’s not necessarily a bad thing. SSRS has long had a robust folder & item level security model with the ability to inherit permissions from parent folders, much like SharePoint and windows in general. Read more »
Executing your own .NET console application from SSIS April 24, 2017 by Hans Michiels Introduction The SSIS Script Task is a very powerful component to use in a SSIS package, and most of the time you can achieve with it what you want. Still, I have faced a few situations where a Script Task was not the best solution. In those cases I made a .NET Console application and executed it from within a SSIS package. Read more »
An introduction to set-based vs procedural programming approaches in T-SQL April 24, 2017 by Jefferson Elias Introduction Most IT professionals started their studies or careers by learning programming languages like Basic, Cobol, C, Pascal, Java and so on. Those languages produce results using a sequence of operations or procedures. For this reason, this approach is called procedural programming. Read more »
Running SQL Server Containers on Windows Server 2016 Core April 21, 2017 by Andrew Pruski SQL Server & Containers There’s a lot of buzz around containers at the moment but not so much in the SQL Server world which I find odd as, to me as a SQL Server DBA, the technology has a lot of benefits that are worth exploring especially when it comes to development environments. Read more »
