SQL Database Administrator Interview Tips

SQL Database Administrator Interview Tips
3.5 (70%) 8 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
4 (80%) 5 votes

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.6 (72.5%) 8 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

Basics of sys.dm_exec_requests

Basics of sys.dm_exec_requests
4.4 (87.27%) 11 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

Main Usage of sys.dm_os_wait_stats

Main Usage of sys.dm_os_wait_stats
4.1 (82.5%) 8 votes

As you know, the main responsibility of the database administrator lies in the monitoring of the SQL Server performance and intervening in determined time. You can find several SQL Server performance monitoring tools in the market but sometimes we need additional information about SQL Server performance to diagnosis and troubleshoot the performance issues. So we must have enough information about SQL Server Dynamic Management Views to handle issues about SQL Server.

Dynamic Management View (DMV) is a concept that helps us to discover SQL Server Engine performance metrics. DMV was first announced in SQL Server 2005 version and it continued in all versions of SQL Server afterward. In this post, we will talk about particular DMV whose database administrator must have enough information. This is sys.dm_os_wait_stats.

Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Fixing Data Loss Using Log Shipping with Delayed Recovery
4 (80%) 5 votes

Introduction

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More

Export data from SQL Server to Excel and Text file via using SSIS package

Export data from SQL Server to Excel and Text file via using SSIS package
4.3 (85%) 4 votes

While using the SSIS data flow, we can perform ETL task that can be used for data migration, data offloading, design and implementation of the data warehouse.

In this article, I explain how to export data from an SQL table to excel and text file by using the SSIS data flow task.

In this demo, I will perform through the following steps:

1. Create an SSIS package and create ADO.NET connection manager.

2. Create data flow task that has ADO.NET source to populate data from SQL Server. Data will be written to excel file and text file hence configure flat file destination and excel destination.

3. Execute SSIS package and verify that data was written to excel and text files.

Read More

Consolidating SQL Server Instance by Clustering and Stacking

Consolidating SQL Server Instance by Clustering and Stacking
3 (60%) 4 votes

Stacked SQL Cluster Instances

NOTES:

  • Windows Failover Clustering comprising two nodes.
  • Two SQL Server Failover Cluster Instances. This configuration optimizes the hardware. IN01 is preferred on Node1 and IN02 is preferred on Node2.
  • Port Numbers: IN01 listens on port 1435 and IN02 listens on port 1436.
  • High Availability. Both nodes back up each other. Failover is automatic in case of failure.
  • Quorum Mode is Node and Disk majority.
  • Backup LAN in place and routine backup configured using Veritas

Read More

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2
4.5 (90%) 2 votes

In my previous article, I have covered the following topics:

  1. What are Registered Servers and their usage.
  2. How to create and configure a Local Server Group and Central Management Studio.

In this article, I am going to demonstrate how to execute a query on multiple servers using Registered Servers. Also, I am going to cover how we can evaluate the database policy on multiple database servers using Registered Servers. Read More

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 1

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 1
5 (100%) 1 vote

Managing numerous database servers in an organization is difficult. Either we must maintain a list or maintain an Excel file that has a list of database servers. To retrieve basic information about SQL Instance or databases, DBA must query on individual database servers. If a developer wants a DBA to execute an SQL Script on all database servers, DBA must review the list of servers and execute them one by one, which is time-consuming and error-prone. It might happen that DBA missed any server where it was required to execute. Imagine the cases when DBA must execute the script on specific database servers. It becomes very painful. This task can be performed using the PowerShell scripts but again, it requires some complex PowerShell coding. This task can be managed more efficiently using “Registered Servers”. Read More