CLOUD SQL 

Create an Extended Date Dimension for a SQL Server Data Warehouse

By: Dinesh Asanka   |   Read Comments (2)   |   Related Tips: More > Dates Problem The Date Dimension is a key dimension in a SQL Server data warehousing as it allows us to analyze data in different aspects of date. Apart from the standard date attributes like year, quarter, month, etc., this article explains how the date dimension can be extended to richer analysis in a SQL Server data warehouse. Solution A date dimension is mostly…

Read More
CLOUD SQL 

How to setup and use a SQL Server Stretch Database

By: Douglas Correa   |   Read Comments   |   Related Tips: More > Azure Problem You need to keep all of your data online in the event that you need to access the data. But some of this data is warm data (needed frequently) and other is cold data (needed occasionally). In this tip we look at how we can achieve this using the new stretch database feature in SQL Server and Azure. Solution SQL Server 2016 introduced…

Read More
CLOUD SQL 

Importing Data from AWS DynamoDB into SQL Server 2017

By: Derek Colley   |   Read Comments   |   Related Tips: More > Amazon AWS Problem You have been tasked with setting up an automatic method to import data from an AWS (Amazon) DynamoDB database, which is a NoSQL data store, into SQL Server.  There are no direct connectors available nor is DynamoDB directly supported in most ETL tooling. Solution In this tip we present a solution to import data directly from DynamoDB within SQL Server 2017…

Read More
CLOUD SQL 

Steps to install a stand-alone SQL Server 2017 instance

By: Percy Reyes   |   Read Comments   |   Related Tips: More > SQL Server 2017 Problem Undoubtedly, many of us have the task of installing a new stand-alone SQL Server instance which includes just the database engine service. For instance, it can primarily be needed for dedicated and consolidated OLTP environments and we may be asked to create a formal document for others to easily follow for future installations and standard configurations. Solution Today’s post is…

Read More
CLOUD SQL 

Making a more reliable and flexible sp_MSforeachdb

By: Aaron Bertrand   |   Read Comments (42)   |   Related Tips: More > Scripts Problem While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the…

Read More
CLOUD SQL 

Use Caution with SQL Server's MERGE Statement

Gordon, I still wouldn’t use it. I haven’t done thorough testing of any of these bugs, and I have no idea which one(s) might apply in your scenario. Even if I did perform testing against all of them, and didn’t find any bugs that affected my scenario, a very subtle difference could make the bugs appear in yours. The bugs exist, some of them closed as “by design” or “won’t fix,” and some of them “incorrect results” bugs; ruling them out in a limited test doesn’t make them…

Read More
CLOUD SQL 

Auditing Windows Groups from SQL Server

      Amit, Thank you for this tip.  When I have run this code on SQL Server 2008 R2 instances, I get this error message: Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42Could not obtain information about Windows NT group/user ‘NT SERVICEMSSQLSERVER’, error code 0x8ac. Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42Could not obtain information about Windows NT group/user ‘NT SERVICESQLSERVERAGENT’, error code 0x8ac. As a quick fix, I have changed the SELECT statement in…

Read More
CLOUD SQL 

SQL Server Reference Data Best Practices – Part 2

By: Haroon Ashraf   |   Read Comments   |   Related Tips: 1 | 2 | More > Source Control Problem Reference data is added to the SQL Server database by multiple developers and testers, thereby causing inconsistencies in the database due to reference data updated by one developer getting overwritten by another developer/tester and so on. Solution To overcome this issue, adopt reference data best practices by using Merge scripts which not only avoids such conflicts, but also…

Read More
CLOUD SQL 

SQL Server Script to Search Through All Error Logs

By: Eli Leiba   |   Read Comments   |   Related Tips: More > Error Logs Problem The requirement is to create a simple SQL Server log report that will display all relevant log rows from all log files in SQL Server. These stored procedures xp_readerrorlog and sp_readerrorlog allows searching the SQL Server error logs, but only one log can be searched at a time using these procedures. The procedure in this tip searches all the available logs, by looping…

Read More
CLOUD SQL 

SQL Server Reporting Services Caching and Snapshots

By: Scott Murray   |   Read Comments   |   Related Tips: > Reporting Services Administration Problem SQL Server Reporting Services offers several options for maintaining past executions of reports. Both the cache and snapshot history options were established in many prior versions and have only been made better with time.  Surprisingly these options are somewhat unknown and used only sporadically and are often misunderstood. The two different methods also cause further confusion as some of the wording and …

Read More