What’s new in SQL Server 2017 August 14, 2018 by Prashanth Jayaram SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing lot about it and testing various features of SQL Server 2017, I’d like to walk you through some of its interesting features. Read more »
Getting started with SQL Operations Studio (SOS); initial installation and configuration August 14, 2018 by Prashanth Jayaram Microsoft SQL Operations Studio is a new GUI-based (vs CLI) lightweight tool for developing and managing your modern SQL databases. The tool resembles Microsoft’s lightweight code editor, the open source IDE Visual Studio Code in terms of functionalities and the UI in general. If you’ve used Visual Studio Code (or VS Code), you’d feel at home with SQL Operations Studio. SQL Operations Studio also integrates really well other products like Azure SQL Database and Data Warehouse has been made very simple. SQL Operations Studio is a cross-platform tool available for Linux, Windows, and Mac to manage the SQL Server databases. You can go ahead and download Microsoft SQL Operations studio to try it out! Read more »
All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool August 14, 2018 by Prashanth Jayaram One of the major challenges we face today, in the software development lifecycle, is with respect to development and deployment. As applications are deployed by moving various pieces of the SQL code between several versions, configuration, different editions, and sometimes even different platforms, deployment becomes daunting. In my opinion, scripting helps a lot and can really “grease the wheels” with addressing many scenarios that involve such complexity. The importance of database level scripting is high, but it is getting easier by the day. Scripting is a process of generating a text file that contains the data structures and data from the database objects. So, do we have a SQL native tool which serves the various purposes and usage? In some cases, the answer’s a yes, and in others, it’s a no. Read more »
Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe August 14, 2018 by Prashanth Jayaram A little curiosity on my part led me to research a little further on the available pieces of SQL Server Tools. A cross-platform tool for continuous integration and deployment, called SqlPackage, is part of SQL Server Data Tools. I picked it up because continuous integration, delivery and deployment are now must-have in the modern-day application development paradigm. We’d talk about the following in this article: The Database Management Life Cycle Complexities of the database release management process Introduction to SQL Server Data Tools and SqlPackage Using SqlPackage.exe, with examples Read more »
The BCP (Bulk Copy Program) command in action August 13, 2018 by Prashanth Jayaram There are various methods available for bulk data operations. BCP utility BULK INSERT Using OPENROWSET Import/Export wizard The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information. Hence, it is recommended to create a format file to record the data format so in case of any failures, you can refer to the format file and better understand the data format to determine what may have gone wrong.. We’ve been using the BCP tool for a long time, the reason being that it has a very low overhead, and works great for bulk exporting and importing of data. It is one of the most efficient ways to handle bulk import and export of data. Read more »
Overview of the SQLCMD utility in SQL Server August 13, 2018 by Prashanth Jayaram This article is aimed at helping you understand the sqlcmd utility. Of course, this is a tool most of us have used it at several occasions given that the tool has been around for a decade now. However, to be a solid starting point, this article contains some examples that help you understand the tool from different viewpoints. This article discusses the following: How to enable SQLCMD A few important points to remember SQLCMD with scripting variables in SSMS Passing variables (or argument) as T-SQL and or a SQL Script file SQLCMD with scripting variables and Windows scripting in SSMS Read more »
Using transactional data replication to replay and test production loads on a staging server August 9, 2018 by Prashanth Jayaram Outline In this article, you’ll see how to simulate production loads on a test server with a “record and replay” type situation using the transaction log, batch scripting, PowerShell and a SQL Server agent job. We’ll be walking through the scenario in the following steps Record the production load and write the transactions to disk by generating a timestamped replay script Create a batch file to automate the task at an interval of every 1 minute Create a SQL Server agent job to schedule the batch file Replay the production workload to the target/test database by running a PowerShell script to open and execute the scripts at the same interval as they were created, every 1 minute Validate the data between the source and the target databases to make sure our job works Monitor the load with a monitoring tool, solution of your choice Read more »
How to setup SQL Server database replication for a reporting server August 7, 2018 by Prashanth Jayaram In this article, we’ll discuss the purpose of database replication and show how you can implement Replication using ApexSQL Log, a SQL Server transaction log reader. Read more »
Dynamic Partitioning in Azure Analysis Services (tabular) August 6, 2018 by Istvan Martinka Objective The real-life requirement Disclaimer: I assume dear Reader, that you are more than familiar with the general concept of partitioning and star schema modeling. The intended audience is people who used to be called BI developers in the past (with a good amount of experience), but they have all sorts of different titles nowadays that I can’t keep up with… I won’t provide a full Visual Studio solution that you can download and just run without any changes or configuration, but I will give you code can be used after parameterizing according to your own environment. Read more »
Top SQL Server bloggers of 2017 August 1, 2018 by SQLShack We made a collection of the most popular SQL Server bloggers, including a link to each individual blog. The ranking is based on Alexa global score. The results are preliminary and won’t be “official” until Sept 1st, 2018, because we want to make sure we update the list with feedback on anyone we may have missed. If you noticed a blogger that we missed, please let us know in the comments. Read more »
Interview questions on SQL Server database backups, restores and recovery – Part I July 30, 2018 by Prashanth Jayaram So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore database command gives an option to test the consistency and integrity of the backup file. As we all know, the backup database command bound with many database options. Indeed, it facilitates the execution of specific backup database command that meets the business requirement. Read more »
Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment? July 27, 2018 by Aamir Syed So, your manager wants you to figure out how to encrypt sensitive Data? Well, Microsoft has introduced a fairly easy way to configure feature called Always Encrypted. Read more »
Interview questions and answers about data science, data understanding and preparation July 27, 2018 by Dejan Sarka Q1: In the data science terminology, how do you call the data that you analyze? In data science, you analyze datasets. Datasets consists of cases, which are the entities you analyze. Cases are described by their variables, which represent the attributes of the entities. The first important question you need to answer when you start a data science project is what exactly is your case. Is this a person, a family, an order? Then you collect all of the knowledge about each case you can get and store this information in the variables. Read more »
Database Backup and Restore process in SQL Server – series intro July 26, 2018 by Prashanth Jayaram It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures. Ready to dive-deep into Backup-and-Restore procedures? Let’s go! Read more »
Interview questions on SQL Server database backups, restores and recovery – Part IV July 25, 2018 by Prashanth Jayaram In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more. Read more »
Interview questions on SQL Server database backups, restores and recovery – Part III July 25, 2018 by Prashanth Jayaram So far, we’ve discussed a lot about database backup commands. In this article, we’ll discuss more on database restore or database recovery processes. When you initiate a restore process, it undergoes a series of internal stages to restore or recover the data to the specific point of time. Read more »
Interview questions on SQL Server database backups, restores and recovery – Part II July 25, 2018 by Prashanth Jayaram In this article, we’ll walk through, some of the refined list of SQL Server backup-and-restore, (or recovery) interview Q&A. Also, it includes the high-level overview and links of the “stairway to backup-and-restore series” articles for detailed information. As you read through the lists of Q&A, you’ll learn most of the features and concepts of Backup-and-Restore operations. Read more »
Concurrency problems – theory and experimentation in SQL Server July 24, 2018 by Jefferson Elias Introduction Intended audience This document is intended for application developers and database administrators who are willing to get an overview of comm concurrency problems to which transaction isolation levels respond in the particular case of Microsoft SQL Server. Read more »
SQL Server – development practices with referenced views July 24, 2018 by Timothy Smith We’ve recently had production failures because our developers changed an important reference. In this case, we had a view which several procedures and views referenced. A developer made a change to the referenced view by removing columns, which caused several procedures and a view that referenced it to fail. We’re considering whether we should stop this practice, or if there are other ways we can prevent changes to an object that’s being referenced by other objects (in our case, a view). Read more »
SQL Server Execution Plans types July 23, 2018 by Ahmad Yaseen In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans. Read more »
