Fundamentals of Managing Datafiles in SQL Server

Total: 1 Average: 5

Introduction

Datafiles are physical objects that constitute the most important part of the database system since they contain actual data. You can think of a database as a collection of data files. An instance gives you the means of mounting and accessing such files.

Here, managing datafiles is understanding how to monitor and resize existing datafiles and how to add or remove the data files from a database.

Read More

Five Different Methods To Start, Stop, And Restart SQL Server Services

Total: 1 Average: 5

When we install the SQL Server, it also installs the following services:

  1. SQL Server database engine service to manage and access data in SQL Server.
  2. SQL Server Integration Service for the ETL process – we install it when including Integration services during setup.
  3. SQL Server Reporting Service to manage the SQL Server reports.
  4. The SQL Server Agent Service. Note that it is not available in the SQL Server Express edition.

In this article, we are going to learn the following methods to manage SQL Server services:

  1. Start, stop, and restart SQL Server services using the SQL Server configuration manager.
  2. Start, stop, and restart SQL Server services using Services MMC (Microsoft Management Console).
  3. Start, stop, and restart SQL Server services using the PowerShell script.
  4. Start, stop, and restart SQL Server services in Ubuntu Linux.
  5. Start, stop, and restart SQL Server services using the Windows server failover cluster manager.

Let us examine all these methods.

Read More

3 Easy and Secure Ways to Make Users Run SSIS Packages

Total: 1 Average: 5

Have you ever come across a situation where you needed to make users run SSIS packages? This happens when the user is not comfortable with a fixed schedule for whatever reason. You can’t define if it’s every weekend, at 9 pm, or the 5th of the month. It happens when it happens.

This sounds problematic, depending on the volume of data. If you know that a million or so records won’t be advisable to process any time of the day, say no. But if the data volume is not a problem, what are your options?

Read More

Configure SQL Server Log Shipping

Total: 1 Average: 5

This article explains the step by step deployment process of the SQL Server Log shipping. It is the database-level disaster recovery solution that is easy to set up and maintain.

The log shipping involves three steps:

  1. Generate the log backup on the primary database.
  2. Copy the backup to the network location or the specific directory on the secondary server.
  3. Restore the log backup on the secondary server.

The log shipping technology performs the steps described above by using SQL Server agent jobs. During the configuration process, the log shipping wizard creates those jobs on primary and secondary servers.

Read More

Configure SQL Jobs in SQL Server using T-SQL

Total: 1 Average: 5

SQL Server Agent is a component used for the database tasks automation. For instance, we need to perform Index maintenance on Production servers during the non-business hours only. So, we create a SQL Server job of running index maintenance and schedule it for “off” hours.

When we install SQL Server, the SQL Server Agent service is disabled. First, we enable it and start it manually. Then, we configure the SQL Server job, using SQL Server Management Studio and the system stored procedures of the MSDB database.

This article explains how to create a SQL Server Job using the system stored procedures of the MSDB database.

Read More

Configure Database Mirroring in SQL Server

Total: 2 Average: 5

Database mirroring is a SQL Server high availability solution provided by Microsoft, with the following components.

  1. Principal database server: a source database that you configure for the mirroring.   
  2. Mirror database server: a destination database that you must restore with NORECOVERY. Ideally, a mirrored database should be on a separate server.
  3. Witness Server: an optional component that you can set up to configure the mirror with automatic failover.
  4. Endpoint: the communication medium used by the principal and mirror server. The mirror listens on port number 5022; however, you can change it during the configuration.
Read More

Introduction to Azure Database Migration Service (ADMS)

Total: 4 Average: 5

Microsoft Azure platform provides an Azure Database Migration Service (ADMS) – a convenient solution for the Azure Cloud-based Databases migration tasks in a straight forward way. In this article, we elaborate on the AMDS essential work and integrated processes. 

We put in the focus issues related to the migration of object resources on Cloud to Cloud and on-premises database server to cloud. Also, we’ll deal with the cross-database migration compatibility between different database technologies.

Read More

Deploying a Database on Azure SQL

Total: 3 Average: 5

Introduction

Azure SQL Database is a Platform as a Service (PaaS) offered by Microsoft Azure. Hence, you need the Azure subscription to use this SQL Database. Azure offers new subscribers a free subscription and $200 worth of resources for 30 days. Recently I revived my relationship with MS Azure and explored the platform a little bit, using the Warner Chaves’ PluralSight Azure SQL Database for SQL Server DBAs training for the start.

In this article, I will explore the steps required to create an SQL Server in Azure and deploy a small database. Here I am using a free tier subscription, and the resources are on the basic level.  Further, I’ll explain these terms.

Read More

10 Tips & Tricks To Work With SQL Server Management Studio

Total: 6 Average: 4.8

SSMS provides access for managing and solving queries in SQL. However, besides its primary and most demanded options, it includes lots of tips and tricks that you might not be aware of. They help enhance the productivity of your work, though.

In this article, we’ll describe those tips you can use in SSMS to increase your efficiency and reduce the time spent on routine.

Read More