These SQL Server DBA Interview Questions and answers for beginners and experts. The following questions and answers are List of frequently asked SQL Server DBA Interview Questions with answers by SQL DBA School. We assume these SQL Server DBA Interview Questions and answers are very useful and will help you to get the best job in the IT industry. This SQL Server DBA Interview Questions and answers are prepared by SQL Server DBA Professionals based on Human Resource Companies expectation. Stay tuned we will update New SQL Server DBA Interview questions with Answers Frequently. 

Q. Compare SQL Server with Oracle.

CriteriaSQL ServerOracle
Supported platformsWindows and LinuxWindows, Solaris, Linux, and Unix
Language usedT-SQL (Transact-SQL)PL/SQL (Procedural Language/ SQL)
UsageSimpler and easy to useComplex but powerful
SyntaxSimpleComplex

Q. How can SQL Server instances be hidden?

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this, we have to follow the below steps:

  • First, in SQL Server Configuration Manager, we have to expand ‘SQL Server Network Configuration’
  • Right-click on Protocols for <server instance> and select ‘Properties’
  • Once we do that, we will find a ‘HideInstance’ box in which, on the ‘Flags’ tab, we have to select ‘Yes’
  • After that, click on ‘OK’

Note: While hiding a named instance, we need to provide the port number in the connection string so that even if the browser is running it is possible to connect to the hidden instance.

Q. What is fundamental unit of SQL DB?

Page

Q. What is size of page?

Kb

Q. Can we add a CPU to SQL Server?

Yes. we can add CPUs physically by adding new hardware, either logically by online hardware partitioning or virtually through a virtualization layer. Starting with its 2008 version, SQL Server supports CPU Hot Add. There are a few requirements to use CPU Hot Add:

  • Hardware that supports CPU hot add
  • 64-bit edition of Windows Server 2008 Datacenter or Windows Server 2008 Enterprise Edition for Itanium-based system OS
  • SQL Server Enterprise

Once the CPU is added, we need to run RECONFIGURE, and then SQL Server recognizes the newly added CPU.

Q. What is the size of page header?

96 bytes.

Q. How can we check whether the port number is connecting or not on a Server DBA?

TELNET PORTNUMBER
TELNET PAXT3DEVSQL24 1433
TELNET PAXT3DEVSQL24 1434

Common Ports:

MSSQL Server: 1433
HTTP TCP 80
HTTPS TCP 443

Q. What are contains in page header?

Index of data, free space

Q. How many types of pages are available?

9

Q. What is Extent?

Extents are basic unit where space is allocated to tables and indexes

Q. How can you start SQL Server in different modes?

Single-user Mode (-m):

sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******

DAC (-A):

sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******

Emergency:

ALTER DATABASE test_db SET EMERGENCY

Q. What is the size of extent?

8 contiguous pages or 64KB

Q.  How many system databases are there?

4+2

Q. List the system Databases?

Master
Model
MSDB
Tempdb
Resource
Distribution

Q. Why is SHRINKFILE/SHRINKDB/AUTOSHRINK not preferable?

  • In the SHRINKFILE command, SQL Server isn’t careful about where it puts the pages that are moved from the end of the file toward the beginning of the file.
  • Data becomes fragmented, potentially up to 100 percent, and hence it is a performance killer for the database.
  • Slow operation: All pointers, being moved to/from the page/rows, have to be fixed and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)

Recommendations:

  • First, use TRUNCATEONLY to shrink the file. It removes the inactive part of the log and then performs the shrink operation.
  • Rebuild/reorganize indexes once the shrink is done so that the fragmentation level is decreased.

 Q. What is the basic syntax for create database?

Create database <databasename>

Q. What different steps will a SQL Server Developer take to secure SQL Server?

  • Preferring NT authentication
  • Using server, database, and application roles to control access to data
  • Securing physical database files using NTFS permissions
  • Using an unusable SA password for restricting physical access to SQL Server
  • Renaming the Administrator account on the SQL Server computer
  • Disabling the Guest account
  • Enabling auditing using multiprotocol encryption
  • Setting up SSL and firewalls
  • Isolating SQL Server from the webserver

Q. How to create database with specific file location?

Restore Database <database name> from disk='<Backup file location + file name>’

Q. What action plan is preferred if SQL Server is not responding?

Connect using DAC via CMD or SSMS:

  • Connect via CMD
  • SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
  • Once you connect to the master database, run the diagnostic queries to find the problem
  • Correct the issue and restart the server

Q. List few methods of HA technologies in SQL?

Backup, Restore, Replication, Mirroring, Log shipping, Clustering, etc,..

Q. Which are the third-party tools used in SQL Server and why would you use them?

Some of the third-party tools used in SQL Server are:

  • SQL Check (Idera): For monitoring server activities and memory levels
  • SQL Doc 2 (Redgate): For documenting databases
  • SQL Backup 5 (Redgate): For automating the backup process
  • SQL Prompt (Redgate): For providing IntelliSense for SQL Server 2005/2000
  • LiteSpeed 5.0 (Quest): For backup and restore processes

Benefits of using these third-party tools:

  • Faster and flexible backup and recovery options
  • Secure backups with encryption
  • An enterprise view of the backup and recovery environment
  • Easy identification of optimal backup settings
  • Visibility into the transaction log and transaction log backups
  • A timeline view of backup history and schedules
  • Recovery of individual database objects
  • Encapsulation of a complete database restore into a single file to speed up restore time
  • Improving SQL Server functionality
  • Saving time and proving better information or notification

Q. What is the syntax for drop database?

Drop database <database name>

Q. What is database backup?

Backup is copy of data/database.

Q. What are Hotfixes and Patches?

Hotfixes are software patches applied to live systems (the ones still running). A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product (i.e., a software bug).

In the Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. For example, if a select query returns duplicate rows with aggregations, the result may be wrong. This can be fixed by a hotfix.

Q. How many types of backup is there?

3

1Q. Suppose, there is a trigger defined for INSERT operations on a table in an OLTP system. The trigger is written to instantiate a COM object and passes the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time-consuming process and since it is done within a trigger, it impedes the data insertion process. The same is the case with sending emails from triggers. This rundown can be better implemented by logging all the necessary data into a separate table and having a job that checks this table and does the needful.

Q. List the backup types?

Full /Database
Differential /Incremental
Transactional Log /Log

Q. Which key provides the strongest encryption in SQL Server DBA?

AES (256-bit).

If we choose a longer key, then encryption will be better. Hence, we need to choose longer keys for more encryption. However, there is a larger performance penalty for longer keys. DES (Data Encryption Standard) is a relatively old and weaker algorithm than AES (Advanced Encryption Standard).

Q. What are the methods for backup?

T-SQL & SSMS

Q. Which port do you need to open on your server firewall to enable named pipe connections?

To enable named pipe connections, we need to open Port 445. Named pipes communicate across TCP Port 445.

Q. What is SSMS?

SQL Server Management Studio.

Q. What are the services are available along with SQL server?

Reporting services, Analysis Services, Management Studio, Integration services, Full text search, browser, etc..

Q. Suppose, we have 300 SSIS packages to be deployed to production. How can we make it easier? What are the easy and short ways to deploy all SSIS packages at once?

  • We have to store this data as XML-based files, not in the MSDB database.
  • With the configuration files, we can point the packages from prod to dev (and vice versa) in just a few seconds.
  • The packages and the config files are stored in a directory of our choice.
  • Resources permit to create a standalone SSIS server away from the primary SQL Server

Q. How to create new user in SQL server?

Create user <username> for login <loginname>

Q. How to decide between active and passive nodes?

Cluster Administrator checks the SQL Server group where we can see the current owner. The current owner is the active node and the other nodes are passive nodes.

Q. How to provide access to particular access?

Use <database name>

Grant <permissionname> on <objectname> to <username>

Q.  What is SQL server monitoring?

Monitoring refers to check database status, settings which may be the owner’s name, file names, file sizes, schedules, etc.

Q. What are main services for SQL server?

SQL Server and SQL Server Agent

Q. What are the common trace flags used with SQL Server?

Deadlock Information: 1204, 1205, and 1222
Network Database Files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Forces uniform extent allocations instead of mixed page allocations 1118 (SQL Server 2005 and 2008) to reduce TempDB contention

Q. What are the methods to start services?

Services.msc and SQL Server Configuration Manager

Q. Can we take a backup for Resource DB?

No. The only way if we want to get a backup is by using Windows backup for option resource MDF and IDF files.

Q. What are the methods to stop services?

Services.msc, SQL Server Configuration Manager and SSMS

Q. Does SQL Server Upgrade Advisor analyze remote instances?

Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the Report Server.

Q. Why we cannot start services via SSMS?

Due to services already stopped state, we cannot access services via SSMS.

Q. How to upgrade SQL Server 2000 to SQL Server 2008?

To upgrade SQL Server 2000 to SQL Server 2008, the safest approach would be a side-by-side upgrade. We can do this either by using backup and restore or by detaching/attaching the database files. However, it is suggested to use the former as it is a safer approach. The steps are as follows:

  • Run the Upgrade Analysis tool from Microsoft. Address any issues raised there, first
  • Identify DTS packages. These must be migrated manually unless we buy Pragmatic Works
  • Rebuild the DTS packages as SSIS
  • Script out all SQL Agent jobs
  • Script out all security
  • Backup the systems and validate the backups (preferably by restoring them to another system)
  • Run the security script on the new system
  • Run restore on the new system
  • Validate the databases by running DBCC
  • Manually update all statistics
  • Run the SQL Agent script

Q. Whether can we stop SQL services without SQL Agent stop?

Agent Service is a dependent service, so it will stop first agent and then SQL services.

Q. Can you detach SQL Server 2005 database and attach it to a SQL Server 2008?

Yes. SQL Server 2005 databases are compatible with SQL Server 2008. Attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to the latter, and the database is then no longer usable by the SQL Server 2005 installation.

Q. What is Replication?

The source data will be copied to destination through replication agents (jobs). Object level technology

Q. Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005?

No. The only way to move a SQL Server 2008 database to a SQL Server 2005 is by transferring the data using methods, such as Data Transformation Services (Import/Export), SSIS, BCP, etc., or by using a query between linked servers.

NEXT PAGE >>