Top SQL Server LinkedIn groups August 16, 2018 by SQLShack Here you can find 60+ LinkedIn groups ordered by a number of members in it and with short description from the group’s profile page. Read more »
How to collect performance and system information in SQL Server August 16, 2018 by Bojan Petrovic In this article, we’re going to through many of the tools we can use for monitoring SQL Server. SQL Server is chock-full of lots of good reports that allows a DBA to quickly spot whether there is any current performance bottleneck on the SQL Server. Many of these sit on top of DMVs but they give us a visually interactive way to look and work with the data. We’re going to start with SQL Server Performance Dashboard Reports. Dashboard Reports Let’s jump into SQL Server Management Studio (SSMS) and the first thing we’re going to take you through out-of-the-box dashboard reports of all levels. They can be found by right-clicking the SQL Server instance in Object Explorer, and from the context menu, you’ll find Reports > Standard Reports: Read more »
Shrinking your database using DBCC SHRINKFILE August 16, 2018 by Gerald Britton Introduction SQL Server is pretty good at managing disk space. As long as we do our part to set up appropriate storage types and place files and filegroups properly and set reasonable AUTOGROW settings, it’s almost a set-it-and-forget-it operation. Mind you, I said, “almost!” Sometimes, things do go BUMP! in the night and we need to act. Here’s what happened to me not too long ago: Read more »
SQL Server database migrations with zero data loss and zero downtime August 15, 2018 by Prashanth Jayaram The growing importance and complexity of data migration, in an era of exploding data volumes and ever-changing business requirements, means that old approaches will no longer get the job done. We are in a world where everything needs to run instantly. Every Database Administrator or Developer would have definitely heard about database migrations with zero downtime and with zero data loss. Read more »
Difference between Identity & Sequence in SQL Server August 15, 2018 by Ben Richardson In SQL Server, both the SEQUENCE object and IDENTITY property are used to generate a sequence of numeric values in an ascending order. However, there are several differences between the IDENTITY property and SEQUENCE object. In this article, we will look at these differences. Read more »
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 »
