Windocks; Database cloning for SQL Server dev/test on “live” production data March 19, 2019 by Daniel Calbimonte Introduction Today, with DevOps, the need for database cloning is urgent. The development and testing process is shorter and it is required to work and test the data faster.
Working with PowerShell’s Invoke-SqlCmd March 18, 2019 by Timothy Smith PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we’ll often use. In addition, because we may want a custom script using some of the underlying .NET objects, we’ll look at an alternative where we will be able to create a custom PowerShell script that connects to SQL Server in order to run commands. The latter can be useful because one-line scripts have a tendency to change in future versions of PowerShell and working with the library directly can sometimes avoid this challenge. Read more »
SQL unit testing with the tSQLt framework for beginners March 18, 2019 by Esat Erkec tSQLt is a powerful, open source framework for SQL Server unit testing. In this article, we will mainly focus on how to create and run SQL unit testing with help of the tSQLt framework. Before we begin to learn tSQLt framework details, let’s discuss essentials and importance of the database unit testing approach, in general. SQL unit testing is a vital and inseparable part of the modern database development approach and it makes possible to prevent errors before producing the release deployment process. Some database developers are still discussing the needs of the SQL unit testing concept; however, database unit testing is very significant to control and check the behavior of the individual parts of the database. For this reason, we cannot ignore the need to write unit test cases. Read more »
Choosing and Setting a PowerShell Execution Policy March 18, 2019 by Prashanth Jayaram The PowerShell execution policy was developed with a vision to be friendly to administrators, and at the same time, be accessible to end users. More than the former, the latter goal was a little challenging to pull off, considering that convenience is, more often than not, inversely proportional to security. Read more »
Change Data Capture for auditing SQL Server March 18, 2019 by Ahmad Yaseen This article on Change Data Capture will provide an overview, notes on installation, architecture, enabling and disabling, auditing DML statements and limitations Read more »
SQL Server replication: Configuring Snapshot and Transactional Replication March 15, 2019 by Priyanka Devre In this article of the series on SQL Server replication, we will explore ways to configure SQL Server Snapshot replication and Transactional replication step by step.
SQL Server replication configuration: Peer to Peer and Merge Replication March 15, 2019 by Priyanka Devre This article will cover SQL Server replication configuration including Peer to peer replication and merge replication, initial configuration, adding nodes and data verification. Read more »
PowerShell SQL Server Validation Utility – DBAChecks March 15, 2019 by Rajendra Gupta In this article, we will explore the DBAChecks PowerShell SQL Server Module. We can use the DBAChecks module to validate SQL Server instances using the various modules. We can also perform more than 100 configuration reviews using it. Read more »
Simulating a Multi Subnet cluster for setting up SQL Server Always On Availability Groups – lab setup March 14, 2019 by Sreekanth Bandarla In this article, we are going to see how to create a multi subnet cluster spanning across multiple subnets for lab purposes. Creating such an environment should help creating Availability groups simulating a far replica residing in a different Data Center (Subnet) acting as a disaster recovery site in your lab for learning/experimenting real world scenarios. Read more »
Accelerated Database Recovery; Instant Rollback and Database Recovery March 12, 2019 by Rajendra Gupta Accelerated database recovery will be the topic of this article, including killing an active query, abnormal shutdown and the accelerate recovery feature itself, in SQL 2019
tSQLt – A Forgotten Treasure in Database Unit Testing March 12, 2019 by Haroon Ashraf This article is an overview of tSQLt, which is one of the best database unit testing frameworks, due to number of reasons, including the ease of writing unit tests in the same language (T-SQL) in which the database objects themselves are written. Read more »
DBATools PowerShell Module for SQL Server March 12, 2019 by Rajendra Gupta This article will provide an overview and introduction to DBAtools, a powerful open source library of automation scripts. Read more »
How to get a SQL database restore history March 11, 2019 by Rajendra Gupta This article will review how to get information on your SQL database restore history, including the metadata in MSDB that can be queried, as well as value added tools and features to the group, sort, report and export this critical information. Read more »
SQL Server FILETABLE – the next generation of SQL FILESTREAM March 11, 2019 by Rajendra Gupta This SQL Server FILETABLE article is the continuation of the SQL Server FILESTREAM series. The SQL Server FILESTREAM feature is available from SQL Server 2008 on. We can store unstructured objects into FILESTREAM container using this feature. SQL Server 2012 introduced a new feature, SQL Server FILETABLE, built on top of the SQL FILESTREAM feature. In this article, we will explore the SQL FILETABLE feature overview and its comparison with SQL FILESTREAM. Read more »
SSIS interview questions March 11, 2019 by Daniel Calbimonte These SSIS interview questions cover many of the top topics that you will be asked when applying for your next SSIS developer job. Each question has links to articles that cover the topic in depth. Read more »
DevSecOps: Security Testing Around Builds and Shared Information March 11, 2019 by Timothy Smith One big component of DevSecOps surrounding security testing involves how we build and deploy with shared information access of details that may be valuable to an attacker. In order to understand these risks, we must think like attacker who wants to compromise an environment that is focusing on quickly writing code, building the code, testing it, and deploying it across multiple environments. An attacker’s ultimate target will be the highest environment – often a production environment, but some attackers may target lower environments because they may be able to inject code that is deployed to production. In addition, an attacker may only be trying to learn about how an environment is laid out to attack in other ways, such as using social engineering with key information. Read more »
Configuring a SQL Server AlwaysOn High Availability Group March 6, 2019 by Priyanka Devre In this article, we will learn the step by step configuration of SQL Server AlwaysOn High availability Group for two nodes. Once nodes are added to the cluster group, we will able to use the AlwaysOn feature in the SQL server.
DevSecOps: Developing with Automated Security Testing March 6, 2019 by Timothy Smith A key component of DevSecOps and identical to running unit tests to validate code after a build, running automated security testing after an application has been deployed (such as automated penetration tests) can provide us with a tool that identifies security risks. As we’ve seen recently, there’s been a growth of many companies experiencing information being compromised and with the development culture of “move fast and break things”, I expect this trend of successful attacks will continue. Before we look at our options for automating this testing, we want to be aware of its limits, evaluate the requirements, and consider common designs that are useful. Read more »
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping March 5, 2019 by Rajendra Gupta This article is the continuation of the SQL FILESTREAM series. Read more »
Importing Data into SQL Server from Compressed Files March 5, 2019 by Rajendra Gupta Introduction I have seen many organizations receive data from various sources and import into SQL Server. You might receive data in various formats and want to import into SQL Server. We can prepare a ETL (Extract-Transform-Load) process to import data into the SQL Server. In doing so, might receive data in a compressed file, which helps to send data over the network using a ZIP file format because it reduces the file size significantly. If we are receiving a ZIP file to import into SQL Server, we need to unzip it and then only we can import data. We might need to create a ZIP file as well from the existing files. Read more »
Transaction log backups in a SQL FILESTREAM database March 5, 2019 by Rajendra Gupta In the continuation of our SQL FILESTREAM article series, we’ll be covering transaction log backups In SQL Server, we take transaction log backups regularly to have a point-in-time recovery of a database. It is essential to define the backup policy with the combination of full, differential and transaction log backups. A standard configuration of backups for large databases in the production database environment is as follows. Read more »
SQL Server trace flags guide; from -1 to 840 March 4, 2019 by Nikola Dimitrijevic SQL Server trace flags are configuration handles that can be used to enable or disable a specific SQL Server characteristic or to change a specific SQL Server behavior. It is an advanced SQL Server mechanism that allows drilling down into a hidden and advanced SQL Server features to ensure more effective troubleshooting and debugging, advanced monitoring of SQL Server behavior and diagnosing of performance issues, or turning on and off various SQL Server features
Using Python SQL scripts for Importing Data from Compressed files March 4, 2019 by Rajendra Gupta Using Python SQL scripts is a powerful technical combination to help developers and database administrators to do data analytics activities. Python provides many useful modules to perform data computation and processing of data efficiently. We can run Python scripts starting from SQL Server 2017. We can create the ETL solutions to extract data from various sources and insert into SQL Server. Read more »
Importing data from a PDF file in Power BI Desktop March 1, 2019 by Rajendra Gupta Power BI Desktop is a useful reporting and analytical tool to represent data in various formats. These presentations help us to quickly understand information and circulate it to stakeholders in a visual fashion.
SSRS Report Builder introduction and tutorial March 1, 2019 by Esat Erkec The SSRS report builder is a powerful tool for data visualization. In this article, we will take a first step into familiarizing ourselves with the SSRS report builder and we will also demonstrate a very basic SSRS report example. Additionally, we will review the self-service business intelligence approach just because this approach can be implemented through the SSRS report builder very easily. Read more »
