A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.Read more »
Languages and coding
How to Import / Export CSV Files with R in SQL Server 2016February 9, 2017
Importing and exporting CSV files is a common task to DBAs from time to time.Read more »
How to create a simple SQL Server database login dialog using PowerShellJanuary 30, 2017
In this article, we’ll be creating a SQL Server login form, as shown in the example below, using PowerShell to accept user input. We’ll show the code behind the form, how to capture the user input and utilize it:Read more »
Using Variables in Dynamic SQLJanuary 26, 2017
Before we delve into these SQL concepts, note that I like to do all my development in SQL Management Studio. So if you want to follow along go ahead and open that up.Read more »
How to generate random SQL Server test data using T-SQLJanuary 26, 2017
In this article, we will talk about generating random values for testing purposes.
I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.Read more »
Connecting PowerShell to SQL Server – Using a Different AccountJanuary 24, 2017
In a previous article on Connecting PowerShell to SQL Server I went over how you use various methods in PowerShell to connect to SQL Server. In those examples though I only touched on using the current user that is running “PowerShell.exe”. In this article I want to pick up and go over how you would use the same methods, but as a different account. I will cover using both Windows Authentication (where supported) and SQL Login authentication.Read more »
How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShellJanuary 23, 2017
In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.Read more »
Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using PowershellJanuary 12, 2017
As DBAs, we all face a lot of challenges while performing the migration of SQL Server databases from one server to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects.Read more »
Native JSON Support in SQL Server 2016January 6, 2017
With the introduction of SQL Server 2016, Microsoft introduced many new features which had taken SQL Server another step forward and they made sure that it stands in front among many major Relational Database Systems.
One such feature which has been lacking but desperately required was the native support towards JSON.Read more »
Java HashCode in TSQLDecember 28, 2016
The Java HashCode method is used to determine uniqueness or similarity of strings. While implemented in Java, there can be many benefits of creating a similar or customized version of this method.Read more »
Handy features of the dbatools Read-DbaBackupHeader commandDecember 21, 2016
If you haven’t heard of dbatools, it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you can build it and contribute it to the project. Or you can ask if someone has time to write if for you.Read more »
How to use SQLCMD commands in the SSMS query editorDecember 13, 2016
SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005.Read more »
3 ways to improve T-SQL performanceNovember 22, 2016
When customers used to ask for advice to solve some T-SQL Problem, they would show me their scripts, stored procedures and cursors. I saw horrible things doing that job, some things I do not want to remember, and even some I cannot tell you about 😉 In this article, we will talk about performance problems when using while loops in T-SQL. We will also talk about problems with UNIONsand finally the use of JOINS of two or more tables using the where clause (ANSI 89) instead of using INNER, LEFT or RIGHT JOINS using the from clause (ANSI 92).Read more »
PowerShell: Get a daily database status emailNovember 22, 2016
The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.Read more »
How to create and manage T-SQL code snippetsOctober 28, 2016
Transact-SQL (T-SQL) snippets were first introduced in SQL Server 2012 Management Studio. T-SQL snippets are templates containing one or more T-SQL statements which you can easily use them when you develop T-SQL scripts. The main concept behind code snippets is code reuse. With code reuse you develop faster, easier and with less syntax errors. If you frequently use specific T-SQL statements, then you should consider creating snippets with these statements as they will help you a lot.Read more »
How to effectively extract data from an OLAP cube by relying upon T-SQLOctober 19, 2016
Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end.
In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from thisRead more »
How to import a sample bacpac file to an Azure SQL Database using sqlpackage and PowerShellOctober 18, 2016
In our first part of this series, we showed how to import a bacpac file using the Azure Portal with Microsoft Azure Storage Explorer (MASE) and SQL Server Management Studio (SSMS) to create a Database in Azure.
In the second part, we will show how to import a bacpac file to create a Database using the sqlpackage.exe tool and PowerShell.Read more »
5 handy Transact-SQL tips you may (or may not) knowSeptember 23, 2016
Even if you are a SQL veteran, actually especially if you are, you sometimes get really stuck doing something in the old way not realizing that in newer versions of SQL Server some handy functions have been added that can make your life easier.
In this article, I will mention a couple of these which appeared in SQL Server 2008 or later versions or might have even been there before but most people just didn’t know about.Read more »
How to use the xp_cmdshell extended procedureSeptember 12, 2016
The xp_cmdshell is a very powerful extended procedure used to run the command line (cmd). This is very useful to run tasks in the operative system like copying files, create folders, share folders, etc. using T-SQL.
In this new article, we will show some useful examples about how to use it.Read more »
New PowerShell Cmdlets in SQL Server 2016August 18, 2016
PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more.Read more »
SQL backup reports with PowerShellAugust 15, 2016
Let me preface this post by saying that this was a process that evolved over time. It started as a simple sql statement that emailed me a csv file. From there the actual SQL code evolved to display cleaner results. When I learned what powershell could do, I spent some time learning it during my lunch breaks at work. Even when I implemented a powershell script for the first time, it still wasn’t a clean looking report, but it was still better than what we had prior. Even now there is room for improvement but I am happy with the results and hope that it can provide some help to other SQL DBA’s out there.Read more »
Top 5 T-SQL functions introduced in SQL Server 2016August 1, 2016
One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.Read more »
Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reportsJuly 27, 2016
With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends.
In today’s “get together” we shall be expanding our outlook by creating efficient and effective reports utilizing SQL Server Reporting Service 2016 and T-SQL, together with the DAX code that we created in our last “fire side chat”.Read more »
Convert SQL Server results into JSONJuly 12, 2016
In my article, Warehousing JSON Formatted Data in SQL Server 2016, we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications may create a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web services etc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams using SQL Server 2016 FOR JSON clauseRead more »
Warehousing JSON formatted data in SQL Server 2016June 22, 2016
In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions.Read more »