Unit Testing Report Procedures – Jump to Start TDDD Part-4

Unit Testing Report Procedures – Jump to Start TDDD Part-4
Rate this post

This article is a walk-through of creating a stored procedure through test-driven database development (TDDD) in order to meet a reporting requirement that cannot be fulfilled by using a database view.

This article also provides some useful hints about cases of preferring stored procedure over database view as a potential object that is going to fulfill the business requirement(s). Read More

Backup and Restore FILESTREAM-Enabled Database

Backup and Restore FILESTREAM-Enabled Database
3.7 (73.33%) 3 votes

In my previous articles, I explained how to create and configure the FILESTREAM feature in SQL server instance. Moreover, I demonstrated how to create a table that has a FILESTREAM column and hot to insert and delete the data from it.

In this article, I am going to explain how to backup and restore the FILESTREAM-enabled dataase. Moreover, I am going to demonstrate how to restore FILESTREAM filegroup without making database offline. Read More

Storing Files in SQL Database Using FILESTREAM – Part 2

Storing Files in SQL Database Using FILESTREAM – Part 2
Rate this post

In my previous article, I described how to configure FILESTREAM in SQL Server, create FILESTREAM-enabled database and tables. Moreover, I demonstrated how to insert and delete data from the FILESTREAM table.

In this article, I am going to demonstrate how to insert multiple files in a FILESTREAM table using T-SQL. Read More

Storing Files in SQL Database Using FILESTREAM – Part 1

Storing Files in SQL Database Using FILESTREAM – Part 1
4.3 (86.67%) 3 votes

FILESTREAM has been introduced by Microsoft in 2008. The purpose was to store and manage unstructured files more effectively. Before FILESTREAM was introduced,  the following approaches were used to store the data in SQL server:

  1. Unstructured files can be stored in the VARBINARY or IMAGE column of a SQL Server table. This approach is effective to maintain transactional consistency and reduces the file management complexity, but when the client application reads data from the SQL table, it uses SQL memory which leads to poor performance.
  2. Instead of storing the entire file in the SQL table, store the physical location of the unstructured file in SQL Table. This approach gives huge performance improvement, but it does not ensure the transactional consistency moreover file management was difficult too.

Read More

SQL Database Administrator Interview Tips

SQL Database Administrator Interview Tips
3 (60%) 2 votes

Interview is the most significant step of SQL database administrator recruitment. During the interview, a technical person or interviewer asks questions about the technical experience of a candidate. The purpose of these questions is to understand candidate’s experience about SQL Server. The answers to the questions will measure your knowledge and experience. If you cannot prove your experience in this interview, most probably the decision on your employment will be negative. First of all, you should be calm during the interview. This advice is the most significant key to success. The first question of an interview is about your past experience and skills. Being relevant to this question, you should describe your old and current job responsibilities and skills. You must be sure that you demonstrate all of your knowledge and experience to the interviewer. Because this introduction makes the first impact on your potential employer. Read More

Moving Existing Table From Primary Filegroup to Different Filegroup

Moving Existing Table From Primary Filegroup to Different Filegroup
Rate this post

In this article, I am going to explain how to move a table from the Primary filegroup to the Secondary filegroup. First, let’s understand what are datafile, filegroup, and type of filegroups.

Database Files and Filegroups

When SQL Server is installed on any server, it creates a Primary data file and Log file to store data. The Primary data file stores data and database objects like tables, index, stored procedures, etc. Log files store information required to recover transactions. Data files can be clubbed together in filegroups. Read More

Move Datafiles in SQL Server – Part 1

Move Datafiles in SQL Server – Part 1
3.3 (66.67%) 6 votes

Introduction

There are a number of situations which would warrant the movement of database files or transaction log files from one volume to another on the same server. These may include:

  1. The need to format the volume assuming it was not formatted properly when SQL Server was installed. Recall that when installing SQL Server, it is recommended that 64K allocation unit size is used to format the volumes. If this is not done at the point of installation and needs to be done later, it will obviously require preserving a backup of the database fist or creating a new, properly formatted volume and moving the database to this new volume.
  2. The need to use a new volume assuming the limits have been reached for the underlying storage. A good example would be the 2TB limit of a VMware Data Store. This is the case as of VSphere 5.0. Higher versions of VSphere have much higher limits.
  3. The need to improve performance by managing IO. One more reason you may want to move datafiles is performance. There are cases where a database is created with multiple datafiles all sitting on one disk until it becomes obvious, as the database grows, that the you have created a “hot region” in the storage layer. One solution would be creating new data files and rebuilding clustered indexes, another would be moving data files.

Read More

Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine – PART2

Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine – PART2
4.7 (93.33%) 3 votes

After reading this article, you will be able to the following:

  1. Create a Linux Docker container using the Docker commands.
  2. Perform required configuration change to setup Docker.
  3. Install DBFS FUSE and related dependent packages.
  4. Mount the file-system and data extraction procedures using the configuration file.
  5. Run DBFS program to run custom SQL queries.
  6. Push Docker container image to GitHub repository.
  7. And more…

Read More

Implementing Paging Using OFFSET FETCH NEXT in SQL Server

Implementing Paging Using OFFSET FETCH NEXT in SQL Server
4.3 (86.67%) 6 votes

A database can have hundreds of thousands of records. It is easy to insert and select these records via database management systems like SQL Server or MySQL etc. However, it is not easy to display thousands of records on a single webpage or in a desktop application. Space and memory constraints make it difficult to display a huge number of records all at once.

A common solution to such a problem is to implement paging. (Note, this is not memory paging implemented by operating systems) Paging in programming refers to displaying data via a series of pages. A random Google search may result in thousands of results. Google uses paging to display these results. If you scroll down the Google page with search results you would see the following:

Here you can see the number of pages that the search result is divided into. You can click the Next link to see more pages.

In this article, we will see how OFFSET FETCH NEXT operators can be used to implement paging on front-end applications. We will begin with a simple example using the OFFSET FETCH NEXT operator and will then see how it can be practically used with the help of a stored procedure.

Read More

Basics of sys.dm_exec_requests

Basics of sys.dm_exec_requests
4.2 (84.44%) 9 votes

Performance monitoring and troubleshooting in SQL Server is a vast topic. In SQL Server 2005, dynamic management views, also known as DMV’s, have been introduced and became an essential helping tool for diagnosing SQL Server performance problems. At the same time, we can use dynamic management views for Azure SQL Database. Some of them can differ from SQL Server on-premise database but the logic of work is still the same. Microsoft has very good documentation about dynamic management views. The only thing,  you need to be careful about the version and product validation of dynamic management views. Read More