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 »
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 »
In this article, in the data science: data analysis and transformation series, we’ll be talking about information entropy.
Read more »
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:
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 »
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 »
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 »
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 »
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 »
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 »
In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restoration operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.
Read more »
With the popularity of social networking and sharing sites and their widespread availability, internet marketing techniques have also been rapidly influenced. Sharing sites with high visitor traffic offers great potential for advertisers, so much so that it is almost unheard of now that brands or products do not have a profile or group on a social networking site. Markets have the opportunity to communicate directly with consumers by creating profiles or groups in sharing sites. Thanks to the comments and forums on the pages, they are able to follow both positive and negative attitudes of the target groups about themselves and thus have more precise ideas about what to do next.
Read more »
As per Wikipedia, the Halloween problem was first discovered by Don Chamberlin, Pat Selinger, and Morton Astrahan, on Halloween day, 1976.
Read more »
Data replication has been around for many decades. There are two primary types of data replication, logical and physical. This article covers a high-level view of logical replication, the differences between logical and physical replication, and the specifics of SQL Server transactional replication.
Read more »
Data extraction is a pivotal part of any business process particularly when it comes to running reports and facilitating business decision-making. In the article, How to configure OData SSIS Connection for SharePoint Online, I covered data extraction off a SharePoint Online list using SQL Server Integration Services (SSIS). You would have noticed in the aforementioned article that getting SSIS to successfully integrate with SharePoint Online lists can be a laborious exercise, especially if you haven’t installed the correct SharePoint SDK files. Thus, in business environments where business and Power Users have more control of data extraction processes, SSIS could get complicated for an ordinary business user to operate. Therefore, given the nature of our source data and the platform in which it resides, ETL architects and developers alike may need to find alternative ETL tools to SSIS. This brings me to Microsoft Flow which could be one possible alternative to using SSIS for data extraction. Microsoft Flow is part of Office 365 applications and just like SharePoint Online, is a cloud-based application that is freely available, easier to operate and effortlessly integrates with – amongst other applications – SharePoint Online. The aim of this article is to demonstrate the convenience of extracting data from one SharePoint Online list to another using Microsoft Flow.
Read more »
Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.
Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.
Read more »
GUID is a 16 byte binary SQL Server data type that is globally unique across tables, databases, and servers. The term GUID stands for Globally Unique Identifier and it is used interchangeably with UNIQUEIDENTIFIER.
Read more »
In the previous articles of this series (see bottom for a full index), we described, in detail, the structure of SQL Server tables and indexes, the basics and guidelines that help us in designing a proper index and finally the list of operations that can be performed on the SQL Server indexes. In this article, we will see how we could design an effective clustered index that the SQL Server Query Optimizer will always take benefits from, in speeding up the data retrieval process, which is the main goal of building an index.
Read more »
SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing and consist of the three features:
When you practice and play with the Azure Storage, you spent money because you need to pay for the service. This obviously provides an incentive to discover a way to emulate the Azure Storage behavior.
In this article, we will use an emulator and learn how it works.
Read more »
SQL Server chooses parallel plans based on the costing (there are also some other factors that should be met for the plan that it can go parallel). Sometimes serial plan is slightly cheaper than a parallel, so it is assumed to be faster and picked by the optimizer, however, because the costing model is just a model it is not always true (for a number of reasons, enlisted in Paul’s article below) and parallel plan runs much faster.
Read more »
While preparing the post about Adaptive Joins, I’d like to share a quick post about the hidden gem in SQL Server 2017 CTP 2.0, discovered recently. In this short post, we will look at how you can determine what statistics are used by the optimizer during a plan compilation in SQL Server 2017.
Prior to SQL Server 2017, there were two ways how you could do it, both undocumented and involving undocumented trace flags.
Read more »
This article explores SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback mechanism in SQL Server.
Read more »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy