This article will provide an overview of the SQL Join and cover all of the SQL join types including inner, self, cross and outer. For inner joins we’ll be discussing Equi and Theta joins.
Read more »
SQL Server database auditing tools
September 17, 2018This collection of content presents an overview of top SQL Server database auditing tools, with product page links for each of the tools and screenshots if available. If you noticed a tool we missed, please let us know in the comments below.
All database auditing tools are sorted by alphabetical order.
Read more »
Substring function overview
September 14, 2018The requirement of data refactoring is very common and vital in data mining operations. In the previous article SQL string functions for Data Munging (Wrangling), you’ll learn the tips for getting started with SQL string functions, including the substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.
Read more »
SQL Server Reporting Services (SSRS) Shared Dataset
September 14, 2018In the SQL Server Reporting Service (SSRS) report development process; we can use a beneficial feature which is called the “shared dataset”. The purpose of SSRS embedded datasets are to retrieve data from target data sources for only one report but a shared dataset can be used by multiple reports to retrieve data.
Read more »
How to perform a performance test against a SQL Server instance
September 14, 2018Introduction
Intended audience
This document is intended for application developers and database administrators who plan to evaluate performance for any computer hosting a SQL Server instance.
Read more »
SQL string functions for Data Munging (Wrangling)
September 13, 2018In this article, you’ll learn the tips for getting started using SQL string functions for data munging with SQL Server. In many cases, Machine learning outcomes are only as good as the data they’re built on – but the work of preparing data for analytics (that is, data wrangling) can eat up as much as 80% of your project efforts.
Read more »
SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup
September 13, 2018A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.
Read more »
Querying data using the SQL Case statement
September 12, 2018The Case statement in SQL is mostly used in a case with equality expressions. The SQL Case statement is usually inside of a Select list to alter the output. What it does is evaluates a list of conditions and returns one of the multiple possible result expressions.
Read more »
SQL Server Execution Plan Operators – Part 1
September 12, 2018In the previous articles of this series, SQL Server Execution Plans overview , SQL Server Execution Plans types and How to Analyze SQL Execution Plan Graphical Components, we discussed the steps that are performed by the SQL Server Relational Engine to generate the Execution Plan of a submitted query and the steps performed by the SQL Server Storage Engine to retrieve the requested data or perform the requested modification operation.
Read more »
SQL Replication: Basic setup and configuration
September 12, 2018This is article is a continuation of the previous: SQL Server replication: Overview of components and topography.
By now, you’re familiar with the components of replication. So far, we’ve seen a lot of theory about replication. It’s a time for practical walkthrough of setting up a basic transactional SQL Replication system. The best way to get a feel for how SQL Replication is implemented and how it works is to see it in action.
Read more »
How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases
September 12, 2018In this data-driven era, replication is often a critical requirement for achieving a modern, agile database management environment. It is believed designing an enterprise-grade dataset is the to achieving this requirement but building datamarts from datasets always presents certain challenges
In this article, we’ll discuss what it takes to setup “central subscriber with multiple publishers” replication model, to create an aggregate dataset from multiple sources, and you’ll also see how to scale with the data.
Read more »
SQL Server replication: Overview of components and topography
September 11, 2018The volume of data retained, managed, and accessed today is unprecedented. Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. To meet today’s needs; we need to replace outdated and inefficient legacy processes with new, more agile techniques. SQL Server Replication is one of the techniques to accommodate such demands.
Read more »
How to create a Mobile Dashboard with SQL Server Microsoft Mobile Publisher?
September 10, 2018Over the past three years, I read some interesting news about Microsoft which was “Microsoft acquires mobile business intelligence leader Datazen”. This news made me excited because showing Reporting Service reports in mobile devices can be painful and often not effective for end users. After this acquisition, Microsoft developers worked quickly to adapt the Datazen application to SQL Server Reporting Services and this effort culminated in Microsoft SQL Server Mobile Report Publisher.
Read more »
How to Analyze SQL Execution Plan Graphical Components
September 7, 2018In the previous articles of this series, SQL Server Execution Plans overview and SQL Server Execution Plans types we went through the different stages that the submitted SQL Server query followed and how it processed by the SQL Server Relational Engine that generates the Execution Plan and the SQL Server Storage Engine that performs the requested data retrieval or modification operation. In addition, we described deeply the different types and formats of the SQL Server Execution Plans that can be used for query performance troubleshooting purposes. In this article, we will discuss the graphical query plan components and how to analyze them.
Read more »
How to create a SQL dependency diagram in SQL Server
September 4, 2018Deleting or changing objects may affect other database objects like views or procedures that depends on them and in certain instances, can “break” the depending object. An example can be that if a View queries a table and the name of that table changes. The View will no longer function.
Read more »
Deploy SQL Server for failover clustering with Cluster Shared Volumes – part 1
September 4, 2018Microsoft SQL Server provides us with a wide variety of solutions to architect High availability (HA) and Disaster Recovery (DR) solutions for mission-critical workloads. In this article, let’s just focus on HA, specifically Failover Clustering. Failover clustering is probably the most mature, robust and stable high availability solution which Windows Server Operating system offers. It’s been there around for few decades now and did evolve over time along with SQL Server. In this article Let’s see a hidden feature of windows server failover cluster which helps in making our already highly available SQL Server Failover clustered instances even more highly available. The new feature which we are going to talk about is Cluster Shared Volumes, AKA CSVs. Considering windows server 2019 is around the corner, I say CSVs are not a new concept in clustering, it’s been there for almost a decade now. Microsoft introduced CSVs in windows server 2008R2, but at that time SQL Server was not supported on CSVs. Well, CSVs were originally designed for Hyper-V workloads and later on enhanced for File servers and eventually landed into SQL Server beginning version 2014.
Read more »
SQL Query Optimization Techniques in SQL Server: Parameter Sniffing
September 4, 2018Description
Of the many ways in which query performance can go awry, few are as misunderstood as parameter sniffing. Search the internet for solutions to a plan reuse problem, and many suggestions will be misleading, incomplete, or just plain wrong.
Read more »
How to setup cross-platform transactional SQL Server replication for database reporting on Linux
August 31, 2018In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.
Read more »
Top 50 PowerShell bloggers of 2018
August 31, 2018We made a collection of the most popular PowerShell bloggers, including a link to each individual blog. The ranking is based on Alexa global score.
If your blog is on this list, you can display the ‘Top blogger’ badge on your blog. Please see the bottom of the page for the instructions on how to display the badge on your website
Read more »
Considerations and concerns when setting up Database mail for SQL Server
August 31, 2018Database mail is widely used by DBAs and companies around the world and is one of the features of SQL Server that can be very important for startups. That is because it is a cheap solution for getting alerts from your SQL Server for potential hardware issues, early warning signs of corruption, along with potential resource constraints. However, it can be misused or pose potential security issues. Before we start, to my myself clear I am in favor of configuring Database mail for the DBA team when done properly. In the rest of the article, we will touch on some of the common mistakes people make with database mail and their SQL Servers. We will not discuss the setup of this feature as it was well documented by Bojan Petrovic on SQLShack.
Read more »
How to integrate Power BI data alerts into Slack
August 31, 2018There is a growing trend among professionals and entire companies to move away from email as a primary means of communication and are even adopting alternative technologies. One of the most popular of these is Slack. Slack is a new kind of messaging and communication platform between colleagues, team or community members that allows them to integrate a lot of services including Visual Studio Team Service, Jira and GitHub.
Read more »
SQL Server Setup – Instant File Initialization (IFI)
August 31, 2018If SQL Server needs to allocate space, first it fills the space it needs with zeros. Examples of when it needs to allocate space are creating/restoring a database, growing/allocating data and log files, and finally the troublesome auto growth operation. However, most of these slowdowns/long running actions can be improved by enabling instant file initialization. I say most because the one case where this is required is on log file growth. However, even when Instant file initialization has been enabled the log file will be zeroed out before the space is usable for SQL Server.
Read more »
SQL Server Dedicated Admin Connection (DAC) – how to enable, connect and use
August 31, 2018The dedicated admin connection (DAC) can help you out of a sticky situation. This was built to help you connect to SQL Server and run basic queries in cases with critical performance problems. This works by telling SQL Server to reserve a thread specifically for processing your queries in an emergency. While it does reserve a connection for you, it is only one thread, there is no parallelism happening here, in fact, you will receive an error.
Read more »
SQL Server Setup – Database Alerts
August 31, 2018Why should you enable alerts
While the article “How to create and configure SQL Server Agent Alerts” created by Minette Steynberg a SQLShack author discusses the features around Alerting through SQL Agent and some conditions for testing. It does not go through some of the common alerts that you should have enabled on your SQL Server.
Read more »
How to do a quick estimated compare of data in two large SQL Server databases to see if they are equal
August 29, 2018Bringing impactful analysis into a data always comes with challenges. In many cases, we rely on automated tools and techniques to overcome many of these challenges.
Read more »