This article will provide an overview covering programmatically moving databases on the Azure Portal while avoiding common problems with users and logins.
Read more »
How to create geographic maps in Power BI using custom shape maps
June 22, 2017Introduction
This is the third article of a series dedicated to discovering geographic maps in Power BI.
In the ToC below the article you can find out references to the previous articles and the project’s goal.
Read more »
Analysis Services (SSAS) Multidimensional Design Tips – Relations and Hierarchies
June 22, 2017We have already discussed quite some design tips for building Analysis Services (SSAS) Multidimensional cubes and dimensions:
Read more »
How to create geographic maps in Power BI using built-in shape maps
June 21, 2017Introduction
This is the second article of a series dedicated to discovering geographic map tools in Power BI.
Read more »
SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation
June 21, 2017The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.
Read more »
How to use Python in SQL Server 2017 to obtain advanced data analytics
June 20, 2017On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.
Read more »
How to create a chatbot with Azure in 5 minutes
June 19, 2017Introduction
As we enter an era of artificial intelligence and serverless computing, the tools to help us create perceived intelligent programs have become so advanced and intuitive that the effort in building a fully functional Q and A chat bot has become almost negligible.
In this article, I will show you how to do just that.
Read more »
Understanding different SQL Server database states
June 16, 2017Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.
Read more »
All about locking in SQL Server
June 16, 2017Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as an in-memory structure is 96 bytes in size.
Read more »
SQL Server Index Performance Tuning Using Built-in Index Utilization Metrics
June 15, 2017Description
Indexing is key to efficient query execution. Knowing what indexes are unneeded, incorrectly used, or unused can allow us to reduce disk usage and improve write performance at little cost to your organization.
Read more »
How to document SQL Server database objects
June 14, 2017Introduction
In any good programming reference, you will read that a developer has to document his code, not only for him/herself but also for the person who, ten years later will be asked to maintain it. This would, of course, be made easier thanks to a good documentation of existing code.
Read more »
SQL Server stress testing using OStress
June 13, 2017What is SQL Server Stress Testing?
Implementing a new database consists of various aspects like creating indexes, tuning stored procedures or installing new RAM etc. But among all of these items, performance stress testing plays a prominent role.
Read more »
Reporting in SQL Server – Power BI Report Server
June 13, 2017Power BI is a self-service business intelligence tool from Microsoft which has been steadily gaining momentum in the last couple of months. One of the well-known disadvantages of Power BI is that it is basically cloud only. A lot of companies are not yet at the point where they feel comfortable having their data in the cloud or are premise bound for some other reasons such as data-sensitivity, data-sovereignty or compliance.
Read more »
How to track SQL Server database space usage with built-in functions and DMVs
June 12, 2017Introduction
In the previous article in this series on dynamic management views in SQL Server, we used DMV’s to discover some basic information about databases on an instance you are connected to, along with some ideas about getting basic file system and security info. I promised to dive into security next but first I’d like to explore a topic that is currently quite critical for the company I work for today: tracking space usage.
Read more »
How to monitor total SQL Server indexes size
June 12, 2017SQL Server indexes overview
Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.
Read more »
How to use Window functions in SQL Server
June 9, 2017All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a set of rows and return a single aggregated value for each row.
Read more »
How to create and configure a linked server in SQL Server Management Studio
June 9, 2017Linked servers allow submitting a T-SQL statement on a SQL Server instance, which returns data from other SQL Server instances. A linked server allows joining data from several SQL Server instances using a single T-SQL statement when data exists on multiple databases on different SQL instances. By using a linked server to retrieve data from several SQL instances, the only thing that should be done is to connect to one SQL instance.
Read more »
Top SQL string functions in SQL Server 2017
June 8, 2017SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.
Read more »
How to report on SQL Server deadlock occurrences
June 8, 2017Introduction
In the previous article entitled “What are SQL Server deadlocks and how to monitor them“, we’ve seen how a deadlock can occur and which tools are available to DBA or developer to get simple or extended information about the conditions that lead a particular deadlock situation to occur.
Read more »
Considerations for On-premises data in PowerBI
June 7, 2017Whether you’ve been using PowerBI for a while or you’re just getting started with it, chances are you’ll want to source at least some of your data from on-premises systems. Be that SQL server databases, Oracle, SSAS etc. The same process even applies if you’re sourcing from AWS which I’ll go into in more detail below. All of these data sources will require Microsoft’s On-Premises Data Gateway.
Read more »
Understanding the Impact of NOLOCK and WITH NOLOCK Table Hints in SQL Server
June 6, 2017Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.
Read more »
SQL Server Database Architecture and Audits for Enhanced Security
June 5, 2017This year, digital security has risen as a top concern for many people, especially after the recent security compromises with Cloudflare in February and ransomware in May. Throughout the last two years, we’ve also seen the rise of sim-swapping where a hacker is able to extract a user’s cell phone data and compromise all emails and two-factor authentication. Unfortunately, I expect these security issues to continue and cause compromises for user’s data while negatively impacting companies involved.
Read more »
Initial Attributes and Measures in SSAS Multidimensional Cubes
June 2, 2017Connecting to tables in SQL Server Analysis Service (SSAS) databases is easy. Maybe too easy. The learning curve for developing in a Multidimensional Cube is steep, although a Tabular Model has lessened this curve. The power of encapsulating the business logic of aggregations and analytics into an Analysis Service database is too great not to embrace Analysis Services in an enterprise environment.
Read more »
Compression and decompression functions in SQL Server 2016
June 2, 2017The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.
Read more »
Analysis Services (SSAS) Multidimensional Design Tips – Creating Dimensions
June 1, 2017In the previous article, Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes, we discussed best practices for SSAS Multidimensional cubes and data source views. In this article, we continue the series with design tips for creating dimensions as the subject. As with the previous article, most tips are suited for SSAS 2008 (most likely 2005 as well) to 2016 and later versions. Analysis Services Tabular is not covered in this article.
Read more »