Posted on

Migrate a SQL Server Database to CockroachDB

The fastest way to get started with SQL Server is via available Docker containers. I’m using the following tutorial to deploy SQL Server on Ubuntu from my Mac. My SQL Server-Fu is a bit rusty and I opted for following this tutorial to restore WideWordImporterssample database into my Docker container. You may also need SQL Server tools installed on your host and you may find direction for Mac OS and Linux at the following site, users of Windows are quite familiar with a download location for their OS. 

I also used the following directions to install SQL Server tools on my Mac but ran into compatibility issues with the drivers in my Docker container. This will be a debug session for another day.

Run SQL Server in Docker

Pull the SQL Server image:

docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04


Posted on

Row-Level Security: SQL Server vs Gallium Data

Introduction

In SQL Server 2016, Microsoft introduced the concept of row-level security, which gives you fine-grained control over who gets access to what data, potentially down to the level of individual rows. Normally, SQL security grants coarse access to a whole table or view (SQL Server can also do it for columns), and anything more granular than that requires the use of views or stored procedures.

There is another way to do row-level security without changing the database clients, and that's with Gallium Data - a free database proxy that can change the network traffic between your database clients and your database servers.

Posted on

Pagination in MS SQL Server

What Is Pagination?

Pagination is the process of dividing large data into smaller data sets in discrete pages. It is widely used in web applications.

How Does Pagination Work in MS SQL Server?

In MS SQL Server, we can achieve the pagination functionality by using OFFSET and FETCH clauses with ORDER BY in a SELECT statement.

Posted on

How to Fix Recovery Pending State in SQL Server Database

This post will explain why a database (DB) is tagged as a recovery pending. It will also discuss how to resolve the ‘SQL server database in recovery pending status' issue. You may fix the issue by running queries in SQL Server Management Studio (SSMS) or by utilizing a professional SQL database recovery tool.

SQL Server Database States

Posted on

Building a RESTful Service Using ASP.NET Core and dotConnect for PostgreSQL

The term REST is an abbreviation for Representational State Transfer. It is a software architectural style created to assist the design and development of the World Wide Web architecture. REST defines a set of constraints that define how a distributed hypermedia system, such as the Web, should be architected. Restful Web Services are HTTP-based, simple, lightweight, fast, scalable, and maintainable services that adhere to the REST architectural style.

The REST architectural style views data and functionality as resources accessed via Uniform Resource Identifiers (URIs). Restful architecture is a client-server paradigm that utilizes a stateless communication protocol, often HTTP, for data exchange between server and client. In REST, the clients and servers interact through a defined and standardized interface.

Posted on

Working With Transactions in Entity Framework Core and Entity Developer

Entity Framework Core, a lightweight cross-platform version of the Entity Framework, gives you a standard method to retrieve data from various data sources using the Entity Framework. It supports working with transactions as well, so you can create and manage transactions elegantly.

This article presents a discussion on how we can work with transactions using Entity Framework Core and Entity Developer for data access.

Posted on

[Fixed] Database in Recovery Mode SQL Server 2014

Summary

This post discusses the instances that put a SQL Server 2014 database in recovery mode and what causes the database to get stuck in recovery. Also, it discusses the methods to restore the database. 

You may find a SQL Server 2014 database in recovery mode when you restart the SQL Server service or restore a database from SQL Server Management Studio (SSMS). Also, a database goes into recovery mode if you restart a system abnormally. The database goes into recovery in such a scenario until all the active transactions are committed or rolled back. Let the recovery process complete, and the database comes back online.

Posted on

How Hasura 2.0 Works: A Design and Engineering Look

Hasura GraphQL Engine launched as an open-source product in July 2018. Since then, Hasura has been downloaded over 200M times and has become the fastest-growing open-source GraphQL project globally. 

Hasura accelerates API development by 10x by giving you GraphQL or REST APIs with built-in authorization on your data, instantly. In this blog post, we’re going to look at how Hasura works, what we thought about when building Hasura, and the problems we were trying to solve.

Posted on

Set Up a Database Diagram Using a Stored Procedure In SQL Server

Steps to be Followed:

  1. Create tables
  2. Create stored procedure using inner join between two tables
  3. Find out stored procedure syntax as text using SQL query
  4. Execute stored procedure to get results
  5. List of tables used in a stored procedure
  6. Then create a database diagram

Step 1

Create a table named: Supplier_Type_Master.

SQL syntax

Posted on

Fetch T-SQL With Passed Parameter Value Using Stored Procedure

Introduction

Using "sys.objects," we can get a list of tables and stored Procedures created in the Database. SQL Server now supports table-valued parameters, which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API. Using SYSOBJECTS and SYSDEPENDS, we can get all the tables, stored procedures, and other database object-related information.

Description

You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects. It contains a row for each user-defined, schema-scoped object that is created within a database. sys.objects does not show DDL triggers because they are not schema-scoped. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. It was not possible to pass a table variable as a parameter to a stored procedure in old versions of SQL Server.

Posted on

How To Recover SQL Server FILESTREAM Enabled Database

Introduction

Besides storing table-based data in SQL Server, you can also store Binary Large Object (BLOB) data as files using the SQL Server FILESTREAM feature. This feature allows the storing of the varbinary(max) objects onto the file system instead of placing them inside a database. However, you can create a database with a 'FILESTREAM' filegroup, allowing you to perform actions on the data stored in the file system using the database.

Sometimes, when saving files to a database using FILESTREAM in SQL Server, the files may turn corrupt. Also, modifying or deleting the files placed in the file system folder (as FILESTREAM data container) may result in consistency errors reported by DBCC CHECKDB. To fix the errors, you can try restoring the database to its original state. But if the backup is not available, repairing the FILESTREAM database using the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option may help.

Posted on

Extended Events: Avoid the XML

One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped.

Look, I get it. I don’t like XML either. It’s a pain to work with. It’s actively difficult to write queries against it. If there weren’t a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let’s see how.

Posted on

How to Perform Object Level Recovery in SQL Server

When a SQL Server database turns corrupt and cannot be repaired, you can restore it from a recent backup to recover objects and data. However, the problem arises when you need to perform object level recovery instead of performing a full database backup.

In other words, there may arise a need to restore a specific table or any other object from the database backup, rather than the complete database. But, there is no direct method to perform object level recovery.

Posted on

Which Configuration Changes in SQL Server Require a Restart?

SQL Server is a complex beast, with many configuration options that can range from recommended to completely avoided.

Since the release of SQL Server 2016, several options that were recommended post-install have been rolled into the default installation options and no longer need to be done, and similar changes were made with SQL Server 2017. Even so, there are configuration changes we data professionals need to make after installation, during maintenance windows, and sometimes even during operating hours, so here’s a handy list of changes that do and don’t require a restart of your operating system or SQL Server instance.

Posted on

How to Migrate Data From SQL Server to PostgreSQL

Migrating data between different types of databases is not a trivial task. In this article, we will compare several ways of converting from SQL Server to PostgreSQL.

Microsoft SQL Server is a great database engine, but it has drawbacks in some cases.

Posted on

The Rule of Three, SQL Server on Linux Edition

When it comes to Microsoft products, the rule of three — at least as far as I'm concerned — is where you can accomplish the same task in three different ways. The go-to example is Microsoft Word, where you can use the ribbon toolbar, a keyboard shortcut, or the context menu to perform the same common formatting options.

As some of you may know, I'm preparing for the upcoming PASS Virtual Summit, where I'm presenting a pre-conference session today titled "SQL Server on Linux from A to Z." As I was preparing my demo scripts, I noticed that this rule of three extends to Linux as well. I've been using Linux (and FreeBSD) since 1996, and have learned in that time that there are many (many!) ways to do the same things.