How to enhance your reports with SQL Server Reporting Services (SSRS) November 18, 2014 by Steve Simon Introduction A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports. Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort. The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!! Read more »
Share your data!… Loading SQL data from SharePoint November 12, 2014 by Steve Simon Introduction In the process of doing the round of PASS SQL Saturday’s, I have often been asked how to load data into SQL Server from SharePoint. Naturally and at first glance it should be a ‘no brainer’ however the gotcha is normally related to a security issue. Over the past few days, I have been working on just such an issue. But wait, I am getting ahead of myself! Read more »
Slipstreaming SQL Server 2012 and 2014 November 12, 2014 by Boris Hristov Let’s say that you care about your SQL Server environment, but let’s also say that you care about your time too. You are actually one of those people who always strive to do more with less. Now let’s say that you frequently have to install new SQL Server instances or probably you are in the middle of a project in which you have to provide the .iso file of your SQL installation to the System Center guys for them to deploy VMs with SQL Server pre-installed on them. However, because you don’t want to first install the RTM version of the product and then manually patch the instance with the Service Packs, Cumulative Updates or Hotfixes that you think are needed, you are searching for a solution to somehow “embed” all of those fixes into the installation itself! This way, once the instance is installed, it actually has everything already applied. How can you do this and is it possible at all? Read more »
Automating database tests with Visual Studio and Team Foundation Server October 31, 2014 by Régis Baccaro This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server. Post 1: Continuous Integration with SSDT and TFS Post 2: Unit testing with SQL Server Data Tools You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user Read more »
SQL Server Database Snapshots October 30, 2014 by Daniel Calbimonte Introduction MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods. The Database Snapshot can be created multiple times and it can only be created using the T-SQL. In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot. Read more »
Index Strategies – Part 2 – Memory Optimized Tables and Columnstore Indexes October 30, 2014 by Marcin Szeliga In the first part we started discussion about choosing the right table structure and described the heaps — in my opinion a mostly overused option. Now let’s see better alternatives. Read more »
How to clean data using Data Quality Services and SQL Server Integration Services October 29, 2014 by Steve Simon Introduction A year or so ago, I worked for an online web grocery software house located in the northern United States. At that time I had my ‘baptismal’ exposure to ‘genuinely dirty data’. Granted most of the data entry was done manually and many times from offshore. The point being that I could not fathom just how many ways there were to spell the brand name of a major cereal manufacturer. Why is this such an issue? The answer is fairly straight forward. Imagine the scenario that you are trying to ascertain the dollar value of breakfast cereals sold in the country from the local supermarket standpoint all the way up to national sales. Imagine this utilizing a SQL Server Multi-dimensional cube. The ‘eagle – eyed’ reader will recognize that the results will not aggregate correctly should our aggregation attributes have a plethora of different ways of being spelt. Read more »
Backup testing with PowerShell – Part 2: Reporting results October 22, 2014 by Derik Hammer Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which would cause a failure. Next CheckTables was executed to thoroughly inspect for data corruption and any consistency errors. Each of these two actions will have their own methods of providing evidence of successful completion. Recall the requirements set by Karla, in part 1 of this series, for the reporting piece. Read more »
Backup testing with PowerShell – Part 1: The test October 21, 2014 by Derik Hammer Karla is a production database administrator and she has a lot in common with you. Being responsible for database backups and recovery, she has implemented a well-structured automated backup system. Maybe she’s using Ola Hallengren’s Maintenance Solution, custom T-SQL stored procedures, or a set of PowerShell scripts. She has heard the saying, “a DBA only needs one thing, either a backup or a resume, but never both,” and is confident that she won’t be dusting off the resume any time soon. Read more »
Unit testing with SQL Server Data Tools October 16, 2014 by Régis Baccaro This is the second part on the serie about SSDT and database development Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server Introduction In December 2012, a great addition was made to SSDT: The ability to do database unit testing. Read more »
Quick and dirty server monitoring with SQL Server Reporting Services October 8, 2014 by Steve Simon Introduction A few month back, I found myself in a position where the client wanted a ‘monitoring tool’ to utilize on a daily basis to ascertain the status of SQL Server Systems and to continually monitor disk space capacity. Being a typical Monday morning quarter back and utilizing my favorite SQL Server Tool, SQL Server Reporting Services, I came up with the following ‘ah-ha’ solution. Read more »
Index Strategies – Part 1 – Choosing the right table structure October 6, 2014 by Marcin Szeliga Introduction Among many different things that can affect SQL Server performance, some are more significant than others. In addition, some changes can be relatively easy to implement, but others are quite painfully: Read more »
Continuous integration with SQL Server Data Tools and Team Foundation Server October 1, 2014 by Régis Baccaro SQL Server Data Tools – a free standalone download or and add-in to visual studio comes in different flavors and versions. Although this blog post uses Visual Studio 2012 and SSDT stand-alone the principles are example are also valid on Visual Studio 2013. Read more »
Intro to Auditing in SQL Server September 30, 2014 by Kenneth Fisher In the world of information, auditing serves an important purpose. It helps to provide an assurance that the data involved is accurate and safe. The level of assurance of course depends on any number of factors including the level of trust in those performing an audit (or collecting the data for the audit), the frequency the data is collected, and the types of data collected. Any time the collection of data is this important to a process you can bet that a DBA is going to be involved. We get asked to create and run queries to pull data (frequently ridiculously complex queries, and rarely some simple ones). You could almost make it into a joke. Read more »
SQL Server Confidential – Part I – Crypto basics and SQL Server Cryptographic Features September 29, 2014 by Jasmin Azemovic Intro We use cryptography every day: on the internet, mobile devices, ATM machines, and in almost every aspects of our digital life. In a nutshell, cryptography is about data scrambling and hiding, depending on the implementation and user-specific needs. Read more »
Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports September 12, 2014 by Milena Petrovic In the previous part of this article, we introduced SQL Server Management Studio custom reports, showed their advantages over other native performance reporting features, and gave steps to create them. In this part, we will give some security recommendations and show how to run and modify these reports. Read more »
Using custom reports to improve performance reporting in SQL Server 2014 – the basics September 8, 2014 by Milena Petrovic SQL Server Management Studio provides three built-in performance monitoring features. These are Activity Monitor, Data collection sets, and standards reports. Read more »
Using Extended Events to review SQL Server failed logins August 5, 2014 by Ivan Stankovic A secured database environment requires an authorization process in order to provide information access to its users. The same applies to a SQL Server instance. Read more »
Performance Dashboard Reports in SQL Server 2014 July 29, 2014 by Milena Petrovic SQL Server Management Studio provides a set of standard reports that show basic performance information. These reports are available out of the box, no previous installation and configuration is needed. There are more than 20 reports that show usually required monitoring and troubleshooting information at the SQL Server instance level. Read more »
SQL Server Management Studio performance reports July 24, 2014 by Milena Petrovic Basic information about performance on a SQL Server instance is useful to at least scratch the surface of a performance issue. To be able to obtain this information, it’s not necessary to have a specialized monitoring tool that provides tiniest details. SQL Server Management Studio provides features that can quickly help. Read more »