One of the main DBA responsibilities is to ensure databases to perform optimally. The most efficient way to do this is through indexes. SQL indexes are one of the greatest resources when it comes to performance gain. However, the thing about indexes is that they degrade over time.
Read more »Working with SQL Server FILESTREAM – Adding columns and moving databases
January 23, 2019SQL Server FILESTREAM is a great feature to store unstructured data into the file system with the metadata into
SQL Server FILESTREAM database recovery scenarios
January 23, 2019The SQL Server FILESTREAM feature is available from SQL Server 2008 onwards. This feature allows the large BLOB objects to store into the file system and keeps metadata in the database tables. Before you go further in this article, let us have a quick overview of the FILESTREAM series articles.
Read more »Domain Controller and Domain Client Node setup for AlwaysON High Availability
January 23, 2019In this series of AlwaysOn High availability configuration in SQL Server, I am going to write step by step articles from pre-requisite to end configuration. This may help beginners who are going to configure for the first time.
Read more »Using SQL Bulk Insert with Strict Business Rules
January 23, 2019This article will cover SQL bulk insert operations deterministic outcomes and responses covering not allowing any bad data to allowing all data to be inserted, regardless of errors.
Read more »Implicit conversion in SQL Server
January 22, 2019This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of implicit conversion
Read more »Using SQL Power Doc to Discover, Diagnose and Document SQL Server
January 21, 2019Disabling triggers in SQL Server for a specific session
January 18, 2019This article will focus on the various ways to disable triggers in SQL Server so they won’t interfere with certain operations like bulk inserts.
SQL Bulk Insert Concurrency and Performance Considerations
January 18, 2019One of the challenges we face when using SQL bulk insert from files flat can be concurrency and performance challenges, especially if the load involves a multi-step data flow, where we can’t execute a latter step until we finish with an early step. We also see these optimization challenges with constraints as well, as fewer steps to complete a data flow results in saved time, but possibly less accurate data.
Read more »Restoring a SQL Server FILESTREAM enabled database
January 17, 2019In the series on the SQL Server FILESTREAM feature, we have explored the various aspects of FILESTREAM including its overview, internal architecture, database creation etc. In my last article [put the name of the article and a link], we explored the backup for the FILESTREAM enabled database. We can take Full backup as well as filegroup level backup in the FILESTREAM database.
Considering Security with SQL Bulk Insert
January 17, 2019In this article, we’ll discuss security implications of using SQL Bulk Insert and how to mitigate those risks.
Read more »Foreign key issues while applying a snapshot in SQL Server merge replication
January 16, 2019This article will review specific SQL Server merge replication issues related to foreign keys and schema snapshots
SQL Server FILESTREAM Database backup overview
January 16, 2019The SQL Server FILESTREAM feature in SQL Server allows storing the large documents files directly into the file systems. In my previous articles, we explored the concepts of FILESTREAM feature in SQL Server. We also learned the folder structures, metadata information and the process of garbage collection. In this article, we are going to learn about the backup and restores in SQL Server.
Read more »Generating Schedules with SQL Server Agent
January 15, 2019Summary
SQL Server Agent allows us to create jobs and assign any number of schedules to them. This allows for great flexibility when deciding when jobs should run. Over time, as the number of jobs and schedules increase on a given SQL Server, it can become challenging to fully understand when and how often jobs run during a given span of time.
Read more »SQL Server Replication with a table with more than 246 columns
January 14, 2019Problem
In our environment we use SQL Server merge replication to replicate data to multiple subscribers. We had a situation where a table needs to be part of merge replication and the table has more than 246 columns. Merge replication supports tables with maximum of 246 columns. In this article let us see how to replicate a table with more than 246 columns.
Managing data with SQL Server FILESTREAM tables
January 14, 2019SQL Server FILESTREAM allows storing the Varbinary (Max) objects in the file system instead of placing them inside the database. In the previous article – FILESTREAM in SQL Server, we took an overview of the FILESTREAM feature in SQL Server and process to enable this feature in SQL Server Instance.
Read more »Inventory and document your SQL Server estate using PowerShell
January 14, 2019Data growth has raised the stakes in the market increasing the size of SQL Server estates that DBAs are expected to manage. Because of this, fast and efficient configuration management can be very helpful. To this end, let’s delve into this article to generate a SQL Server inventory using PowerShell.
FILESTREAM in SQL Server
January 11, 2019This article will discuss SQL Server FILESTREAM including installation, configuration, enabling and general considerations.
Change default database file and backup paths in SQL Server on Linux
January 10, 2019In a previous article, we explored the process to change default SQL dump file location in SQL Server. In this article, we will view the process to change the default database files and backup locations in SQL Server on Linux.
Read more »Working With Line Numbers and Errors Using Bulk Insert
January 10, 2019In the first part of reviewing the basics of bulk insert, we looked at importing entire files, specifying delimiters for rows and columns, and bypassing error messages. Sometimes we’ll want to skip first and ending lines, log errors and bad records for review after inserting data, and work with data types directly without first importing using a varchar and converting to the data type later. In this part, we look at these techniques using T-SQL’s native bulk insert.
SQL Server Audit Overview
January 9, 2019To fill an optimal role in all business’s computerized operations, information technology systems and infrastructure should be guaranteed, well controlled, aligned with the business goals and secured. Technology auditing ensures the evaluation and examination of these business requirements on an organization.
Read more »Tracking Times In Data Flows for Finding Performance Issues
January 9, 2019We’re facing a challenge with several of our data flows that use more time than they have in the past and we’re not sure when this trend started. We know in the past month, our reports have been delayed by over a day from the start to the finish. For some of our data flows we use SQL Server Agent that calls SSIS packages or procedures, while some of them use a custom data import and reporting application we’ve created. How can we track the length of time for these data flows, since we’re using a combination of tools for importing data?
SQL Server Transaction Overview
January 8, 2019In general, a Transaction is a single unit of work consists of multiple related tasks that should succeed or fail as one atomic unit. To make the concept of the transaction more familiar and why it should go all or none, imagine one of the most critical transaction examples in our daily life, which is withdrawing money from the ATM.
Read more »T-SQL’s Native Bulk Insert Basics
January 8, 2019From troubleshooting many data flow applications designed by others, I’ve seen a common pattern of over complexity with many designs. Putting aside possible risks by introducing too much complexity, troubleshooting these designs often involves opening many different applications – from a notepad file, to SSIS, to SQL Server Management Studio, to a script tool, etc. It may sound like many of these are doing a hundred steps, yet many times, they’re simply importing data from a file, or calling five stored procedures and then a file task of moving a file. This complexity is often unnecessary, as is opening many different tools when we can use a few tools and solve issues faster.
Read more »Improvements of Scalar User-defined function performance in SQL Server 2019
January 4, 2019In SQL Server, we normally use user-defined functions to write SQL queries. A UDF accepts parameters and returns the result as an output. We can use these UDFs in programming code and it allows writing up queries fast. We can modify a UDF independently of any other programming code.