MSSQL Interview Questions
Where will you find the SQL Server installation related logs?
Installation related logs are stored under the shared feature directory folder which was selected at the time of first SQL Server instance installation. E.g. If Shared Features were selected to be placed on the
“C:\Program Files\Microsoft SQL Server” then logs will be created under.
What is a service account?
Based on the selected components while doing the installation we will find respective service to each component in the Windows Services. e.g. SQL Server, SQL Server Agent, SQL Analysis Services, SQL Server integration Services etc. There will be a user for each and every service through which each service will run. That use is called Service Account of that service.
Mainly we categorize the Service account as below:
Local User Account: This user account is created in the server where SQL Server is installed; this account does not have access to network resources.
Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server.
Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.
Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.
Domain Account: This account is a part of our domain that has access to network resources for which it is intended to have permission. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.
Do we need to grant Administrator permissions on the Windows server to SQL Service account to run the services or not, why?
No, it is not required. It’s not mandatory to grant Administrator permissions to the service account.
What are Shared Features Directory and its usages?
This directory contains the common files used by all instances on a single computer e.g. SSMS, sqlcmd, bcp, DTExec etc. These are installed in the folder :\Program Files\Microsoft SQL Server\110\ ,
where <drive> is the drive letter where components are installed. The default is usually drive C.
Type of Instance and maximum no. of instances which can be installed on a server.
There are two types of Instances.
Each computer can run maximum of 50 instances of the Database Engine. One instance can be the default instance.
The default instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance.
A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.
What is a collation and what is the default collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
Default collation: SQL_Latin1_General_CP1_CI_AS
What is an RTM setup of SQL Server?
RTM stands for release to manufacturing.
What is a slip stream installation and its usages?
SQL Server 2008 introduced a concept that’s called “Slipstream Installation”. This is a way to deploy a SQL Server instance with all the needed Service pack as part of the installation. Everything will be installed in one go, hence there is no need to deploy any other service packs on the installation.
What is a silent installation and how can we use this feature?
The procedure to install SQL Server instance through command line using ConfigurationFile.ini file in quite mode is known as Silent installation.
Can we change the default port of SQL Server, How?
Yes, it is possible to change the Default port on which SQL Server is listening.
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>
Step 3. Right Click on TCP/IP and select Properties
Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup. Now change the value to static value which you want to set for SQL Server port
What’s the practical approach of installing Service Pack?
Steps to install Service pack in Production environments:
– Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.
– List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.
– Install the service pack on SQL Servers.
– Verify all the SQL Services are up and running as expected.
Can we uninstall/rollback a service packs from SQL Server 2005?
- No not possible for SQL Server 2005. To rollback a SP you have to uninstall entire product and reinstall it.
- For Sql Server 2008 you can uninstall a SP from Add/Remove programs.
Is it mandatory to restart the Windows server after installing SQL server service pack?
No, it’s not mandatory to restart Windows server after installing SQL Server service pack but it is always a good practice to do so.
What is the port numbers used for SQL Server services?
The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number.
The browser service runs on port UDP 1434.
Reporting services is a web service – so it’s port 80, or 443 if it’s SSL enabled.
Analysis service is on 2382 but only if it’s a default install. Named instances get a random port number.
How to recover a database that is in suspect stage?
ALTER DATABASE test_db SET EMERGENCY
After you execute this statement SQL Server will shutdown the database and restart it without recovering it. This will allow you to view / query database objects, but the database will be in read-only mode. Any attempt to modify data will result in an error similar to the following:
16, State 1, Line 1 Could
ALTER DATABASE test SET SINGLE_USER;
DBCC CHECKDB (‘test’, REPAIR_ALLOW_DATA_LOSS)
If DBCC CHECKDB statement above succeeds the database is brought back online (but you’ll have to place it in multi-user mode before your users can connect to it). Before you turn the database over to your users you should run other statements to ensure its transactional consistency. If DBCC CHECKDB fails then there is no way to repair the database – you must restore it from a backup.
Why Shrink file/ Shrink DB/ Auto Shrink is really bad?
In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.
The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;
The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, 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)
Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation
Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased
Can we track no of transactions / inserts / updates / deletes a Day (Without using profiler)? If yes how?
You could use capture data change or change tracking.
How many datafiles I can put in Tempdb? What is the effect of adding multiple data files.
By far, the most effective configuration is to set tempdb on its own separate fast drive away from the user databases. I would set the number of files based on # of cpu’s divided by 2. So, if you have 8 cpu’s, then set 4 tempdb files. Set the tempdb large enough with 10% data growth.
I would start at a general size of 10 GB for each size. I also would not create more than 4 files for each mdf/ldf even if there were more than 8 cpu’s. you can always add more later.
Let’s say a user is performing a transaction on a clustered server and failover has occurred. What will happen to the Transaction?
If it is active/passive, there is a good chance the transaction died, but active/passive is considered by some the better as it is not as difficult to administer. I believe that is what we have on active. Still, active/active may be best depending on what the requirements are for the system.
What are Page Splits?
Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as “Page Split”.
In which Files does SQL Server Actually Store Data?
Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.
What is Collation in SQL Server?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.
What are the different types of database compression introduced in SQL Server 2008?
Row compression and Page compression.
Do you know about Resource Database?
All sys objects are physically stored in resource database and logically available on every database. Resource database can faster the service packs or upgrades.
Really does resource faster the upgrades? Can you justify?
Yes, in earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file. We are also capable of rollback the process, because it just needs to overwrite the existing with the older version resource copy.
I have my PROD sql server all system db’s are located on E drive and I need my resource db on H drive how can you move it?
No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.
Can we take the backup for Resource DB?
No way. The only way if you want to get a backup is use windows backup for option resource mdf and ldf files.
What is Fill Factor?
Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
High Availability Questions:
What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?
High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored
Database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.
What type of locking occurs during the Snapshot generation?
Locking depends on the type of replication used: In snapshot replication, the snapshot agent locks the object during the entire snapshot generation process. In transactional replication, locks are acquired initially for a very brief time and then released. Normal operations on a database can continue after that. In merge replication, no locks are acquired during the snapshot generation process.
What options are there to delete rows on the publisher and not on the subscriber?
One option is to replicate stored procedure execution instead of the actual DELETE command. You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete. Another option is to not replicate DELETE commands.
Data is not being delivered to Subscribers, what can be the possible reasons?
There are a number of possible causes for data not being delivered to Subscribers: The table is filtered, and there are no changes to deliver to a given Subscriber. One or more agents are not running or are failing with an error. Data is deleted by a trigger, or a trigger includes a ROLLBACK statement. A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created. Replication of stored procedure execution for a transactional publication produces different results at the Subscriber. The INSERT stored procedure used by a transactional article includes a condition that is not met. Data is deleted by a user, a replication script, or another application.
Explain what stored procedure sp_replcounters is used for?
Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data
Is it possible to replicate data from SQL Server to Oracle?
Yes this can be done using heterogeneous replication.
How will you monitor replication latency in transactional replication? Tracer tokens were introduced with SQL Server 2005 transactional replication as a way to monitor the latency of delivering transactions from the publisher to the distributor and from the distributor to the subscriber(s). For details, please refer tip to this tip: Monitor SQL Server replication latency using tracer tokens
Can you tell me some of the common replication DMV’s and their use? s
sys.dm_repl_articles – Contains information about each article being published. It returns data from the database being published and returns a row for each object being published in each article. sys.dm_repl_schemas – Contains information about each table and column being published. It returns data from the database being published and returns one row for each column in each object being published sys.dm_repl_traninfo – Contains information about each transaction in a transactional replication
How to determine if the data at Subscribers is synchronized with data at the Publisher?
Use validation. Validation reports on whether a given Subscriber is synchronized with the Publisher. For more information, see Validate Replicated Data. Validation does not provide information on which rows if any are not synchronized correctly, but the tablediff utility does.