Use cases for Query Store in SQL Server July 18, 2018 by Aamir Syed Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data. Read more »
Multiple methods for scheduling a SQL Server backup automatically July 17, 2018 by Robert Seles SQL Server backups are an essential part of every good disaster recovery strategy. That is the good. But setting up such backups to run effortlessly, is the goal. In this article, we’ll review the types of backups, recommended practices and three different methods for automatically setting up SQL Server backups on a schedule. Note: these solutions can also be used in combination with one another Read more »
Useful T-SQL techniques for development in SQL Server July 17, 2018 by Timothy Smith When we’re developing solutions, we can sometimes forget useful commands we can use in T-SQL that make it convenient to remove data, eliminate objects, or carefully remove data. We look at three of these commands with a few examples of where we might consider using them in development, or in rare production cases. While they may offer us speed and convenience in some cases, we also look at some situations where they may not be the best tool to use. Read more »
Identifying Object Dependencies in SQL Server Management Studio July 16, 2018 by Ben Richardson In relational database systems, objects have different types of relationships with each other. Apart from table relationships (such as one to one, one to many and many to many), objects such as stored procedures, views, custom functions also have dependencies on other objects. It is important to understand object dependencies, particularly if you want to update an object that depends upon other objects. Read more »
How to monitor and manage Transparent Data Encryption (TDE) in SQL Server July 13, 2018 by Sreekanth Bandarla Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted). Read more »
Query optimization techniques in SQL Server: Database Design and Architecture July 13, 2018 by Ed Pollack Description One of the best ways to optimize performance in a database is to design it right the first time! Making design and architecture decisions based on facts and best practices will reduce technical debt and the number of fixes that you need to implement in the future. Read more »
Eating the frog: How to create a daily-deliverable sprint burn down graph in Excel July 12, 2018 by Brian Lockwood In the first article I wrote on how to create a SCRUM-based, sprint burn down in Excel, How to create an advanced sprint burn down chart in Excel, I showed how to easily set up a conventional burn-down with effort points as a percent, to create a pretty nice looking burn down chart, just by entering the IDs of each completed PBI on the date that it was finished. Read more »
Understanding Backup and Restore operations in SQL Server Docker Containers July 12, 2018 by Prashanth Jayaram In this 17th article of the series (see the full article index at bottom), we will discuss the concepts of database backup-and-restore operations on SQL Server Docker containers. This is certainly the era of containers era and it is buzz right now, so let us understand the importance of backup-and-restore the databases on the Docker containers. Read more »
Spatial data types in SQL Server July 11, 2018 by Nikhilesh Patel Spatial data type SQL server furnishes us with the geometry and geography data types for conserving spatial data, which allows us to render graphical data. To be more specific, it is beneficial for creating, analyzing, comparing and retrieving spatial data. Read more »
How to create an advanced sprint burn down chart in Excel July 10, 2018 by Brian Lockwood SCRUM is a great way to manage all sorts of tasks including sprints, but you don’t have to purchase expensive software packages to burn one. Excel is a great tool for rolling your own sprint burn down graph or you can just take mine. In this article, I’ll walk you through a burn down spreadsheet I created, show you how to modify it and at the end you can download it for yourself and kick the tires. Read more »
Data science, data understanding and preparation – grouping and aggregating data July 10, 2018 by Dejan Sarka I already tacitly did quite a few aggregations over the whole dataset and aggregations over groups of data. Of course, the vast majority of the readers here is familiar with the GROUP BY clause in the T-SQL SELECT statement and with the basic aggregate functions. Therefore, in this article, I want to show some advanced aggregation options in T-SQL and grouping in aggregations of data in an R or a Python data frame. Read more »
Main Concepts of SELECT operators in SQL Server execution plans July 9, 2018 by Esat Erkec One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan. Read more »
In-Memory Optimized database backup and restore in SQL Server July 6, 2018 by Prashanth Jayaram A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. A well-planned and well-tested backup-and-restore strategy always help to protect the organization with no data loss, or at least a minimum, from the many varieties of system and database failure. As such, it is recommended to understand the importance and implication of the backup-and-restore strategy. Read more »
Various techniques to audit SQL Server databases July 5, 2018 by Nesha Maric SQL Server auditing has gone from a nice to have to a legal requirement, especially following new legislation like HIPAA and GDPR. Organizations are now tasked with auditing access to records, reporting suspicious and potentially malicious activity, forensically auditing data changes, as well are tracking login attempts, security changes and much more. Read more »
How to link two SQL Server instances with Kerberos July 5, 2018 by Jefferson Elias Introduction Intended audience This document is intended for application developers, database administrators and system administrator who plan to create linked servers between instances of Microsoft SQL Server using Windows Authentication. Read more »
Sharpen your ax July 4, 2018 by Brian Lockwood This is a popular story which illustrates the advantages of not just plowing away at your work, but improving your knowledge, planning, skills and preparation first to achieve a better outcome in the end Once upon a time there were two men who lived in the same forest and decided to have a contest chopping wood. The first man was in good physical shape and very muscular. The second man was in good shape but smaller in statute and wiry. They would chop wood all day and at the end of the day compare to see who had chopped the most wood. The first man laughed to himself that there was no way this wiry little man would beat him and so they began the contest. Every 45 minutes the second smaller man would take a break and seems to just wonder off somewhere. The first man laughed again to himself and said “Yep there’s no way this wiry little man is going to beat me.” This happens several times during the day. At the end of the day the two men compare their piles of chopped wood and unbelievably enough the wiry little man has chopped twice as much wood as the more physically fit man. He says “I don’t understand. First I’m twice your size and twice your strength! On top of that every 45 minutes you rolled off and took a break or a nap or something. You must have cheated!” The smaller man says “I didn’t cheat. It was easy to beat you because every 45 minutes when you thought I was taking a break, I was out back sharpening my ax.” 1 Read more »
SQL Server Execution Plans overview July 4, 2018 by Ahmad Yaseen In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans. When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query. Read more »
Reading the transaction log in SQL Server – from hacks to solutions July 3, 2018 by Nesha Maric The SQL Server transaction log is akin to a ‘Black box’ in an airliner. It contains all of the records of transactions made against a database. This information is a proverbial goldmine for database audits, recoveries etc but it was never meant to be exposed to end users let alone visualized in an easy to read manner nor used for DBA tasks. As such, utilizing this information can be a challenge, to say the least. Read more »
Comprehensive Performance review template July 3, 2018 by Brian Lockwood I looked around for a good performance review template for a long time but I finally just sat down and wrote my own. It is a 5 pt system with various categories that cover hard skills – your skill at your core competency i.e testing soft skills (quality, follower ship, communication, proactivity, intangibles) Performance – how you are doing, productivity Quality Trend – getting better, worse or the same Read more »
SQL Server – using lowest unit of measurement in T-SQL July 2, 2018 by Timothy Smith A client recently discovered a discrepancy on one of our reports that showed an improvement in performance metrics but was inaccurate. Our reports came from a software tool, which showed the average performance throughout the day. It derived this number from periodic checks and the frequency changed, which affected our report. When we showed an improvement in the metrics on a report, the client showed us that the frequency change may have impacted this, not necessarily any improvement in performance. In situations where we’re measuring values and comparing them to other values, how can we prevent a change in measurement from impacting our reports? Read more »
