What are SQL Virtual Log Files aka SQL Server VLFs? February 18, 2019 by Ahmad Yaseen In this article we’ll review SQL Virtual Log files aka SQL Server VLFs, providing an overview and review topics related to size, performance, monitoring and troubleshooting. Read more »
Applying Field Operators and Objects in Azure Cosmos DB February 15, 2019 by Timothy Smith Since we will sometimes require removing documents in Azure Cosmos DB, we’ll want to be able to specify the documents for removal. In some cases, this will be as simple as specifying a field for removal, such as removing one type of workout in our temporary database we’ve created. In other delete situations, we’ll want to remove if the value of the field isn’t what we expect – such as greater than what we want. This applies to updates as well – we may want to drill into a specific value range for an update. In this tip, we’ll look at using operators with strings, numeric types and dates. Read more »
Export SQL Server FILESTREAM Objects with PowerShell and SSIS February 14, 2019 by Rajendra Gupta In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.
SQL Server FILESTREAM Database Corruption and Remediation February 13, 2019 by Rajendra Gupta This article will cover corruption and recovery scenarios in the context of SQL Server FILESTREAM including missing data, incompatible files types, DBCC checks, orphan files and garbage collection
Power BI Desktop and Python; like Peanut Butter and Chocolate February 13, 2019 by Rajendra Gupta Power BI Desktop helps to visualize complex data with the help of inbuilt and custom visualizations. It allows integrating data from the various data sources and preparing visualization from it. Microsoft recently integrated the Python programming language in the Power BI. Python is a powerful object-oriented language and is used by the data analyst and scientist to analyze complex datasets. We can now use the Python as a preview feature in the Power BI August 2018 release onwards. We can now use Python scripts to generate custom visualization. The combination of these technologies is akin to 1 + 1 = 3 or like the famous ad for Reese’s Peanut butter cups, when chocolate was introduced to Peanut Butter 😉 Read more »
Python in SQL Server: The Basics February 13, 2019 by Ben Richardson With the introduction of SQL Machine Learning Services, it is now possible to run Python Scripts from any SQL Server client such as SQL Server Management Studio. In addition to directly running the Python Scripts on SQL Server Clients, you can write Python Code on native Python editors and run it remotely on SQL Server using Python clients for SQL Server. In this article, we will see how to execute some of the basic Python functionalities within SQL Server Management Studio. The article provides an introduction to running basic Python scripts in SQL Server Management Studio. Read more »
Creating a SQL Server audit using SQL Server Extended Events and Triggers February 13, 2019 by Ahmad Yaseen This article will provide an overview of manually creating a SQL Server audit using SQL Server Extended events and triggers. It will provide an overview of SQL tracing/profiling as well as extended events. I’ll walk you through how to create a new extended events session and how to use it, in a worked example, to audit failed logins. Finally, we’ll touch on an auditing approach using triggers Read more »
SQL Unit Testing with SSDT (SQL Server Data Tools) February 12, 2019 by Esat Erkec In this article, we will discuss the essentials of the SQL unit testing and then we will explore how to apply SQL unit testing methodology in Azure SQL Database with help of SQL Server Data Tools (SSDT). In particular, this article will focus about unit test theory in terms of SQL Server database development aspect and illustrate SQL unit testing with a detailed example. Read more »
Updating and Querying Details in Azure Cosmos DB February 12, 2019 by Timothy Smith In the first part of this series Getting Started with Azure Cosmos DB, we looked at using Azure Cosmos DB for saving an individual’s fitness routine and why this database structure is better for this data than a SQL database while also showing that we still have to organize our structure like a file system organizes files. In this part of our series, we’ll begin looking at the terminology translation between NoSQL and SQL along with running updates for our documents and queries with filters that return some fields in our document, but not other fields.
Viewing SQL Server FILESTREAM data with SSRS February 12, 2019 by Rajendra Gupta Importing and exporting SQL Server FILESTREAM stores objects into the file system and applications can retrieve these objects to benefit from the IO streaming capability of the Windows OS. In my earlier articles we used the SSIS package to import and export data from the a FILESTREAM table Read more »
Getting Started with Azure Cosmos DB February 11, 2019 by Timothy Smith In the past two years, we’ve seen an explosion in growth with document-oriented databases like Azure Cosmos DB. MongoDB – one of the major document databases – went live on the Nasdaq and attracted some attention in the past year as well. While more developers are using the document structure for some appropriate data models, less than 10 years ago, some in the industry were predicting that document databases were unnecessary and wouldn’t last because all data could be flattened to fit the SQL model. I took the opposite approach, being an early adopter of MongoDB along with continuing to use SQL databases as I saw opportunities in both SQL and NoSQL for various data structures. While some data do fit the SQL model and SQL will continue to exist, some data are best for document databases, like Azure Cosmos DB. In this series, we’ll be looking at the why and how of document databases.
How to work with SQL random numbers in SSIS February 7, 2019 by Daniel Calbimonte Introduction In this article, we will show how to work with SQL random numbers in SSIS. To demonstrate this, we will have a table with people and we will create a winner randomly from that list. Read more »
Preparing for SQL Server Database Corruption; initial reaction and analysis February 6, 2019 by James Rhoat Corruption is a looming thought through every administrator’s mind, from sysadmins to database administrators there is always a monster hiding in the shadows. SQL Server Database corruption happens all the time around the world and while most of us have been lucky enough to avoid it, we should still be prepared. While there is no way to prevent the corruption from happening, we must work hard to implement practices that minimize damage caused by the corruption. This means good backups and of course running DBCC CHECKDB. Read more »
SQL database provisioning via Database clone using PSDatabaseClone PowerShell module February 4, 2019 by Milan Rancic With the complex SQL database development and production infrastructure there comes an issue with database provisioning. The issue implies that all development and testing instances should have proper version of a database in the proper environments. So that means that database development teams, client application teams, QA and testing teams need to work on proper database version which is usually the one in production but naturally cannot work on the production database itself. This is why those environments have to have a provisioned database and there comes the problem for database DevOps teams how to perform database provisioning, which is repetitive task, with optimal time invested. Read more »
SQL Server FILESTREAM queries and Filegroups February 1, 2019 by Rajendra Gupta In this series of the SQL Server FILESTREAM (see TOC at bottom), We have gone through various aspects of this feature to store large size objects into the file systems.
Importing SQL Server FILESTREAM data with SSIS packages February 1, 2019 by Rajendra Gupta Initial configuration We have been exploring the SQL Server FILESTREAM feature in this ongoing series of articles. In this previous article, Managing data with SQL Server FILESTREAM tables, we wrote about inserting FILESTREAM data into a FILESTREAM table and performing DML activities on it. Suppose we have created the FILESTREAM database in our instance and now we want to insert a large number of files into a FILESTREAM container. It is easy to write out the insert queries for a small number of files, but if the numbers of files were in huge quantity, it would be difficult to write out the code and insert data into it. It is difficult to manage such kind of requests regularly in the environment. Read more »
SQL Server Transaction Log Architecture January 31, 2019 by Ahmad Yaseen This article will cover SQL Server transaction log architecture including file topography, basic overview, review of LSN and MinLSN, and log truncation
CTEs in SQL Server; Using Common Table Expressions To Solve Rebasing an Identifier Column January 31, 2019 by Timothy Smith Since we know that the SQL CTE (common table expression) offers us a tool to group and order data in SQL Server, we will see an applied example of using common table expressions to solve the business challenge of re-basing identifier columns. We can think of the business problem like the following: we have a table of foods that we sell with a unique identifier integer associated with the food. As we sell new foods, we insert the food in our list. After a few years, we observe that many of our queries involve the foods grouped alphabetically. However, our food list is just a list of foods that we add to as needed without any grouping. Rather than re-group or re-order through queries using a SQL CTE or subquery, we want to permanently update the identifier. Read more »
SQL Server Replication (Merge) – Performance Issues in replicating schema changes January 30, 2019 by Ranga Babu This article will review performance issues in replicating schema changes on tables involved in SQL Server Replication (Merge). Read more »
SQL Server In-Memory database internal memory structure monitoring January 30, 2019 by Prashanth Jayaram We can use a variety of available options to keep systems performing at their very best. The SQL Server In-Memory OLTP technological advancement is a great innovation to relieve tempdb contentions. This feature is available with an additional advantage to monitor systems without incurring stress on the database servers. Read more »
SQL Convert Function January 29, 2019 by Esat Erkec In this article, we will discuss and learn basics and all details about SQL Server data type converting operations and also we will review the SQL CONVERT and TRY_CONVERT built-in functions with various samples. At first, we will explain and clarify syntax of the SQL CONVERT function and then we will learn how can we make data converting process numerical and date/time data types to character data. Read more »
CTE SQL Deletes; Considerations when Deleting Data with Common Table Expressions in SQL Server January 29, 2019 by Timothy Smith In this article, the latest in our series on Common table expressions, we’ll review CTE SQL Deletes including analyzing the operation before the delete, actually removing the data as well as organizing and ordering the deletes with CTEs. Read more »
Importing and Working with CSV Files in SQL Server January 28, 2019 by Ben Richardson Introduction CSV (comma separated values) is one of the most popular formats for datasets used in machine learning and data science. MS Excel can be used for basic manipulation of data in CSV format. We often need to execute complex SQL queries on CSV files, which is not possible with MS Excel. However, before we can execute complex SQL queries on CSV files, we need to convert CSV files to data tables. Read more »
Inserts and Updates with CTEs in SQL Server (Common Table Expressions) January 28, 2019 by Timothy Smith In CTEs in SQL Server; Querying Common Table Expressions the first article of this series, we looked at creating common table expressions for select statements to help us organize data. This can be useful in aggregates, partition-based selections from within data, or for calculations where ordering data within groups can help us. We also saw that we weren’t required to explicitly create a table an insert data, but we did have to ensure that we had names for each of the columns along with the names being unique. Now, we’ll use our select statements for inserts and updates. Read more »
Implementing a manual SQL Server Audit January 25, 2019 by Ahmad Yaseen A SQL Server audit can be performed using various number of methods. This includes using the built-in SQL Server Audit feature, using third party tools from the SQL Server market or simply perform the audit task using the legacy methods manually. In the previous article of this series, SQL Server audit overview, we discussed the main concept of a SQL Server audit, the importance of auditing the SQL Server instance changes and the SQL server audit checklists. In this article, we will go through the manual procedures for auditing the SQL Server instance. Read more »
