To make the first steps into the BI world easier, you just need to build one SSAS Tabular model database. In this article, we will learn some basics of querying a SSAS Tabular model database with simple DAX queries, starting with a theoretical approach, and then retrieve data and analyze it.
Read more »
Top 10 security considerations for your SQL Server instances
August 31, 2016SQL Server is one of the world’s leading data platforms. It is being broadly used hosting millions of databases. These databases store data. These data are each organization’s most valuable asset. It is with this data that organizations run their everyday operations and processes. This fact makes it a necessity to efficiently secure your SQL Server instances, in order to protect your databases and consequently your data. This article suggests a list with the top 10 security considerations based on which you can efficiently secure your SQL Server instances.
Read more »Archiving SQL Server data using Partitions
August 29, 2016The Partition feature was introduced in the SQL Server 2005. This article is to cover how partitioning can be useful when it comes to archiving of SQL Server data in a database. Please note that this article does not cover how partitioning works and its configurations in detail.
Read more »
Optimize NULL values storage consumption using SQL Server Sparse Columns
August 29, 2016SQL Server 2008 introduces a new column attribute that is used to reduce the storage consumed by NULL values in the database tables. This feature is known as Sparse Columns. Sparse Columns work effectively in the case of the columns with high proportion of NULL values, as SQL Server will not consume any space storing the NULL values at all, which helps in optimizing the SQL storage usage.
Read more »Creating your first SSAS tabular model database
August 25, 2016Considering BI environment, when comparing Multidimensional Vs Tabular model databases, both of them have their own advantages and purpose in data analytics and business intelligence.
Read more »How to use SQL Server Reporting Services (SSRS) to execute SQL Agent Jobs
August 22, 2016Every production ETL (Extract, Transform, Load) solution is often intrinsically linked to a scheduling mechanism that is used to execute that ETL solution. In a SQL Server-based environment, SQL Server Agent is one of the scheduling mechanism that can be utilized to schedule an execution of ETL solutions such as SQL Server Integration Service (SSIS) packages. In the organization that I currently work for, we’ve had several instances (for various reasons) whereby as the data team we’ve been required to provide a platform for business users to execute an ETL at their own convenience (i.e. on-demand). In this article, we will demonstrate on how we went about delivering self-service ETL execution requirement.
Read more »10 things you need to know to become a Data Scientist
August 22, 2016Introduction
If you have been browsing job ads lately, you would have noticed a huge amount of positions available for Data Scientist. The demand seems to be much larger than the supply which means that there is a huge opportunity here. However, there appears to be a catch: Most of these positions requires some experience or knowledge in the field of Data Science. So if you want midway through your career, how can you skill up to become a Data Scientist?
Read more »Ready, SET, go – How does SQL Server handle recursive CTE’s
August 19, 2016First of all, a quick recap on what a recursive query is.
Recursive queries are useful when building hierarchies, traverse datasets and generate arbitrary rowsets etc. The recursive part (simply) means joining a rowset with itself an arbitrary number of times.
A recursive query is defined by an anchor set (the base rowset of the recursion) and a recursive part (the operation that should be done over the previous rowset).
Read more »Troubleshoot SQL query performance using SQL Server 2016 Live Execution Statistics
August 19, 2016SQL Server Management Studio a graphical interactive that allows you to interact with the databases hosted on your servers. SSMS provides you with the ability to write, edit, execute, analyze and monitor your SQL queries. It also helps database administrators answer important questions about the SQL query performance, such as why a query is slow or why an index is not used. The answer to these questions can be found simply by tracking the Query Execution Plan.
Read more »New PowerShell Cmdlets in SQL Server 2016
August 18, 2016Introduction
PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more.
Read more »SQL Server stretch databases – Moving your “cold” data to the Cloud
August 18, 2016Tons of new and exciting features have been introduced with the release of the latest SQL Server 2016. It simply is faster, better and more reliable! 🙂 As with the last several releases, there are again a lot of improvements with regards to the integration between on-premises databases and databases sitting into the Cloud. The ultimate goal is to have as convenient and easy as possible migration of the data in both directions. Today’s focus will be one of those features: Stretched Databases. Fasten your seatbelts and discover what is beneath this term!
Read more »Understanding the graphical representation of the SQL Server Deadlock Graph
August 16, 2016Introduction
If you are reading this I am sure you already know what a deadlock is, but just in case you are new to SQL, a deadlock is when 2 queries are blocking each other in such a way that neither of the two can be completed.
Read more »
SQL Server deadlock definition and Overview
August 16, 2016Introduction
In this series, I will provide all of the information you need to understand in order to deal with deadlocks.
Read more »Link a SQL Server to an Oracle database
August 15, 2016There are some cases in the wild where we want to get back data from an external database without additional coding in an application and just write a T-SQL query that will do all the job for us. That’s why Microsoft provides the « linked server » feature.
Read more »SQL backup reports with PowerShell
August 15, 2016Let 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 »SQL Server 2016 Trace flags modifications
August 9, 2016SQL Server Trace Flags are special switches that are used to customize and control specific behaviors of the SQL Server Engine. Trace Flags can be defined in two forms; Session Trace Flags that are activated and visible at the current connection level only, and Global Trace Flags that are enabled and visible at the SQL Server Instance level and applied to all connecting sessions in that SQL Server. Global Trace Flags should be enabled globally in order to take effect, where some Trace Flags that can be either Global or Session Trace Flags can be enabled in the appropriate scope, and its effect will appear on the defined level.
Read more »Reducing SQL Server ASYNC_NETWORK_IO wait type
August 9, 2016The ASYNC_NETWORK_IO wait type is one of those wait types that can be seen very often by DBAs, and it can be worrisome when excessive values occur, as it is one of the most difficult wait types to fix.
Read more »Working with table, blob, queues and file storage in Azure
August 9, 2016Introduction
There are several options to upload SQL Server backups files, scripts or other files to Azure. In this new article, we will show how to use a new tool, Microsoft Azure Storage Explorer (MASE).
Read more »Measuring Availability Group synchronization lag
August 9, 2016With all of the high-availability (HA) and disaster recovery (DR) features, the database administrator must understand how much data loss and downtime is possible under the worst case scenarios. Data loss affects your ability to meet recovery point objectives (RPO) and downtime affects your recovery time objectives (RTO). When using Availability Groups (AGs), your RTO and RPO rely upon the replication of transaction log records between at least two replicas to be extremely fast. The worse the performance, the more potential data loss will occur and the longer it can take for a failed over database to come back online.
Read more »Reporting in SQL Server – Combine three reports into one using SQL Server Data Tools
August 4, 2016Introduction
In our last “fireside chat” we discussed a few of the challenges that the HR Manager of a major hardware chain was experiencing. Mary Smith, the HR manager has since approached us to modify her existing reports to function more efficiently and effectively by utilizing her existing data, yet reduce the total number of reports.
Read more »Top 5 T-SQL functions introduced in SQL Server 2016
August 1, 2016One 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 »Force query execution plan using SQL Server 2016 Query store
July 29, 2016SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its performance to decide which execution plan the SQL Server Query Optimizer should use for that query.
Read more »Running with running totals in SQL Server
July 29, 2016Background
Running totals have long been the core of most financial systems, be statements or even balance calculations at a given point in time. Now it’s not the hardest thing to do in SQL Server but it is definitely not the fastest thing in the world either as each record has to be evaluated separately. Prior to SQL Server 2012, you have to manually define the window/subset in which you want to calculate you running total, normally we would define a row number with a window on a specific order or a customer depending on the requirements at hand.
Read more »Use of hierarchyid in SQL Server
July 29, 2016I attended a TDWI conference in May 2016 in Chicago. Here I got a hint about the datatype hierarchyid in SQL Server which could optimize and eliminate the good old parent/child hierarchy.
Read more »Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reports
July 27, 2016Introduction
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 »