March towards SQL Server : Day 21 – SQL DBA Interview Questions Answers – REPLICATION

Dear Readers,

Now as we have already crossed two third of the month and covered a lot of topics till date, Now lets get a deep insight about another very interesting feature of SQL Server, The Replication. It is simple, It is bit tough and It is complex at the same time depending on the mode of replication you are using. While it is highly useful for Reporting purpose, It can also be described as cheapest solution for high availability and disaster recovery as you can move object(Say Most important objects of your environment) from one server to another server. You don’t need to move whole database from one node to another if there are only few tables which are important from HA\DR point of view. Lets start the reading most frequently asked QA series on Replication. Here you go.

 

1) What is replication?

Replication is subset of SQL Server that can move data and database objects in an automated way from one database to another database. This allows users to work with the same data at different locations and changes that are made are transferred to keep the databases synchronized.

 

2) What are types of replication?

• Snapshot replication – As the name implies snapshot replication takes a snapshot of the published objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the subscriber each time a snapshot is applied. It is best suited for fairly static data or if it’s acceptable to have data out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected.  An example use of snapshot replication is to update a list of items that only changes periodically.


• Transactional replication – As the name implies, it replicates each transaction for the article being published. To set up transactional replication, a snapshot of the publisher or a backup is taken and applied to the subscriber to synchronize the data. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency. Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks.


• Merge replication – This is the most complex types of replication which allows changes to happen at both the publisher and subscriber.  As the name implies, changes are merged to keep data consistency and a uniform set of data. Just like transactional replication, an initial synchronization is done by applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated.  The merge agent has the capability of resolving conflicts that occur during data synchronization.  An example of using merge replication can be a store with many branches where products may be centrally stored in inventory. As the overall inventory is reduced it is propagated to the other stores to keep the databases synchronized.

3) What are various Agents of replication?

• Snapshot Agent- The Snapshot Agent is used with all types of replication. It prepares the schema and the initial bulk copy files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.


• Log Reader Agent – The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher (the Distributor can be on the same computer as the Publisher)


• Distribution Agent – The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.


• Merge Agent – The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.


• Queue Reader Agent – The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

 

 
4) Why is primary key needed in Transactional replication?

The reason is in the subscriber, rows are updated/deleted one-by-one using primary key.

For example:

If you delete 100 rows in the publisher using a single DELETE statement, in the subscriber 100 DELETE statements would be executed.

— on publisher

DELETE FROM dbo.tbAddress WHERE City = ‘LONDON’

— on subscriber

DELETE FROM dbo.tbAddress WHERE pk = @pk

5) Which all database objects can be included in replication?

Tables Partitioned Tables Stored Procedures – Definition (Transact-SQL and CLR) Stored Procedures – Execution (Transact-SQL and CLR) Views Indexed Views Indexed Views as Tables User-Defined Types (CLR) User-Defined Functions (Transact-SQL and CLR) Alias Data Types Full text indexes Schema Objects

6) What are prerequisites of transactional replication?

Primary Key:

This is a basic rule that every article should have a Primary Key to be a candidate table for Transactional Replication. Primary keys are used to maintain uniqueness of records and to maintain  referential integrity between tables, and that is why it is recommended for every article to have a primary key.

Securing snapshot folder:

Schedule:

Network bandwidth:

Enough disk space for database being published:

We need to make sureWe need to make sure that we have ample space available for the transaction log for the published database, as it will continue to grow and won’t truncate the log records until they are moved to the distribution database. Please note that even in simple recovery model, the log fle can grow large if replication breaks. That is the reason it is recommended to set T-log’s auto grow option to value “true”.  We should also make sure that the distribution database is available and  log reader agent is running.

Enough disk space for distribution database:

It is necessary to have enough disk space allocated to the distribution database. This is because the distribution database will store the transactions marked for replication until it is applied to the subscriber database within the limit of retention period of distribution (which is 72 hours by default), or it will retain the transactions until the snapshot agent re-runs and creates a new snapshot. re-runs and creates a new snapshot.

Use domain account as service account:

We should always use the domain account as a service account, so that when agents access the shared folder of snapshot fles, it won’t have any problem just because they are local to the system and do not have permission to access network share. While mentioning service account, we are asked to choose from two built-in accounts including Local System account, Network Services, and this account, wherein we have to specify the domain account on which the service account will run.

7) Difference between push and pull replication.

• Push – As the name implies, a push subscription pushes data from publisher to the subscriber. Changes can be pushed to subscribers on demand, continuously, or on a scheduled basis.


• Pull – As the name implies, a pull subscription requests changes from the Publisher.  This allows the subscriber to pull data as needed.  This is useful for disconnected machines such as notebook computers that are not always connected and when they connect they can pull the data.

8) Define Distributor, Subscriber & Publisher

Publisher

The Publisher is a server that makes data available for replication to other servers. In addition to being the server where you specify which data is to be replicated, the Publisher also detects which data has changed and maintains information about all publications at that site. Usually, any data element that is replicated has a single Publisher, even if it may be updated by several Subscribers or republished by a Subscriber. The publication database is the database on the Publisher that is the source of data and database objects to be replicated. Each database used in replication must be enabled as a publication database either through the Configure Publishing and Distribution Wizard, the Publisher and Distributor properties, by using the sp_replicationdboption system stored procedure, or by creating a publication on that database using the Create Publication Wizard.

Distributor

The Distributor is a server that contains the distribution database and stores meta data, history data, and/or transactions. The Distributor can be a separate server from the Publisher (remote Distributor), or it can be the same server as the Publisher (local Distributor). The role of the Distributor varies depending on which type of replication you implement, and in general, its role is much greater for snapshot replication and transactional replication than it is for merge replication.

Subscribers

Subscribers are servers that receive replicated data. Subscribers subscribe to publications, not to individual articles within a publication, and they subscribe only to the publications that they need, not necessarily all of the publications available on a Publisher. If you have applications using transactional replication built with Microsoft® SQL Server™ version 6.5 or later, and those applications subscribe directly to articles instead of to publications, the applications will continue to work in SQL Server 2000. However, you should begin to migrate your subscriptions to the publication level where each publication is composed of one or more articles.

9) Define Article, Publication & Subscription.

Article

An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects. When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.

Publication

A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

Subscribe

A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions: push and pull.

10) Can we add or drop a single article from a publication. If so, How?

It is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object). Add a table to a publication through the Publication Properties – <Publication> dialog box or the stored procedures sp_addarticle and sp_addmergearticle.

Remove a table from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties – <Publication> dialog box. You cannot drop articles from snapshot or transactional publications after subscriptions have been added; you must drop the subscriptions first.

11) Define sp_replcounters

Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database.

12) Can we use replication to replicate data across different RDBMS i.e. SQL to Oracle

Oracle and DB2 can subscribe to snapshot and transactional publications using push subscriptions. Subscriptions are supported for the two most recent versions of each database listed using the most recent version of the OLE DB provider listed.

However, Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and SSIS.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

 

13) Explain Latency in replication. How can you monitor Latency of particular publication

Transactional replication provides the tracer token feature, which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher, Distributor and Subscribers. A token (a small amount of data) is written to the transaction log of the publication database, marked as though it were a typical replicated transaction, and sent through the system, allowing a calculation of:

• How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor.


• How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber.

14) What permissions are needed to a user to monitor replication.

The replmonitor database role in the distribution database. These users can monitor replication, but cannot change any replication properties.

15) Name some commonly used Replication DMVs and their use.

There are four replication related DMV’s in SQL Server.

sys.dm_repl_articles

sys.dm_repl_schemas

sys.dm_repl_tranhash

sys.dm_repl_traninfo

16) What are the advantages and disadvantages of Snapshot replication over Transactional replication.

Snapshot Replication would be good to use if:

1. if you are sure that you would synchronize only once in a day and your business requirements do not include replicating transactions as and when they are comitted on the publisher

2. If the size of the replicating articles is small – may be a few MBs/GBs

3. If it is does not matter that for some time the replicating articles would be locked (till the snapshot would be generated)

Transactional Replication would be good to use if:

1. You want incremental changes to be propagated to Subscribers as they occur.

2. The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.


3. The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.

4.The Publisher has a very high volume of insert, update, and delete activity.

15) What is peer to peer replication.

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

16) What is conflict resolution in merge replication.

Merge replication allows multiple nodes to make autonomous data changes, so situations exist in which a change made at one node may conflict with a change made to the same data at another node. In other situations, the Merge Agent encounters an error such as a constraint violation and cannot propagate a change made at a particular node to another node.

The Merge Agent detects conflicts by using the lineage column of the MSmerge_contents system table; if column-level tracking is enabled for an article, the COLV1 column is also used. These columns contain metadata about when a row or column is inserted or updated, and about which nodes in a merge replication topology made changes to the row or column. You can use the system stored procedure sp_showrowreplicainfo (Transact-SQL) to view this metadata.

As the Merge Agent enumerates changes to be applied during synchronization, it compares the metadata for each row at the Publisher and Subscriber. The Merge Agent uses this metadata to determine if a row or column has changed at more than one node in the topology, which indicates a potential conflict. After a conflict is detected, the Merge Agent launches the conflict resolver specified for the article with a conflict and uses the resolver to determine the conflict winner. The winning row is applied at the Publisher and Subscriber, and the data from the losing row is written to a conflict table.

Conflicts are resolved automatically and immediately by the Merge Agent unless you have chosen interactive conflict resolution for the article.

17) What are datatype concerns in transactional replication.

Transactional replication supports publishing LOBs and performs partial updates on LOB columns: if a LOB column is updated, only the fragment of data changed is replicated, rather than all the data in the column.

If a published table includes any LOBs, consider using the following Distribution Agent parameters: -UseOledbStreaming, -OledbStreamThreshold, and -PacketSize. The most straightforward way to set these parameters is to use the Distribution Agent profile titled Distribution Profile for OLEDB streaming.

The process of replicating text, ntext and image data types in a transactional publication is subject to a number of considerations. It is recommend that you use the data types varchar(max), nvarchar(max), varbinary(max) instead of text, ntext, and image data types, respectively.

 

18) Which all SQL editions provide replication functionality

Feature Name	Enterprise	Business Intelligence	Standard	Web	Express
SQL Server change tracking	Yes	Yes	Yes	Yes	Yes
Merge replication	Yes	Yes	Yes	Yes (Subscriber only)	Yes (Subscriber only)
Transactional replication	Yes	Yes	Yes	Yes (Subscriber only)	Yes (Subscriber only)
Snapshot replication	Yes	Yes	Yes	Yes (Subscriber only	Yes (Subscriber only)
Heterogeneous subscribers	Yes	Yes	Yes
Oracle publishing	Yes
Peer to Peer transactional replication	Yes

19) Can we rename a database used in Publication or subscription.

No. we would need to drop the publications, rename the database and re-configure replication all over again.  So there is no easy way to do this.

20) Are logins and passwords replicated?

No. You could create a DTS\SSIS package to transfer logins and passwords from a Publisher to one or more Subscribers.

21) Please underline the complications involved in using replication on SQL Cluster.

No special considerations are required because all data is stored on one set of disks on the cluster.
22) Are tables locked during snapshot generation?

The length of time that the locks are taken depends on the type of replication used:

• For merge publications, the Snapshot Agent does not take any locks.


• For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.


• For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.

23) What recovery model is required on a replicated database?

 

Replication is not dependent on any particular recovery model. A database can participate in replication whether it is in simple, bulk-logged, or full. However how data is tracked for replication depends on the type of replication used.
24) Can the same objects be published in different publications?

Replication supports publishing articles in multiple publications (including republishing data) with the following restrictions:

• If an article is published in a transactional publication and a merge publication, ensure that the @published_in_tran_pub property is set to TRUE for the merge article.


• An article cannot be published in both a merge publication and a transactional publication with queued updating subscriptions.


• Articles included in transactional publications that support updating subscriptions cannot be republished.


• Transactional replication and unfiltered merge replication support publishing a table in multiple publications and then subscribing within a single table in the subscription database (commonly referred to as a roll up scenario). Roll up is often used for aggregating subsets of data from multiple locations in one table at a central Subscribe
  

25) Can multiple publications use the same distribution database?

Yes. There are no restrictions on the number or types of publications that can use the same distribution database. All publications from a given Publisher must use the same Distributor and distribution database.

If you have multiple publications, you can configure multiple distribution databases at the Distributor to ensure that the data flowing through each distribution database is from a single publication. Use the Distributor Properties dialog box or sp_adddistributiondb (Transact-SQL) to add a distribution database.

26) Does replication encrypt data?

No. Replication does not encrypt data that is stored in the database or transferred over the network.

27) What is the effect of running a bulk insert command on a replicated database?

For transactional replication, bulk inserts are tracked and replicated like other inserts. For merge replication, you must ensure that change tracking metadata is updated properly.

28) Why can’t I run TRUNCATE TABLE on a published table?

TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.

29) What is NOT FOR REPLICATION option for table constraints

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the following database objects are treated differently when a replication agent performs an operation:

• Foreign key constraints : The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.


• Check constraints : The check constraint is not enforced when a replication agent performs an insert, update, or delete operation.


• Identity columns : The identity column value is not incremented when a replication agent performs an insert operation.


• Triggers : The trigger is not executed when a replication agent performs an insert, update, or delete operation.

30) Does replication resume if a connection is dropped or do we need to reinitialize the replication?

Yes. Replication processing resumes at the point at which it left off if a connection is dropped. If you are using merge replication over an unreliable network, consider using logical records, which ensures related changes are processed as a unit.

31) How do I move or rename files for databases involved in replication?

In versions of SQL Server prior to SQL Server 2005, moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached, replication had to be removed from these databases first. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect on Replication

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information  which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

 

March towards SQL Server : Day 21 – SQL DBA Interview Questions Answers – REPLICATION

March towards SQL Server : Day 6 – SQL DBA Interview Questions Answers – All about SQL Server Services

This blog is all about the SQL Server Services which are required for the functionality of SQL Server components. It also contains Questions answers about the important parameters and SQL Server start process.

1) List out various services which are important for SQL Server and its components functionality?

• SQL Server Service


• SQL Server Agent service


• SQL Server Analysis Service


• SQL Server Browser service


• SQL Server Integration Service


• SQL Server Reporting Service


• SQL Server VSS Writer service


• Distributed Transaction Coordinator


• SQL Server Active Directory Helper


• SQL Full-Text Filter Daemon Launcher


• SQL Server Distributed Replay Client service


• SQL Server Distributed Replay Client service

2) What is SQL Server service and its importance?

SQL Server service is core of SQL Server instance. It runs the Database Engine and executes the client requests related to data processing.  If this service is not running, no users can connect to the any of the database, hence users will not be able to fetch, insert, update or delete the data.

3) What is SQL Server Agent service and its importance?

SQL Server Agent is the primary scheduling engine in SQL Server. This is used to execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch Files and Subscriptions etc. which are referred to as Jobs. It uses msdb database to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information related to Backup, Restore, Log Shipping, SSIS Packages etc.

4) What is SQL Server Analysis service and its importance?

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

5) What is SQL Server Integration service and its importance?

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

6) What is SQL Server Browser?

This service acts as a listener for the incoming requests for Microsoft SQL Server resources. It provides information about the list of installed SQL Server instances on the computer to the client computers/applications. It helps in browsing the list of servers, locating and connecting to the correct server.

This listener service responds to client requests with the names of the installed instances, and the ports or named pipes used by the instance.

7) What is SQL Server Reporting Services?

This service is primarily used by SQL Server Reporting Services (SSRS) for browsing and viewing the reports on Reports Server, through Report Server or Report Manager interface. It is used to manage the shared data sources, reports, shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting services are managed using the Reporting Services Configuration Manager.

8) What is SQL Server VSS Writer?

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.

Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.

9) Which types of backups are supported by SQL Write Service?

SQL Writer supports:

• Full database backup and restore including full-text catalogs


• Differential backup and restore


• Restore with move


• Copy-only backup


• Auto-recovery of database snapshot

10) Which types of backups are supported by SQL Write Service?

SQL Writer does not support:

•                 Log backups


•                 File and filegroup backup


•                 Page restore

11) What is Full-Text Search service?

This service is used by the full-text search feature of SQL Server. It helps in starting the filter daemon host process, which manages the full-text indexing, querying, search filtering and word processing as part of the full-text search feature.

12) What is SQL Server Active Directory Helper?

This service enables the integration with the Active Directory. Irrespective of number of instances of SQL Servers installed on a computer, there is always only one instance of SQL Server Active Directory Helper service. This service is automatically started by SQL Server when required and is stopped once the operation is completed. This service is required whenever an SQL Server object needs to be created in the Active Directory to register an instance of SQL Server.

13) What is Distributed Transaction Coordinator?

This service coordinates distributed transactions between two or more database servers. Client applications use this service to work with data from multiple sources in one transaction. There is always only one instance of MSDTC service running on a computer irrespective of how many SQL server instances are installed. This service should be running on each of the servers which handle distributed transactions. This service is not a part of SQL Server installation. This service is installed with Windows OS installation.

14) How to check how many SQL Server instances are installed on a Window Server?

There are multiple ways through which we can check the No. of SQL Server instances which are running on a server like:

• Check the SQL services for different Instances


• SQL Server Configuration Manager Start- all programs – Microsoft SQL Server 2008 R2 -> configuration tools –> Microsoft SQL Server configuration Manager

• List out SQL Services from Net Start command


• Using Powershell commands


• Readging the Registry Keys, Regedit-> HKEY_LOCAL_MACHINE–>SOFTWARE ->Microsoft ->Microsoft SQL Server –> InstalledInstance

15) What are the different ways to start and Stop SQL Server services?

There are different ways through which we can start or stop SQL Server services.

a) Go to Services –> Look for SQL server service related to  the Instance

Named Instance: SQL Server(Instance Name)

Default Instance: SQL Server(MSSQLServer)

b) Right Click on the SQL Server instance in management studio and click on restart

c) Go to SQL Server Configuration Mananger (SQLServermanager10.msc) and right click on the services and click restart.

d) Net stop command

e) use “Net START” command to list all the running services

f) Use “Net STOP MSSQL$Instancename”  to stop the SQL Service for a particular instance

16) List out the Service Display name, Service Name and Executable for SQL Serve related services?

Common Name	Service Display Name	Service Name	ExecutableName
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	SQL Server (MSSQLSERVER)	MSSQLSERVER	sqlservr.exe
	SQL Server (NamedInstance)	MSSQL$NamedInstance
SQL Server Active Directory Helper	SQL Active Directory Helper Service	MSSQLServerADHelper100	SQLADHLP.EXE
SQL Server Agent	SQL Server Agent (MSSQLSERVER)	SQLSERVERAGENT	SQLAGENT.EXE
	SQL Server Agent (NamedInstance)	SQLAgent$NamedInstance
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER)	MSSQLServerOLAPService	msmdsrv.exe
	SQL Server Analysis Services (NamedInstance)	MSOLAP$NamedInstance
SQL Server Browser	SQL Server Browser	SQLBrowser	sqlbrowser.exe
SQL Server Full Text Search	SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	MSSQLFDLauncher	fdlauncher.exe
	SQL Full-text Filter Daemon Launcher (NamedInstance)	MSSQLFDLauncher$NamedInstance
SQL Server Integration Services	SQL Server Integration Services 10.0	MsDtsServer100	MsDtsSrvr.exe
SQL Server Reporting Services	SQL Server Reporting Services (MSSQLSERVER)	ReportServer	ReportingServicesService.exe
	SQL Server Reporting Services (NamedInstance)	ReportServer$NamedInstance
SQL Server VSS Writer	SQL Server VSS Writer	SQLWriter

17) What are the default parameters of SQL Server service start up process and from where these parameters can be changed?

master database data and log file and error log files are the default parameters which are passed to SQL Server service.

-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf;

-eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG;

-lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

We can add trace flags and other parameters to the startup process from SQL Server Configuration manager.

18) How to start the SQL Server with minimal configuration?

If there are any configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f.  Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

19) How to start SQL Server with single user model?

Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

20) What are trace flags and how can we apply trace flags on a SQL Server instance?

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.

21) How the trace flags are enabled?

Trace flags are enabled at different levels.

• Global


• Session

Instance level

We can use the -T option in the startup configuration for the SQL Server Service to enable trace at instance level.

Session Level

We can use the DBCC TRACEON and DBCC TRACEOFF commands to enable it on a session level.

22) How do I know what Trace Flags are turned on at the moment?

We can use the DBCC TRACESTATUS command

23) Name some of the Important Trace flags and their functionality?

Trace Flag: 1204

This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth turning this trace flag on, only for debugging purposes.

Trace Flag: 1222

Similar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in XML format.

Trace Flag: 3226

In an environment where database backup is frequently performed, it is a good idea to turn trace flag 3226 on, to suppress an entry to the SQL Server Error Log for each backup.  This is beneficial as the bigger the log, the harder it is to find other messages.  However, you will need to ensure that none of your scripts or systems rely on the backup entry detail on the SQL Server Error Log.

24) What are the mandatory databases to bring SQL Services up?

master, model, resource db, tempdb location.

25) Which system database is associated with SQL Server agent service?

msdb

26) What is Protocol is used by SQL Server Browser service?

SQL Server Browser service uses UDP protocol.

27) Which Port no. is used by SQL Server Browser service ?

1434

28) What will happen if SQL Server Browser service is stopped?

If the SQL Server Browser service is not running, the following connections do not work:

• If we have just one instance installed on machine and it is running on default port 1433, then status of SQL Server Browser service does not make any difference in our connection parameters.


• If there are more than one instances running on the same machine, in that case either we have to start SQL Server Browser service or provide the port number along with IP (or server name) and instance name, to access any other instance than default.


• If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused.


• If SQL Server instance is configured using dynamic ports then browser service is required to connect to correct port number.


• Also our named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)

29) What is the high Level SQL Server start up process?

• The service is authenticated by verifying the credentials provided in the logon account and the service is started.


• PID is allocated at windows level


• Authentication mode details are verified i.e either MIXED or WINDOWS


• Information of the startup parameters is captured i.e mdf location of master database, SQL Server error log location and ldf file location


• Some memory and CPU settings done at windows level, this is an informational message only


• Starts the master database


• model is the next database to start


• set the port related information


• Tempdb is recreated each time when we restart sql server


• Start msdb and other user databases based on dbid

30) What is Distributed Replay?

Distributed Replay is a new functionality of Microsoft SQL Server 2012. It helps you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL Server tunings by replaying a trace captured from a productive SQL Server environment to a new SQL Server test environment.

This new functionality is similar to SQL Server Profiler, but with more possibilities: e. g. replaying the trace from multiple clients (up to sixteen), use a stress or synchronization mode, configure options like think time, or connect time etc.

31) What are the various components involved in Distributed Replay Concepts?

The following components make up the Distributed Replay environment:

Distributed Replay administration tool: A console application, DReplay.exe, used to communicate with the distributed replay controller. Use the administration tool to control the distributed replay.

Distributed Replay controller: A computer running the Windows service named SQL Server Distributed Replay controller. The Distributed Replay controller orchestrates the actions of the distributed replay clients. There can only be one controller instance in each Distributed Replay environment.

Distributed Replay clients: One or more computers (physical or virtual) running the Windows service named SQL Server Distributed Replay client. The Distributed Replay clients work together to simulate workloads against an instance of SQL Server. There can be one or more clients in each Distributed Replay environment.

Target server: An instance of SQL Server that the Distributed Replay clients can use to replay trace data. We recommend that the target server be located in a test environment.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.mssqltips.com/sqlservertip/2622/windows-services-for-sql-server/

https://msdn.microsoft.com/en-us/library/hh510203.aspx

https://technet.microsoft.com/en-us/library/ms175609(v=sql.90).aspx

http://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/

March towards SQL Server : Day 6 – SQL DBA Interview Questions Answers – All about SQL Server Services

March towards SQL Server : Day 5 – SQL DBA Interview Questions Answers – Data File and Transaction Log File Architecture

This Blog illustrates about the most frequent questions which are asked about the Data file and Transaction Log file architecture. Intentionally I did not add any troubleshooting questionnaire in this blog because I will post a separate Questions\Answers blog for the same.

1) Explain the SQL Server Database Data File structure?

SQL server divides the data file into 8 KB pages and page is the smallest unit of any IO operation. SQL server refer the first page in the data file as page number 0.In all the data file first 9  pages (till page number 8) are in same order as shown below. In the primary data file, the 10th page (Page number 9) will be the boot page which stores the metadata about the database.

2) What is a Data Page?

Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data.

3) What is an Index Page?

Contains information related to Indexes. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.

4) What is a Page Header?

Page number 0- the file header (page type 15). It holds the file header information. There will be only one header page for each data file and that reside in the 0th position. The header page store the file level information like file size, minimum size, max size and file growth.

5) What is PFS?

PFS page is the second page (Page Number 1) in the data file followed by file header (Page Number 0). PFS pages are used to track page level allocation. PFS page repeat after every 8088 pages

6) What is a GAM Page?

GAM pages records what extents have been allocated for any use. GAM has bit for every extent. If the bit is 1, the corresponding extent is free, if the bit is 0, the corresponding extent is in use as uniform or mixed extent.

7) How many GAM pages will be there in a 7GB data file?

A GAM page can hold information of around 64000 extents. That is, a GAM page can hold information of (64000X8X8)/1024 = 4000 MB approximately. In short, a data file of size 7 GB will have two GAM pages.

8) What is SGAM page?

SGAM (Shared Global Allocation Map) page (Page type 8). SGAM pages record what extents are currently being used as mixed extent and also have at least one unused page. SGAM has bit for every extent. If the bit is 1, the corresponding extent is used as a mixed extent and has at least one page free to allocate. If the bit is 0, the extent is either not used as a mixed extent or it is mixed extent and with all its pages being used.

9) How many SGAM pages will be there in a 7GB data file?

A SGAM page can hold information of 64000 extents. That is, a SGAM page can hold information of (64000X8X8)/1024 = 4000 MB. In short, a data file of size 7 GB will have two SGAM page.

10) What is a DCM and its significance?

SQL Server uses DCM pages to track extent modified after the last full backup operation. DCM pages track the extents modified after the full backup.DCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last full backup, if the bit is 0, there is no change in the corresponding extent after the last full backup

11) How SQL Server engine will decide which extents need to be added into a differential backup?

A differential backup process scans through DCP page to identify the extents which are modified after the last full backup and add those extents in the Differential backup.

12) What is a BCM and its significance?

This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. If the bit for an extent is 1, the extent has been modified by a bulk logged operation after the last BACKUP LOG statement. If the bit is 0, the extent has not been modified by bulk logged operations.

Although BCM pages appear in all databases, they are only relevant when the database is using the bulk-logged recovery model. In this recovery model, when a BACKUP LOG is performed, the backup process scans the BCMs for extents that have been modified. It then includes those extents in the log backup.

13) What is Boot Page?

There is one special data page that exists only once per database. It is the database boot page. The database boot page is always stored in page 9 of file 1, the first file in the primary file group.

The database boot page contains a broad variety of data about the database itself. Here you can find the internal database version, the compatibility level, the database name and the date of the last log backup.

14) How many types of Allocation units are there?

There are three types of allocation units.

IN_ROW_DATA (default): If a table is relatively simple in desin (meaning record size is less than 8000 and no LOB data types are present), all records are stored in a pages refered as IN_ROW_DATA pages.

ROW_OVERFLOW_DATA: assume that a table is created with record size 12000 bytes having 4 varchar data types of size 4000 bytes. Whenever user inserts a record with size greater than 8000 (page size is 8K), then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms, ROW_OVERFLOW_DATA pages will come in  to picture only when the row size exceed page maximum limit.

LOB_DATA: LOB data like text not stored along with data pages. LOB data is stored in pecial pages called LOB_DATA pages. 16 byte pointer in data page will be used to refer LOB_DATA page.

15) What is an IAM page?

IAM stands for Index Allocation Map: To catalog all the pages that belong to a single allocation unit, SQL Server utilizes a special type of pages, the Index Allocation Map or IAM pages. Each allocation unit has at least one IAM Page; depending on the size of the table there might be many. The page type of an IAM page is 10.

16) What is a ROW OFFSET and its importance?

Page header stored system information about the page. After the page header data row is stored serially. Row offset information is stored at the end of the page, after the data rows. Every data row has a row offset. Row offset stores information about how far the row is from the start of the page.

17) What are LOB pages?

SQL Server stores all data in 8192-byte sized blocks called pages. Several types of pages are in use within a typical database. One particularly interesting group is formed by the type-3 pages or Large Object Pages.

 

LOB_DATA Allocation Units

Most data types in SQL Server take up no more than 8000 bytes of storage. However, there are a few data types, which allow for larger pieces of information to be stored. Examples include the VARCHAR(MAX), VARBINARY(MAX) or XML data types.

Normal data pages that belong to a table are grouped in IN_ROW_DATA allocation units. However, if a value that is larger than 8000 bytes needs to be stored, SQL Server does not attempt to store it in those data pages anymore. It does not even store those values in the same allocation unit. Instead, Large Object data or LOB data is stored in special LOB_DATA allocation units.

 

18) What is a PAGE SPLIT?

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows.

19) How Page Splits impacts the database performance?

Too many page splits can decrease the performance of the SQL server because of the large number of I/O operations.

 

Following remedies can be taken to avoid too many page splits:-

• Rebuild indexes frequently to empty the fill factor space for more data.


• Increased the Fill factor value after observing the page split behavior

20) How can we monitor or detect Page Splits?

Number of page splits can be observed by using the Performance Monitor and watch the SQLServer:Access Methods:Page Splits/sec counter

21) What is a Fill Factor?

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

22) What is a Logical Read?

Logical Reads:

Logical read indicates total number of data pages that are found in the data cache and read from Data cache without reading anything from Disk.

23) What is a Physical read?

Physical Reads

Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

24) How can we view the content of a page?

To see the row data stored in that page , we have to use the DBCC PAGE command. The syntax of DBCC PAGE

dbcc page ( dbid, filenum, pagenum [, printopt=3 ]);

Print opt:

0 – print just the page header

1 – page header plus per-row hex dumps and a dump of the page slot array

2 – page header plus whole page hex dump

3 – page header plus detailed per-row interpretation

DBCC TRACEON(3604)
 GO
 DBCC page("test",1,114,3)

 

25) What is a Transaction log file?

A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.  In the event of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server. On restarting the server, database goes through the recovery process.

During this recovery process, the transaction log is used to make sure that all committed transactions are written to respective data file (rolled forward) and rollback the uncommitted transaction.

 

Logically transaction log is a set of log records. Each record is identified by a log sequence number (LSN). The new log records are always written at the logical end of log file with a LSN which is greater than the previous one

 

26) What are Virtual Log files? How these are created?

SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

27) How VLFs are created SQL Server in a Transaction log file?

SQL server decides the size and number of VLF in a log file based on the size of the log file growth as given below.

Growth upto 64 MB        = 4 VLF

From 64 MB to 1 GB       = 8 VLF

Larger than 1 GB             = 16 VLF

28) What is the way to check Virtual Log file details?

DBCC loginfo(‘mydb’)

29) What is Log Sequence No?

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

30) What is a Log Truncation?

Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. However, before the log can be truncated, a checkpoint operation must occur. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation.

 

31) How Does Log Truncation occurs?

Log truncation occurs automatically after the following events, except when delayed for some reason:

• Under the simple recovery model, after a checkpoint.


• Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

 

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.practicalsqldba.com/2013/09/sql-server-data-file-structure.html

http://sqlity.net/en/2315/index-allocation-map/

http://www.dotnetinterviewquestions.in/article_sql-server-interview-questions:-what-are-page-extents-page-header-and-row-offset-in-sql-server_122.html

http://careerride.com/SQL-Server-what-is-page-splits.aspx

 

March towards SQL Server : Day 5 – SQL DBA Interview Questions Answers – Data File and Transaction Log File Architecture

March towards SQL Server : Day 4 – SQL DBA Interview Questions Answers - Database Architecture and Database Properties

This article comprises of Questions answers series on Database Architecture and various Database Properties. Intentionally I skipped Database Recovery Models, Backups, Restore, Data File and Transaction Log Architecture Questions from this series because I will cover them in a complete separate series on Database Backup, restores and File Architectures.

1) How many types of files are there in a SQL Server database?

SQL Server databases have three types of files:

• Primary data files


• Secondary data files


• Transaction Log files

2) Explain each type of database files?

Primary data files

The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is.mdf.

Secondary data files

Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.

Transaction Log file

This file holds all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

3) What is the major difference between Primary data files and secondary data files?

Primary data file contains system objects where as secondary data files contains all user defined Database objects if these are not part of the Primary File group.

One of the important difference between Primary and Secondary data files is BOOT PAGE. Page Number 9 is the boot page Page type 13). Boot page is available as 9th page only in the primary data file.

4) How many maximum files can be added to a database?

32,767

5) What are file groups and Type of File Groups?

Database File groups: Database objects and files can be grouped together in file groups for allocation and administration purposes. There are two types of file groups:

Primary: The primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group.

User-defined: User-defined file groups are any file groups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

6) What is the default File Group in a database?

Primary File group

7) Is it possible to change the Default file group from Primary to some other user defined file group? What is the benefit of this?

Yes it is possible to change the Default File group to user defined File group. All the newly created objects will be created in User defined File group be default.

8) How many Maximum File groups can be added in a database?

32,767

9) Is it possible to add Transaction Log file in a file group?

Log files are never part of a filegroup. Log space is managed separately from data space.

10) What is the use of having multiple File Groups?

Below are the major benefits which can be achieved using multiple data files and placing these files in separate file groups on separate disk drives.

1. Disk I\O Performance


2. Easy Management and Archiving of the data


3. Benefit of doing File Group level Backups and restores


4. Usage of File Groups in Portioning of the tables

11) Is there any benefit to add multiple log files? Yes/No, Why?

No, there is no benefit of adding multiple log files in a database as the write operations in a Transaction log files are always serial.

12) Will the below script work if yes, how?

Create database Test;

Yes, this script will work because rest of the parameters will be taken from model database and Files will be located to the respective folders which are set at the SQL Server instance level.

13) What is Database Growth and what settings are available in a database?

Each database file that is associated with your database has an auto-growth setting. There are three different settings you can use to identify how your database files will grow. They can grow by a specific size, a percentage of the current size, or not grow at all. Additionally you can set your files to unrestricted growth, which means they will keep growing as they need more space or you run out of disk space. Or you can restrict the growth of a database file to grow no larger than a specified size. Each one of these different auto-grow setting have defaults, or you can set them for each database file.

 14) What are the recommended settings for transaction Log File for file growth?

If you are required to set the setting for Auto growth of Transaction log file, it should always be in a specific size instead of percentage.

15) What is a compatibility level of a database?

Compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server. The default compatibility level is 110. Databases created in SQL Server 2012 are set to this level unless the model database has a lower compatibility level.

16) How to change the Compatibility level of a database?

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 110 

‘90 = SQL Server 2005

100 = SQL Server 2008 and SQL Server 2008 R2

110 = SQL Server 2012

17) What’s the difference between database version and database compatibility level?

Database version

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to.

USE master;
GO
SELECT DatabaseProperty ("dbccpagetest", "version");
GO

Database compatibility level

The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use ‘*=’ and ‘=*’

SELECT name AS "DB Name", compatibility_level AS "Compatibility Level"
FROM master.sys.databases; 
GO

18) What is a Page Verify option in a database?

When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.

19) What are the different Database states in SQL server instance?

State	Definition
ONLINE	Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
OFFLINE	Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING	One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
RECOVERING	Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
RECOVERY PENDING	SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
SUSPECT	At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
EMERGENCY	User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

 

20) How many databases can be created in SQL server instance?

32,767

21) What is auto close option?

AUTO CLOSE option:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

 When set to OFF, the database remains open after the last user exits.

22) What is auto shrink option?

AUTO SHRINK option:

When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up.

When set to OFF, database files are not automatically shrunk during periodic checks for unused space.

23) What is page?

Page is the smallest unit of storage in SQL Server database, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page

24) What is an extent?

An extent is a collection of eight physically contiguous pages.

 

25) Types of extent?

Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

26) What is the difference between single user and restricted user and restricted user option?

This option controls who and how many users can connect to a database.

When SINGLE_USER is specified, one user at a time is allowed to connect to the database. All other user connections are broken.

When RESTRICTED_USER is specified, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.

When MULTI_USER is specified, all users that have the appropriate permissions to connect to the database are allowed.

27) What is a logical File and physical File name?

Each data and transaction log file in a SQL Server database has two names:

logical_file_name

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

28) What is ROW_OVERFLOW_DATA? How does it work?

ROW_OVERFLOW_DATA : Assume that a table is created with record size 12000 bytes having 4 varchar data types of size 4000 bytes. Whenever user inserts a record with size greater than 8000 (page size is 8K), then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms, ROW_OVERFLOW_DATA pages will come in to picture only when the row size exceed page maximum limit.

29) How can we check the allocation unit of objects?

DMV sys.system_internals_allocation_units

30) What is trustworthy property of a database?

Trustworthy property :When ON, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

When OFF is specified, in an impersonation context cannot access resources outside the database.

TRUSTWORTHY is set to OFF whenever the database is attached.

31) What is Instant file initialization?

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

• Create a database.


• Add files, log or data, to an existing database.


• Increase the size of an existing file (including autogrow operations).


• Restore a database or file group.


• File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/04/26/what-s-the-difference-between-database-version-and-database-compatibility-level.aspx

http://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/

https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

March towards SQL Server : Day 4 – SQL DBA Interview Questions Answers - Database Architecture and Database Properties

March towards SQL Server : Day 2 – SQL DBA Interview Questions Answers - Security Permissions

1) What is Authentication and Authorization? What is the difference between both?

Authentication is the process of verifying who you are. Logging on to a PC with a username and password is authentication.

Authorization is the process of verifying that you have access to something. Authorization is gaining access to a resource (e.g. directory on a hard disk) because the permissions configured on it allow you to access it.

2) How many type of SQL Server authentication mode supported by SQL Server 2012?

There are two type of authentication available in SQL Server.
Windows Authentication — TRUSTED connection

Windows Logins

Windows Groups
MIXED authentication — NON Trusted connection

Windows Logins

Windows Groups

SQL Server logins

3) What’s the difference between Windows and Mixed mode?

Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.

Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server

4) Being a DBA which authentication mode you will prefer if you are asked to give an advice for a new Application?

Windows authentication is definitely more secure as it’s controlled and authenticated by Active Directory policies.

5) What are Principals?

Principals are entities that can request SQL Server resources. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID). e.g.
Windows-level principals

• Windows Domain Login

• Windows Local Login
SQL Server-level principals

• SQL Server Login

• Server Role
Database-level principals

• Database User

• Database Role

• Application Role

6) What is a Securable?

Securables are the resources to which the SQL Server Database Engine authorization system regulates access. For example, a table is a securable. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured. The securable scopes are server, database, and schema.

7) Explain scope of securable on Server, Database and Schema level?
Securable scope: Server –The server securable scope contains the following securables:

• Endpoint

• Login

• Server role

• Database
Securable scope: Database –The database securable scope contains the following securables:

• User

• Database role

• Application role

• Assembly

• Message type

• Route

• Service

• Remote Service Binding

• Full text catalog

• Certificate

• Asymmetric key

• Symmetric key

• Contract

• Schema
Securable scope: Schema –The schema securable scope contains the following securables:

• Type

• XML schema collection

• Object – The object class has the following members:

o Aggregate

o Function

o Procedure

o Queue

o Synonym

o Table

o View

8) What are logins and users and its difference?

A login is the principal that is used to connect to the SQL Server instance. A user is the principal that is used to connect to a database.

The security context on the instance itself is dictated by the login, it’s roles and the permissions granted/denied. The security context on the database is dictated by the user, it’s roles and the permissions granted/denied.

9) What is a schema?

SQL Server 2005 introduced the concept of database schemas and the separation between database objects and ownership by users. An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects. schema as a collection of database objects that are owned by a single principal and form a single namespace

10) What are Fixed Server roles and importance?
Bulk Admin: Members of this role can perform Bulk Insert operations on all the databases.
DBCreator: Members of this role can Create/Alter/Drop/Restore a database.
Disk Admin: Members can manage disk files for the server and all databases. They can handle backup devices.
Process Admin: Members of this role can manage and terminate the processes on the SQL Server.
Server Admin: Members of this role can change Server-wide configurations and shutdown SQL Server instance.
Setup Admin: Members of this role can Add/Remove Linked Servers.
Security Admin: Members of this role can create/manage Logins, including changing and resetting passwords as needed, and managing GRANT, REVOKE and DENY permissions at the server and database levels.
SysAdmin: Members of this role have Full Control on the instance and can perform any task.
Public: Public is another role just like Fixed Server Roles, that is by default granted to every login (Windows/SQL)

11) What are “View Server State”,”VIEW DATABASE STATE” permissions meant for?

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

There are two types of dynamic management views and functions:

Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.

Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

12) What are “View Definition” permissions?

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

The VIEW DEFINITION permission can be granted on the following levels:

• Server scope

• Database scope

• Schema scope

• Individual entities

13) What is a guest account?

Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission. The recommendation is not valid for master, msdb and tempdb system databases. If Guest user is disabled in msdb system database, it may cause some issues. Distribution database is also system database and more information about the Guest User in distribution database can be found below. It is recommended to disable guest user in every database as a best practice for securing the SQL Server.

14) Is it possible to create new User Defined Server role in 2012 or not?

Yes, it is possible to create a Server role in SQL Server 2012.

15) What are the security related catalog views?
Server-Level Views

sys.server_permissions

sys.sql_logins

sys.server_principals

sys.server_role_members
Database-Level Views

sys.database_permissions

sys.database_role_members

sys.database_principals

16) What are the extra DB roles available in msdb?

SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

• SQLAgentUserRole

• SQLAgentReaderRole

• SQLAgentOperatorRole

17) Which one is highest privileged role out of SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole?

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole andSQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the serve.

18) What are Fixed Database Roles?
db_datareader: The db_datareader role has the ability to run a SELECT statement against any table or view in the database.
db_datawriter: The db_datawriter role has the ability to modify via INSERT, UPDATE, or DELETE data in any table or view in the database.
db_denydatareader: The db_denydatareader role is the exact opposite of the db_datareader role: instead of granting SELECT permissions on any database object, the db_denydatareader denies SELECT permissions.
db_denydatawriter: db_denydatawriter role serves to restrict permissions on a given database. With this role, the user is preventing from modifying the data on any data via an INSERT, UPDATE, or DELETE statement
db_accessadmin: The db_accessadmin fixed database role is akin to the securityadmin fixed server role: it has the ability to add and remove users to the database.

The db_accessadmin role does not, however, have the ability to create or remove database roles, nor does it have the ability to manage permissions.

Granted with GRANT option: CONNECT
db_securityadmin: The db_securityadmin role has rights to handle all permissions within a database. The full list is:

DENY, GRANT, REVOKE, sp_addapprole, sp_addgroup, sp_addrole, sp_addrolemember, sp_approlepassword, sp_changegroup, sp_changeobjectowner, sp_dropapprole, sp_dropgroup, sp_droprole, sp_droprolemember

The list includes the DENY, GRANT, and REVOKE commands along with all the store procedures for managing roles.
db_ddladmin: A user with the db_ddladmin fixed database role has rights to issue Data Definition Language (DDL) statements in order to CREATE, DROP, or ALTER objects in the database.
db_backupoperator: db_backupoperator has rights to create backups of a database. Restore permissions are not granted, but only backups can be performed.
db_owner: Equal to a sysadmin at instance level, DB_OWNER can perform any task at DB Level.
public: By default all the users in database level are granted Public Role.

19) What is the purpose of db_denydatawriter and db_denydatareader?

The deny roles are an extra safeguard that you can use to make sure that certain logins or groups will never have the type of access that is specifically denied under the role. With nesting of groups in Windows and multiple role assignments sometimes individuals inadvertently end up with excessive permissions. This is just another level that can be applied to lessen these accidental cracks in the permissions hierarchy.

20) What are Application Roles?

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.

21) What are Orphaned Users?

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.

• A database user can become orphaned if the corresponding SQL Server login is dropped.

• A database user can become orphaned after a database is restored or attached to a different instance of SQL Server.

• Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

22) How to troubleshoot issues with the Orphaned users?

This will lists the orphaned users:

 EXEC sp_change_users_login "Report"

If you already have a login id and password for this user, fix it by doing:

 EXEC sp_change_users_login "Auto_Fix", "user"

If you want to create a new login id and password for this user, fix it by doing:

 EXEC sp_change_users_login "Auto_Fix", "user", "login", "password"

 

23) How can SQL Server instances be hidden?

To hide an instance of the SQL Server Database Engine

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-clickProtocols for , and then selectProperties.

2. On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box. The change takes effect immediately for new connections.

24) Being a DBA what all measures you will follow to make SQL SERVER more secure?

• When possible, use Windows Authentication logins instead of SQL Server logins

• Using server, database and application roles to control access to the data

• Using an un guessable SA password

• If possible, disable and rename the sa account

• Restricting physical access to the SQL Server

• Disabling the Guest account

• Minimize the number of sysadmins allowed to access SQL Server.

• Give users the least amount of permissions they need to perform their job.

• Use stored procedures or views to allow users to access data instead of letting them directly access tables.

• Don’t grant permissions to the public database role.

• Remove user login IDs who no longer need access to SQL Server.

• Avoid creating network shares on any SQL Server.

• Turn on login auditing so you can see who has succeeded, and failed, to login.

• Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.

• Do not use DBO users as application logins

• Firewall restrictions ensure that only the SQL Server listening port is available on the database server.

• Apply the latest security updates / patches

25) What is Transparent Data Encryption?

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

26) What is Service master key?

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

27) What are the types of keys used in encryption?

Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.

Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. This intricacy has turned it into a resource-intensive process.

28) How to take backup of the Service master key?

 BACKUP SERVICE MASTER KEY TO FILE = "path_to_file"
 ENCRYPTION BY PASSWORD = "password"

29) Is it possible to disable, how?

Disable the SA Login

Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can’t use the SA account in an emergency. we can use the below T-SQL to disable SA account.

–Query to disable the SA account.

 ALTER LOGIN sa DISABLE;

30) Is it possible to Rename the SA Login

Yes we can rename the SA account which will prevent hackers/users to some extent.

–Query to check account status

 ALTER LOGIN sa WITH NAME = [newname];

31) Define SQL Server Surface Area Configuration Tool

SQL Server 2005 contains configuration tools such as a system stored procedure calledsp_configure or SQL Server Surface Area Configuration tool (for services and features) in order to enable/disable optional features as needed. Those features are usually installed as disabled by default. Here is the list of the features that can be enabled using the tool:

• xp_cmdshell

• SQL Server Web Assistant

• CLR Integration

• Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)

• OLE Automation system procedures

• System procedures for Database Mail and SQL Mail

• Remote use of a dedicated administrator connection

March towards SQL Server : Day 2 – SQL DBA Interview Questions Answers - Security Permissions