Discovering more SQL Server information using the built-in dynamic management views (DMVs) April 6, 2017 by Gerald Britton Introduction This is the second article in a continuing series on the many system tables, views, procedures and functions available in SQL Server. In the first part of this series, Discovering SQL server instance information using system views, you learned how to discover many attributes of a SQL Server instance you have been given access to. In this part, we will continue the journey and see what else we can find. Read more »
How to create geographic maps using Power BI – Filled and bubble maps April 5, 2017 by Andrea Martorana Tusa The project This is the first article of a series dedicated to discovering geographic maps in Power BI. My goal is to illustrate to you the different visuals available in Power BI for making maps. Remember that Power BI is a product undergoing constant evolution; new features are added every month at a fast pace. And over the time Power BI has gained more mapping capabilities compared to the starting point. Read more »
SQL Server Data Warehouse design best practice for Analysis Services (SSAS) April 4, 2017 by Thomas LeBlanc Before jumping into creating a cube or tabular model in Analysis Service, the database used as source data should be well structured using best practices for data modeling. Some might say use Dimensional Modeling or Inmon’s data warehouse concepts while others say go with the future, Data Vault. No matter what conceptual path is taken, the tables can be well structured with the proper data types, sizes and constraints. Read more »
How to use Microsoft Assessment and Planning (MAP) Toolkit for SQL Server March 31, 2017 by Musab Umair As a Microsoft SQL Server Database Consultant, I have to visit multiple companies with variety of SQL Server versions and patch level. Generally speaking, we need to analyze the environment of these clients and get information about the infrastructure. To do so, we have to run multiple scripts and tools on almost all the machines at client. Though it’s a part of our job but at times it takes hours to get even the basic information about the SQL Server versions, patch level, configuration and small details which even client does not have. Read more »
Concept and basics of DBCC Commands in SQL Server March 31, 2017 by Mustafa EL-Masry Many DBA’s and database developers aren’t very familiar with DBCC commands (aka Database Console Commands) because they don’t always have the chance to work with them. In this article, we’ll provide a simple primer on DBCC commands with specific examples Read more »
How to discover and handle orphaned database users in SQL Server March 30, 2017 by Jefferson Elias Introduction Context As SQL Server database administrators, we should all know that, most of the time, a database user is linked to a SQL Server login. We do this to tell SQL Server that a SQL login LoginA has access to database Db1 using the context and permissions of UserA database user. Read more »
Understanding the XML description of the Deadlock Graph in SQL Server March 29, 2017 by Minette Steynberg Introduction In my previous 2 articles, What is a SQL Server Deadlock and Understanding the graphical representation of the SQL Server Deadlock Graph, I discussed what a deadlock is, how SQL Server handles deadlocks, some tips on how to reduce deadlocks and ultimately what information you can glean by just looking at the graphical representation of the Deadlock Graph. In this article, I will look at what information is available in the XML description of the Deadlock Graph. The XML contains information which is not visible in the graphical representation of the Deadlock Graph, which makes it necessary for us to look at the XML description. Read more »
What’s new in SQL Server 2016 Reporting Services (SSRS) March 29, 2017 by Mohamed Osman Microsoft released many new features in Reporting Services with the SQL 2016 release.: New Report Portal Paginated Report Enhancements Mobile Report Publisher Read more »
How to optimize the dimension security performance using partitioning in SSAS Multidimensional March 27, 2017 by Koen Verbeeck Introduction In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query are read instead of all the data. Read more »
Custom keyboard shortcuts in SQL Server Management Studio (SSMS) March 27, 2017 by Prashanth Jayaram People love taking shortcuts because it’s the easiest way to do things faster; computers are no exception. Shortcuts, particularly ones performed by keyboard, can save you hours of time once applied properly. Learning keyboard shortcut saves you a lot of time; you’ll definitely notice a boost the productivity because you’re not unnecessarily reaching for a mouse all the time. Read more »
Implementing and Using Calendar Tables March 24, 2017 by Ed Pollack Introduction There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes. In my previous article, you could learn about designing of a calendar table. Read more »
How to use Package Parts to develop easily maintainable Data Warehouse solutions March 24, 2017 by Sifiso W. Ndlovu Whilst discussing the evolution of SQL Server Data Tools in my previous article, I touched on a new feature, Package Parts, which is available in SSDT 2015. This article takes an in-depth analysis of the Package Parts feature, particularly, as it relates to Data Warehouse development. Read more »
How to Configure Read-Only Routing for an Availability Group in SQL Server 2016 March 23, 2017 by Ahmad Yaseen The SQL Server Always On Availability Groups concept was introduced the first time in SQL Server 2012 as an enterprise-level high availability and disaster recovery solution that will replace the database mirroring feature. Always On Availability Group provides a high availability solution on the groups level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas. Read more »
Illustration of an example availability monitoring service Using PowerShell and SQL March 23, 2017 by Prashanth Jayaram This article discusses a simple solution of how to monitor SQL service availability across multiple servers and reporting. To build this I’ll use SQL Server with simple PowerShell script and cmdlets which generate JSON data and displays results in HTML Read more »
How to mimic a wildcard search on Always Encrypted columns with Entity Framework March 22, 2017 by Hans Michiels Introduction The title of this post should have been “How to implement wildcard search functionality with Always Encrypted, make deterministic encryption safer, and load initial data using SqlBulkInsert”, but as you understand, that’s just too long for a title. Read more »
Designing a Calendar Table March 22, 2017 by Ed Pollack Introduction There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes. Read more »
How to execute a Deployed Package from the SSIS Catalog with various options March 21, 2017 by Thomas LeBlanc In my previous two articles on SQL Server integration Services (SSIS), Parameterizing Database Connection in SSIS and Deploying Packages to SSIS Catalog (SSISDB), packages were developed, deployed and configured in the SSIS Catalog. Now, it is time to execute the packages with various options. There are a couple of ways to do this, but we need to be able to change the parameter values as well as monitor for failures or successes. Read more »
CHECKSUM page verification in SQL Server March 21, 2017 by David Alcock CHECKSUM is an option for page verification that is available at the database instance level and we can see what level of verification each of our databases are currently using by the following query: Read more »
Availability Groups WITHOUT an Active Directory Domain in Google Cloud Platform March 20, 2017 by Kaloyan Kosev Starting with SQL Server 2016 and Windows Server 2016 there have been numerous cloud related enhancements, most of them are tightly related to Microsoft’s Azure like the ‘Cloud Witness’ but there are others that can be implemented within other public or private clouds. Read more »
Some uncommon but useful T-SQL and Database Engine Enhancements in SQL Server 2016 March 20, 2017 by Musab Umair SQL Server 2016 is the most advanced version of Microsoft’s Data Platform released yet. This is obviously my favorite one as it has tremendous capabilities and enormous features. These new additions not only enhance the productivity of its users (Database Developer, DBA or Application Developer), but also enable the enterprise to use its data more effectively and efficiently. Read more »