Connecting to Azure SQL Database May 23, 2018 by Istvan Martinka The below screenshots and code samples are valid at the time of writing (May 2018) but as things in Azure can change so quickly, please check the latest documentation if a code sample doesn’t work as expected! Read more »
Step by step installation of SQL Server 2017 May 23, 2018 by Daniel Calbimonte Introduction The new SQL Server 2017 comes with new features in the installation. It now supports Machine Learning Services that support R and Python. It also includes SSIS Scale Out Master and Scale Out Worker. It also includes scale out options in PolyBase. Read more »
Backup Linux SQL Server databases using PowerShell and Windows task scheduler May 22, 2018 by Prashanth Jayaram This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines. Here’s an outline of what this article is all about: Introduction Technical specifications How to load SQL Server modules on Windows machine Security – Credential Management The objectives of Backup and Restore Library Linkage How SQL Server 2017 backup feature is used on Linux And more … Read more »
Understanding benefits of Graph Databases over Relational Databases through Self-Joins in SQL Server May 21, 2018 by Sifiso W. Ndlovu Earlier this year, I published several articles on SQLShack with an aim of demonstrating tools available for visualising SQL Server 2017 graph databases. I was so caught up in the excitement of having SQL Server finally support graph databases that I forgot that some people still do not have a good grasp of how graph databases work let alone consider replacing their relational databases models in favour of graph. Although there are several ways that one can go about explaining the usefulness of graph databases over its relational counterpart, I have opted to focus on the benefits and strengths of graph databases by demonstrating the differences in which graph and relational databases deal with hierarchical datasets. Read more »
Securing access for SQL Server auditing May 21, 2018 by Timothy Smith Our organization must restrict permissions and prove to an independent party that we investigate access and restrict permissions. We restrict permissions to objects using least permissions and give full access to an exceptional few based on an organizational design that follows best practices. What can we do additionally that will help us prevent unauthorized access or catch when someone who shouldn’t have access is able to infiltrate our systems, especially in the context of showing a third party that we track this. Read more »
Tracing and tuning queries using SQL Server indexes May 18, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of the SQL Server tables and indexes, the best practices to follow when designing a proper index, the group of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes and finally the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification. In this article, we will discuss how to tune the performance of the bad queries using SQL Server Indexes. Read more »
How to perform a page level restore in SQL Server May 18, 2018 by Prashanth Jayaram In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required. Read more »
Restricting and monitoring SQL Server data access with views and procedures May 17, 2018 by Timothy Smith We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »
Recursive CTEs and Foreign Key References in SQL Server May 16, 2018 by Gerald Britton Introduction Foreign key constraints are a powerful mechanism for preserving referential integrity in a database. They can also represent a challenge when doing bulk table loads, since you need to find a “base” table to start with – that is, a table that has no foreign key constraints defined. Let’s label tables like this as level 0, or ground level if you like. Once that is loaded, you can begin to load other tables that have foreign key references to the base table. We can label those tables level 1, and so on. If you start with table data that already has referentially integrity and load tables by their level numbers — level 0, level 1, level 2 and so on – the load should proceed without problems. Let’s look at a simple example: Read more »
Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions May 16, 2018 by Ben Richardson Data conversion is one of the most fundamental tasks of any programming language. Data received from different sources is often not in the right format. For example, if you receive an XML file where age is in the string format and you want to calculate an average age for the people in the file you will need to convert age into an integer. To make the conversion process simple, the TRY_PARSE and TRY_CONVERT functions were introduced in SQL Server 2012. Before TRY_PARSE and TRY_CONVERT, SQL Server only had the PARSE and CONVERT functions. Read more »
The HashBytes function in T-SQL May 16, 2018 by Nikhilesh Patel One of the paramount ways to guard data within a database is to utilize database encryption. However, no one encryption solution is perfect for all databases. Which encryption solution you select totally depends on the requirements of your application. Note that more powerful encryption for larger amounts of data requires a healthy amount of CPU. So, be prepared in the event that that introduction of encryption increases the system load. Read more »
Logging SQL Server database errors May 15, 2018 by Timothy Smith We receive many database alerts with many of the alerts logging some of these same alerts or information to files or tables. What we’ve found over time is that the logging is now costing us quite a bit of resource. Our logging server (where both files and table logging are stored) has had a few outages related to conflicts from messages for other servers. We’ve considered scaling the alerting by environment type, but we’ve also considered that we may be logging too much information about our databases. In addition, since we receive so many alerts each day, it’s impossible for us to resolve them and assist with other issues that arise. What are some techniques that we can use to help us with the issue of too much logging information and too many alerts? Read more »
Data science, data understanding and preparation – entropy of a discrete variable May 14, 2018 by Dejan Sarka In the conclusion of my last article, Data science, data understanding and preparation – binning a continuous variable, I wrote something about preserving the information when you bin a continuous variable to bins with an equal number of cases. I am explaining this sentence in this article you are currently reading. I will show you how to calculate the information stored in a discrete variable by explaining the measure for the information, namely the entropy. Read more »
Smart database backups in SQL Server 2017 May 14, 2018 by Prashanth Jayaram So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion: Discuss checkpoints Discuss the enhancements made in the Dynamic Management View (DMV) sys.dm_db_file_space_usage for smart differential backups Discuss the enhancements made for the Dynamic Management function (DMF) sys.dm_db_log_stats for smart transactional log backup Understand the functioning of smart differential backup and its internals Understand the Smart transaction log backup process and its internals T-SQL scripts And more… Read more »
FAQ and examples about the SQL Server Agent May 11, 2018 by Daniel Calbimonte Introduction In this article, we will answer FAQs about the SQL Server Agent. We will learn how to create a job, some things about the internal tables used, how to schedule jobs, add PowerShell jobs, cmd jobs, T-SQL jobs and more. Read more »
Reading file data with PowerShell May 10, 2018 by Timothy Smith We have some custom files that we receive from different providers and for these situations we are unable to use standard ETL programs without any customization. Since we’re expanding our ability to read these custom files with .NET, we’re looking for efficient ways to read files with PowerShell that we can use in SQL Server Job Agents, Windows Task Schedulers, or with our custom program, which can execute PowerShell scripts. We have many tools for parsing data and wanted to know efficient ways of reading the data for parsing, along with getting specific lines of data from files by number, or by the first or last line of the file. For reading files efficiently, what are some functions or libraries we can use? Read more »
How to add a new disk to an existing SQL Server failover clustered instance May 9, 2018 by Sreekanth Bandarla In this article, we are going to see how to add a new disk to an existing SQL Server failover clustered instance (which I will be referring as FCI from now on). Let’s assume a scenario where you have an existing FCI with dedicated data and log drives and you want to add a new disk (Let’s say you have a new requirement to store your backups locally). Read more »
Working with different SQL Server indexes types May 8, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of both SQL Server tables and indexes, the main guidelines that you can follow to design a proper index, the list of operations that can be performed on the SQL Server indexes, and finally how to design effective Clustered and Non-clustered indexes that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process, which is the main goal of creating an index. In this article, we will go through the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification, and when to use them. Read more »
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques May 7, 2018 by Prashanth Jayaram Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine. Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result. Read more »
Designing effective SQL Server non-clustered indexes May 7, 2018 by Ahmad Yaseen In the previous articles of this series (see below for the full index of articles), we went through the internal structure of SQL Server tables and indexes, listed a number of guidelines that help in designing a proper index, discussed the operations that can be performed on SQL Server indexes and finally showed how to design and create a SQL Server Clustered index to speed up data retrieval operations. In this article, we will see how to design an effective Non-clustered index that will improve the performance of frequently used queries that are not covered with a Clustered index and, in doing so, enhance the overall system performance. Read more »
