How to use BULK INSERT to import data locally and in Azure November 16, 2017 by Daniel Calbimonte Introduction BULK INSERT is a popular method to import data from a local file to SQL Server. This feature is supported by the moment in SQL Server on-premises. However, there is a new feature that is supported only in SQL Server 2017 on-premises. This feature allows importing data from a file stored in an Azure storage account to SQL Server on-premises using BULK INSERT. This feature will be supported in Azure SQL versions in the future. In this article, we will show two examples. The first example will show how to use the traditional BULK INSERT statement from a local CSV file to Azure and the second example will show how to import data from a CSV file stored in Azure to SQL Server on-premises. Read more »
How to solve the Identity Crisis in SQL Server November 14, 2017 by Ed Pollack Description Identity columns provide a convenient way to auto-number an ID column within a table without the need to manage the sequence. This convenience can save immense amounts of time, but also presents a single challenge: What happens when an identity column runs out of space within the data type chosen? Read more »
How to set up SQL Server Log Shipping on Linux November 13, 2017 by Prashanth Jayaram Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS. Read more »
How to import data from an Excel file to a SQL Server database November 10, 2017 by Marko Zivkovic There are many ways to import data from an Excel file to a SQL Server database using: SQL Server Integration Services (SSIS) the OPENROWSET and OPENDATASOURCE functions SQL Server linked servers the SQL Server Import and Export Wizard In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes. Read more »
An introduction to the bcp Utility (bulk copy program) in SQL Server November 9, 2017 by Daniel Calbimonte Introduction The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa. It is very popular because it is fast and easy to download. Read more »
How to be the top SQL Shack author and attend the PASS Summit – twice in a row November 8, 2017 by Ahmad Yaseen Being the top SQL Shack author again required double the effort and the time than I spent last year (2016) to achieve it, competing more professional authors with creative writing styles. Fortunately, I achieved it this year again, two years in a row. Let me tell you how I succeeded and what kept me motivated! Read more »
How to identify and resolve Hot latches in SQL Server November 7, 2017 by Bhavesh Patel Description In SQL Server, internal latch architecture protects memory during SQL operations. It ensures the consistency of memory structures with read and write operation on pages. Rudimentarily, it has two classes, buffer latches, and non-buffer latches which perform lightweight synchronization in the SQL Engine. Read more »
An overview of Python vs PowerShell for SQL Server Database Administration November 2, 2017 by Prashanth Jayaram Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have: Read more »
What’s new in SQL Server Management Studio 17.3; Import Flat File wizard and XEvent Profiler November 1, 2017 by Ahmad Yaseen SQL Server Management Studio is an integrated graphical user interface tool that is used to perform wide range of administration and development tasks on the SQL Server instances, hosted locally on the user machine, remotely on a Windows or Linux server or on SQL Azure or Azure Data warehouse instances hosted in the cloud. You can use SQL Server Management Studio to easily administrate, develop, deploy, configure and monitor the SQL Server instances. Read more »
Commonly used SQL Server Constraints: FOREIGN KEY, CHECK and DEFAULT October 31, 2017 by Ahmad Yaseen In the previous article Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY, we described, in detail, the first three types of the SQL Server constraints; NOT NULL, UNIQUE and PRIMARY KEY. In this article, we will discuss the other three constraints; FOREIGN KEY, CHECK and DEFAULT by describing each one briefly and providing practical examples. Read more »
How to build a SQL Server Virtual Lab with AutomatedLab in Hyper-V October 30, 2017 by Craig Porteous There are often situations I want to test a piece of software or the latest version of SQL Server for a proof of concept, maybe even the new Power BI Report Server. I could install these locally, where I’ve likely got previous SQL installs or an already burgeoning system. You could be lucky that you have access to Virtual Machines that you can build out for the task. Now add a few hours to setup Windows or even just the applications themselves. Those VMs may be in Azure, or AWS, now add a few £’s or $’s every time you want to work on something. Did you remember to turn them off? Read more »
How to import/export data to SQL Server using the SQL Server Import and Export Wizard October 27, 2017 by Marko Zivkovic This article will explain the steps for importing and exporting data using the SQL Server Import and Export Wizard. Depending on the options that are selected in the SQL Server Import and Export Wizard, some steps may be different than those described in this article. For the purpose of this example, exporting data from the SQL Server database to an Excel file will be shown. Read more »
SQL Server utilities you probably didn’t know about – SQLdiag, osql and SqlLocalDB October 26, 2017 by Daniel Calbimonte Introduction There are several tools included in SQL Server on-premises that you may not know. In this article we will explain the following tools: SQLdiag osql SqlLocalDB After reading this article, you will learn how to use them and understand what are they for. Read more »
Commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY October 25, 2017 by Ahmad Yaseen SQL Server Constraints are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column’s data. In other words, if the inserted data meets the constraint rule, it will be inserted successfully. If the inserted data violates the defined constraint, the insert operation will be aborted. Read more »
ETL optimization using SQL Server TRY Functions October 24, 2017 by Sifiso W. Ndlovu Introduction An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL Server built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise. Read more »
Why would a SQL Server DBA be interested in Python? October 23, 2017 by Prashanth Jayaram If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think: Why so much emphasis on Python these days? Isn’t knowing PowerShell scripting sufficient for the automation requirements of today? Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently? Why do so many job postings these days include “knowledge of scripting” as a requirement? Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift? Read more »
How SQL Server selects a deadlock victim October 20, 2017 by Ben Richardson In the article on Deadlock Analysis and Prevention, we saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why one process is selected as deadlock victim and not the other. Read more »
How to import a flat file into a SQL Server database using the Import Flat File wizard October 19, 2017 by Marko Zivkovic Import Flat File wizard is a new feature implemented in SQL Server Management Studio (SSMS) v17.3 for importing a .csv or a .txt file into a SQL Server database. The latest SSMS version can be found on this link Read more »
Working with the SQL Server command line (sqlcmd) October 18, 2017 by Daniel Calbimonte Introduction Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line. In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure. In this new chapter, we will show the following examples in a local SQL Server using sqlcmd: Read more »
Monitoring SQL Server database status changes using T-SQL and PowerShell scripts October 17, 2017 by Prashanth Jayaram Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays. Read more »
