Introduction
In the Azure Portal, you will find the option to install Azure SQL Cosmos DB. So, what is Cosmos DB, exactly?
Read more »
New Excel slicer feature for report users in Office 2016
May 9, 2016Whilst researching for the article Report filtering: Excel slicers vs SQL Server Reporting Services (SSRS) parameters, I discovered a new Excel Slicer feature in Microsoft Office 2016 that allows users to select/deselect multiple items without having to hold down the control (Ctrl) keyboard key. For some people, this may not be a sufficient reason to upgrade to Office 2016 but for developers of business intelligence (BI) solutions, this new feature further enhances the experience of consumers of BI solutions.
Read more »How to build better alerting
May 4, 2016Background
One of the most popular complaints from developers to DBAs involves alerting, whether from third party tools or alerting built by other developers or DBAs in the environment. Building or using alerts for important applications, data layers, or processes within a SQL Server environment offer everyone benefits, but can become noisy if they’re architected poorly, or the purpose isn’t considered. In this article, we look at considerations for building effective alerts that tell us when something is wrong without creating situations where we learn to disregard them. We want to make sure that we respond when we need to, and not always be on high alert when there is no issue.
Read more »Report filtering: Excel slicer vs SQL Server Reporting Services (SSRS) parameters
May 4, 2016This article explores the report filtering using Excel Slicer and SQL Server Reporting Services.
Read more »
SQL Server 2014 Columnstore index
April 29, 2016By default, SQL Server stores data logically in the tables as rows and columns, which appear in the result grid while retrieving data from any table and physically in the disk in the row-store format inside the data pages. A new data store mechanism introduced in SQL Server 2012, based on xVelocity in-memory technology, in which the data is stored in the column-store data format. This data store mechanism called the Columnstore index.
Read more »
How to copy tables from one database to another in SQL Server
April 29, 2016In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.
SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process. In order to go through each one of these methods, we will consider the below scenario:
- The hosting SQL Server: localhost.
- Both databases hosted in the same SQL Server 2017 instance
- The source database: AdventureWorks2018
- The destination database: SQLShackDemo
SQL snippets in SQL Server Management Studio
April 22, 2016Snippets are a great productivity feature to speed up typing of repetitive T-SQL. Snippets were introduced in SQL Server 2012 as part of SQL Server Management Studio (SSMS).
Snippets are XML templates with predefined fields and values that render into T-SQL, when processed by the query window.
Read more »Meaning of the 5127 database snapshot error code
April 15, 2016Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.
Read more »The new elastic databases in Azure
April 12, 2016Introduction
The elastic database is a new feature offered for SQL Azure Databases. Elastic databases are useful when you have several databases and you want to monitor and manage all the databases at the same time. To do this, a pool is required. The pool allows handling several databases at the same time.
In this chapter, we will learn how to create the pool and add SQL Azure Databases to it.
Read more »SQL database continuous integration with Team City
April 12, 2016Team City is a Java based continuous integration and deployment server shipped as commercial software but free to use for up to 20 build configurations. It polls the source control repository for any changes providing build reports and statistics to the team.
Read more »SSIS and PowerShell – Execute process task
April 11, 2016Introduction
SQL Server Integration Services (SSIS) and PowerShell (PS) together offer a plethora of opportunities, and some shortcuts when having to import, export, or at times moving data. I have come across packages that contain a Script Task with lines and lines of C# code that, done with PowerShell, could make maintaining that package much easier. Overall, the most common thing I see Script Task doing is accessing the file system or doing some manipulation on a file. One thing I hope that picks up speed in the BI world of SSIS is utilizing PowerShell for these type of actions. This is not to say one is better than the other as you should pick what is best in your eyes, but when I can do operations against the file system with a one-liner in PowerShell it is just easier to maintain that in the package. In this article I will go over how you can use the most common task utilized for executing PowerShell code in an SSIS package: Execute Process Task.
Read more »Create Azure Data Warehouse reports using Azure Power BI
April 11, 2016Introduction
Power BI is an extremely powerful tool to create dashboards and reports. In the last article, we learned how to create a Data Warehouse in Azure. In this new chapter, we will learn how to create reports from the Data Warehouse in Azure using Power BI in Azure.
Read more »How to take advantage of the SQL Server’s transaction log?
April 11, 2016Introduction
SQL Server keeps track of all database modifications and every database transaction. This is done in a file called the transaction log or TLOG. This transaction log is particular to a SQL Server database and there is, at least, one transaction log per SQL Server database.
As explained on MSDN, the transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.
Read more »The benefits, costs, and documentation of database constraints
April 7, 2016Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them?
Read more »Perform volume maintenance tasks security policy
April 4, 2016Introduction
You may see it more commonly referenced as Database Instance File Initialization (DIFI). If you are not familiar with the file initialization, this is the process SQL Server has to go through when it is creating the data files for a given database, and also during an expansion event (either manually or from auto growth) for a database. It only pertains to the data file(s) of the database, as log files are not affected by this security policy. SQL Server will “zero out” the file, basically fill it up with a bunch of zeros to allocate the amount of space requested. If you are a new DBA, this configuration actually goes all the way back to SQL Server 2005.
Read more »TFS tools for managing SQL Server development
March 29, 2016In today’s world of DevOps and ALM practices, there is often a strong emphasis on the visibility and traceability of the work we perform towards software development. This means that as developers, we often have to account (to clients, business owners, project teams etc.) for a list of development tasks that will be undertaken to deliver a given project requirement or user story. Amidst several tools that can be used to facilitate such a practice, Team Foundation Server (TFS) is one such tool that can be used by developers to keep track of their work items.
Read more »How to install and use Microsoft Baseline Security Analyzer (MBSA)
March 25, 2016Introduction
Microsoft Baseline Security Analyzer is one of the tools provided by Microsoft to help administrators to scan systems (local and remote) for missing security updates and common security misconfigurations. It can scan the server operating system and SQL Server but also other products as well, such as Microsoft web server IIS.
Read more »Using SQL Server cursors – Advantages and disadvantages
March 23, 2016Intro
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.
Read more »
SQL Server indexed views
March 17, 2016SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.
Read more »Managing untrusted foreign keys
March 17, 2016Introduction
Intended audience
This article is intended for application developers and database administrators who plan to develop, deploy, and/or assess solutions for Microsoft SQL Server on a Microsoft Windows platform.
Read more »Working with ragged right formatted files in SSIS
March 16, 2016In the world of SSIS development architecture, preference should be given to extracting data from flat files instead of non-Microsoft relational databases. This is because you often don’t have to worry about driver support and compatibility issues in your SSIS development/server machine that is often attributed to non-Microsoft database vendors. In fact, I’ve been in several situations whereby we cannot upgrade to another version of SSIS (i.e. BIDS to SSDT) due to the lack of external vendor driver compatibility issues in the newer versions of SSIS.
Read more »Compare and synchronize images between two SQL Server databases
March 11, 2016Introduction
Sometimes we lose data by mistake and we want to compare the data with older backups and synchronize the data.
There are several ways to do this. For example, you can create T-SQL scripts with some outer joins. Another option is to use the tablediff command line.
Read more »Searching SQL Server made easy – Building the perfect search script
March 9, 2016The need to search through database schema for specific words or phrases is commonplace for any DBA. The ability to do so quickly, accurately, and completely is critical when developing new features, modifying existing code, or cleaning up the vestiges from an application’s ancient history.
Read more »Searching SQL Server made easy – Searching catalog views
March 9, 2016The need to search through database schema for specific words or phrases is commonplace for any DBA. The ability to do so quickly, accurately, and completely is critical when developing new features, modifying existing code, or cleaning up the vestiges from an application’s ancient history.
Read more »The new SQL Data warehouse in Azure
March 8, 2016Introduction
In previous chapters, we taught how to create SQL Databases in Azure. In this new chapter, we will show you the new SQL Data Warehouse Service in Azure.
Read more »