Column-level and row-level tracking in merge replication

Column-level and row-level tracking in merge replication
4.6 (91.11%) 9 vote[s]

In this article, let us review row-level and column-level tracking options in merge replication and how these are used in detecting conflicts during merge replication.

Merge Replication: Merge replication is used to replicate data in both ways i.e. from the publisher to the subscriber and from the subscriber to the publisher.

The initial snapshot of objects is taken and applied to subscribers. Incremental data changes and schema changes are tracked using triggers and applied to subscribers when the subscriber synchronizes with the publisher.

Read More

DBCC SHRINKFILE Overview

DBCC SHRINKFILE Overview
5 (100%) 4 vote[s]

Running DBCC Shrink commands is quite a controversial issue across the SQL Server community. In this article, we will review details about this command and provide a brief overview of its use and also warn you about the risks of running this command. As DBAs, a number of databases were handed over to from other teams or vendors, and it is not always we get to manage the databases which we created. As DBAs, whenever we are involved in migrations or new projects, we need to ensure that we carefully plan a smooth transition of the database to production and regular use. It is at this stage that we need to factor in the size of the database. Can you imagine, you set up a database application without considering the growth forecast for the first year or so. How about you create a SQL Server database with size so small that it needs to grow every other day raising capacity disk alerts in the middle of the night? It may sound silly, but in reality, the truth is this happens, and this sometimes may not be in your control.

Read More

Automating SQL Database Maintenance Tasks using SQLCMD

Automating SQL Database Maintenance Tasks using SQLCMD
4.3 (85%) 4 vote[s]

This article is about automating SQL database maintenance tasks through SQLCMD utility which lets you run T-SQL commands directly from the command prompt without using SSMS (SQL Server Management Studio).

Typically, automating database tasks requires SSMS (SQL Server Management Studio) for scheduling jobs that run these tasks, but in this article, an alternative approach is used to automate database tasks without having to use the much-needed SSMS.

The SQLCMD utility can be a real time saver for database developers and DBAs since they can immediately run the necessary SQL scripts from the command line, and automating database maintenance tasks with the SQLCMD utility is a plus.

Read More

Exporting Data to Flat File with BCP Utility and Importing data with Bulk Insert

Exporting Data to Flat File with BCP Utility and Importing data with Bulk Insert
4.1 (82%) 20 vote[s]

The BCP (Bulk Copy Program) utility in SQL Server allows database administrators to import data into a table and export data from a table into a flat file. The BCP utility also supports various features that facilitate the process of exporting and importing of the bulk data.

Now let’s get started with a business scenario. Read More

DBCC CheckDB Overview

DBCC CheckDB Overview
4.1 (82.86%) 7 vote[s]

Introduction

Regular database maintenance is an important part of a Database Administrator’s job which helps to ensure that critically important systems are running as per normal. One of the easiest ways to accomplish this will be to automate tasks related to DBCC CheckDB. No matter what version of SQL Server you are running, there will never be a database that requires no maintenance. You will have to plan the maintenance to occur regularly so that you can cover your back especially at the time of a real disaster scenario.

Read More

SQL Server CRUD Operations – Part 2

SQL Server CRUD Operations – Part 2
3.6 (72%) 5 vote[s]

CRUD word is an acronym of the following essential database functions.

In the first part of CRUD article series, we mentioned the concept of the CRUD operations and then discussed the INSERT operation with examples. In this part, we will continue exploring the CRUD operations and will focus on the read, update and delete operation details of CRUD. Read More

Transferring Jobs and Schedules between Instances using T-SQL

Transferring Jobs and Schedules between Instances using T-SQL
4.3 (86.67%) 3 vote[s]

Introduction

Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, and these cases are not uncommon. Also, this wouldn’t work if the recipient MS SQL Server instance is of a newer version than the one you’re transferring the jobs from. So, how can you transfer the Agent jobs without restoring the msdb database?

We will look at an example of a T-SQL script which copies the Agent jobs from one instance of MS SQL Server to another. This solution was tested by transferring jobs from MS SQL Server 2012-2016 to MS SQL Server 2017.

Read More

SQL Server Bulk Insert – Part 2

SQL Server Bulk Insert – Part 2
4 (80%) 2 vote[s]

In the previous part of this article, we discussed how to import CSV files to SQL Server with the help of BULK INSERT statement. We discussed the main methodology of bulk insert process and also the details of BATCHSIZE and MAXERRORS options in scenarios.  In this part, we will go through some other options (FIRE_TRIGGERS, CHECK_CONSTRAINTS and TABLOCK) of bulk insert process in various scenarios.

Read More

Implementing Automated Database Backup and Restore with Default Means

Implementing Automated Database Backup and Restore with Default Means
3.5 (70%) 6 vote[s]

Introduction

You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means.

This example will cover a number of approaches – from checking the database’s integrity before backing it up to restoring the database from a previously created backup copy.

Read More

SQL Server 2019 New Features

SQL Server 2019 New Features
4.4 (88.49%) 73 vote[s]

SQL Server 2019

Microsoft’s leading database is an essential tool, with in-cloud and on-premises versions providing incredible storage and analytic tools. It’s additionally turned into an essential application for data scientists, giving a structure to building and testing machine learning models. There is more in SQL Server, and the new release version can indicate where Microsoft thinks your data needs will go over the next few years.

Like all the previous versions, the Microsoft SQL Server 2019 latest version is also jam-packed with new features and capabilities. It will help organizations evaluate and test relational data with multiple integrations of various software. By creating a simplified view of data, users can have a comprehensive view of information.

Read More