Interview Q and A for SQL Server Part - 2

101. Can we perform a SQL Server compressed backup?
Starting SQL Server 2008, we can perform a compressed backups. All types of backups can be performed with compressed option. Prior to SQL Server 2008, backups may need to be compressed using third party tools and but need to regularly test to make sure that the backups are be uncompressed and restored successfully.

102. How can you automate performing database backups in SQL Server?
We can create maintenance plans and use backup tasks option and schedule the maintenance plan to perform backups automatically. We can also create SQL Server jobs and specify the backups commands or create a Stored procedure to perform backups of databases on certain conditions and create jobs and add job step to to run the stored procedure and then schedule the job. Also, there are many third party tools available to automate performing database backup and restores.

103. What are different types of Backups in SQL Server?
In all versions of SQL Server, below are the type of backups that can be performed.

SQL Server FULL Database Backups – A Full database backup contains the whole database at the time the backup finished. These backups contains all the data in the databases and active portion of the transaction log file too.

SQL Server Differential Backups – Differential Backups contains the data extents which has been changed after a Full Backup.

SQL Server Transaction Log Backup – Transaction Log backups can only be performed on databases whose recovery model is set to either FULL or BULK-LOGGED. Transaction log backups consists of log records inside a transaction log file(.ldf), since last log backup. This allows a databases to be recovered to a point in time, incase of a failure.

SQL Server File Backups – File Backups in SQL Server allows to backup one or more important database files, rather than backing up entire database. There are various rules in place on how you can perform a File backup and how to restore a file backups.

SQL Server Filegroup Backups – Filegroup Backups in SQL Server allows us to backup one or more FILEGROUPS, which contains one or more database files. By default each database has one FileGroup which is PRIMARY filegroup and we can create additional filegroups and add one or more data files to it. If there are Read_Only filegroups, then one can just backup any Read_Write Filegroups instead of backing up the entire database.

SQL Server PARTIAL backups – PARTIAL backups in SQL Server was introduced with SQL Server 2005 which allows us to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files. This is a good option when there are Read-Only filegroups in the database and we do not want to backup the entire database all of the time. PARTIAL backups can be performed for only a Full or Differential backup, but cannot be used with Transaction Log backups.

SQL Server Copy-Only backups – Copy-Only backups are special purpose backups which do not disturb regular backup sequence. Usually performing different types of backup affect the way the database is restored, if you do not want a certain backup to change or disturb a restore sequence, then a Copy-Only backup needs to be performed. A copy-only backup cannot be used as a differential base later. Copy-Only can be used with FULL, Differential and Transaction Log backups.

104. What are the differences between In-Place Upgrade and Side-By-Side Upgrade in SQL Server 2008 R2?

– In In-Place Upgrade, instance name does not change, so no need to change the connection string, but in side-by-side upgrade, instance name will be different if new instance is installed on same server, if installed on other server, then the server name will change and will result in requirement to change to the connection string.

– In-Place upgrade has risk or additional down time in case the upgrade fails which ends up with cleanup and reinstalling everything clean and during this entire process, there will be huge amount of downtime required. In side-by-side upgrade, we are installing a new instance or even on a new server, so any failures will not affect the existing SQL instance, which will continue to server the clients.

– Side-by-side migration has lot of addition tasks like backup and restore of user databases on new instance, create logins, fix orphan users, configure SQL Server settings appropriately, Create all the required jobs, etc. In-Place upgrade does not require much changes as everything will be migrated and readily available to use.

– Rollback of SQL Server instance in in-place method is not possible, but is fully possible in side-by-side upgrade.

– Amount of downtime is more with in-place upgrade compared to side-by-side upgrade when planned properly.

105. What are ways of migrating SQL Server from lower version to higher version?
If you want to upgrade a SQL Server instance from SQL Server 2008 R2 to SQL Server 2012, below are the different ways you can do this migration.

1. In-Place Upgrade – In this method, existing instance of SQL Server will be upgraded to higher version, thus we end up with one instance of SQL Server with higher version i.e., SQL Server 2012. Here the instance name remains same, so application connection string remains the same, only change that may be required is to have latest connectivity drivers installed.

2. Side-By-Side Upgrade – In this method a new instance of SQL Server 2012 is installed on same server or a different server and them all User databases, Logins, Jobs, configuration settings need to be configured or created on the new SQL Server instance.

106. Where can I find the SQL Server Setup logs to troubleshoot any setup failures?
Setup logs can be found from C:\programfiles\Microsoft SQL Server\110\Setup Bootstrap\Log\Folder with time stamp with latest datetime. change 110 to 100 for SQL Server 2008 R2, 90 for SQL Server 2005, 120 for SQL Server 2014.

There can be two summary files, one for main setup work flow and other for component update. There is file with name detail.txt which has all the informational, warning and error messages related to setup, this file mostly points to the exact exception or error which caused the setup failure.

Reviewing summary and details.txt should help in identifying where exactly was the problem.

107. What are the pre-requisites before installing a service pack or Cumulative Updates?
On critical servers, it is important to make sure to follow all the pre-requisites before installing service pack or Cumulative Updates, so that there are no issues after patching the critical production servers.

– Install the service pack or CU on test server with similar setup
– Check for any errors in SQL errorlogs or Eventlogs
– Test the application thoroughly to make sure it works without any issues.
– Document and Test the Rollback plan on test server to make sure that we can rollback successfully incase of any issues after applying the patches.
– Backup all System and User databases and verify that they can be restored.
– Install the service pack or CU on production servers.
– Checked SQL errorlog and eventlog and make sure there are no errors.
– Test the application thoroughly.

108. How do you install Service Packs or CU on SQL Server 2012 instances with AlwaysON Availability Group databases?
With AlwaysON Availability Group databases, we can install service packs or CUs with minimal downtime to the end users, but there can be impact if secondary replicas are used for reporting purposes. Below are the steps to install Service Packs or CU on SQL Server 2012 instances with AlwaysON Availability Group databases.

– Make sure that the AlwaysON Availability Group is running on one node, which will be the active node.
– Backup all the System and User databases.
– Install the service pack or CU on the secondary replica.
– Test the secondary replica, by checking Errorlogs and eventlogs to make sure there are no failures or errors.
– Failover AlwaysON Availability Group to secondary replica which will now become new primary replica.
– Backup all system databases.
– Install the service pack or CU on the new secondary replica.
– Test the new secondary replica, by checking Errorlogs and eventlogs to make sure there are no failures or errors.
– Failover AlwaysON Availability Group to the secondary server which will now become the primary server.
– Verify and Test the application.

109. What is Slip-stream installation of SQL Server?
There were various bugs which caused the failure of SQL Server 2008 and SQL Server 2008 R2 installations, so Microsoft has created some fixes to avoid the failures. But the setup media which was already released does not have these fixes, so a procedure called slip-stream was introduced where these fixes are merged with the main SQL Server setup media to avoid any known SQL Server setup failure issues. There are two ways of performing slip-stream, one is installing setup support files from SP1 or SP2 and then install the SQL Server 2008 R2, second method is to merge some of the setup files from SP1 or SP2 with the SQL Server 2008 or R2 media files and then running the install which will install SQL Server 2008 or SQL Server 2008 R2 plus the service pack 1 or Service Pack 2.

110. Can a Service Pack or Cumulative Update be uninstalled to rolled back in case of failures?
We cannot uninstall a service pack or Cumulative Update on SQL Server 2005 or lower, but starting with SQL Server 2008, we can uninstall a service pack or Cumulative Update from control panel add or remove programs – view installed updates window. To rollback a service pack or CU update on SQL Server 2005 or lower, we need to completely uninstall SQL Server and reinstall SQL Server 2005 to same build where it was before applying SP4, also need to have it installed with same collation as it was before. On SQL Server 2005 is installed and brought up to same build as it was before, replace the .mdf and .ldf files of all the system databases or be restoring the backups of all the system databases.

111. How to apply service pack to SQL Server in cluster environment in SQL Server 2008 R2?
– First need to test applying the service pack on a test server to make sure that the application does not break after applying the service pack or cumulative update.
– On a two node cluster, make sure SQL Server instance and MSDTC and Cluster groups are all on one node of the cluster, which will become the active node.
– Perform backups of System and user databases.
– Remove the passive node from the SQL Server resource possible owners list.
– Install the service pack on the passive node.
– Add the passive node back to the SQL Server resource possible owners list.
– Failover SQL Server instance to node where we applied the Service Pack.
– Check Errorlogs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL errorlog.
– Remove the new passive node from the SQL Server resource possible owners list.
– Install the service pack on the new passive node.
– Add the passive node back to the SQL Server resource possible owners list.
– Failover SQL Server instance to the newly upgraded node.
– Check Errorlogs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL errorlog.
– Test the application.

Even skipping the steps of removing and adding the node name from possible owners from SQL Server resource properties, should be fine and is done that way by most of the DBA’s, but above is the recommended approach.

112. How do you determine whether to apply latest Service Packs or Cumulative Updates?
Some of the common reasons for installing Service Packs or Cumulative Updates.

– If SQL Server is experiencing any known issue and it is found that the issue was fixed on a particular Service Pack or CU, then need to test the patch by applying on a test server and if application is working fine, then can go ahead and install the patches on production server.
– Security Updates are releases when some vulnerability is identified with the product, so need to apply these as soon as it is available.
– Service Packs can be applied as they are more safe than Cumulative updates. In general after a service pack is released, CU1 for that service pack will be released very soon, so good practice to apply a service pack as soon as it is available and then also install the CU1. Of-course, Service Pack should be first installed on Test server and application should be tested thoroughly to make sure it works with out any problems.
– It is always good to be on latest build of SQL Server to avoid any known issues before they cause production issues. Quarterly patching of SQL Servers should be good.

113. What are the things to be considered to install new SQL Server 2012 instance?
Note: Question can be asked related to different versions of SQL Server, but general steps will be applicable for during installation of all versions of SQL Server.

– Prepare documentation and implement the required Hardware and Software which includes Operating System, OS patches, features like clustering, .Net framework, etc.
– Study and document the SQL Server features which need to be implemented. Example, to use AlwaysON, need to have windows clustering feature enabled and other requirements need to be understood and documented.
– Study, document and implement all the pre-requisites required for the installation of SQL Server.
– Install SQL Server version.
– Apply latest Service Packs or Cumulative Updates or Security Updates.
– Check the Setup logs and Event logs and make sure there are no errors related to OS or SQL Server.
– Test and make sure you can connect to SQL Server from remote systems and all features which are installed are working properly.

114. Unable to view Cluster Shared Volumes (CSV) from SQL Server Management Studio (SSMS) while trying to perform Backup or Restore?
This ideally should work, where one should be able to view the Cluster Shared Volumes (CSV) from SQL Server Management Studio (SSMS) backup or restore wizards. This has been notified as a bug to the Microsoft Product team and most likely a fix would be released, so make sure that SQL Server 2014 instance is patched with latest updates and test the backup and restores

115. Receiving error during the installation of SQL Server while using the Cluster Shared Volumes (CSV)?
There are some people who received various errors while they try to specify the data directories path to Cluster Shared Volumes (CSV) and may receive errors like ” The volume that contains the SQL server Data directory does not belong to Cluster Group”.

These errors are mostly related and occurs when you try to use the Cluster Shared Volumes (CSV) with SQL Server 2012 or lower versions. Support to Cluster Shared Volumes (CSV) for SQL Server was only introduced with SQL Server 2014.

116. Is Cluster Shared Volumes (CSV) similar to Oracle RAC and does SQL Server allow all cluster nodes to perform Read/Write, thus allows load balancing?
Cluster Shared Volumes (CSV) provides a clustered file system which is basically a storage infrastructure for SQL Server, it is possible to allow RAC like support. Even without a Cluster Shared Volumes (CSV), you can still achieve a load balance where read workloads can be spread on primary and multiple secondary replicas.

117. What is Cluster Shared Volumes (CSV)?
Cluster Shared Volumes (CSV) provides multiple cluster instances in a failover cluster environment to have simultaneous read-write access to the same LUN (disk) that is provisioned as an NTFS volume.

Example: Two SQL Server instances installed on a failover cluster can use same disk D:\ to store its database files. You can failover one SQL instance from one node to another node and does not require the disk D:\ to be failed over to new node, rather the SQL instance will access the disk D:\ from other node, resulting in two SQL instances, each one running on different nodes can use disk D:\ for thier own database files. Refer Cluster Shared Volumes (CSV) for more information.

118. When was Cluster Shared Volumes (CSV) introduced?
Cluster Shared Volumes (CSV) was introduced with Windows Server 2008 R2 version, but has been completely re-architected in Windows Server 2012 version.

119. From Which version of SQL Server was Support to Cluster Shared Volumes (CSV) introduced?
Starting with SQL Server 2014, support for deployment of a SQL Server Failover Cluster Instance (FCI) with Cluster Shared Volumes (CSV) was introduced.

120. What are the advantages of Deploying SQL Server 2014 with Cluster Shared Volumes (CSV)?
Below are some of the advantages of deploying SQL Server with Cluster Shared Volumes (CSV)

Scalability – Allows multiple SQL Server instances to use or share the same LUN/Disk, which otherwise wound need separate disks for each SQL Server instance with traditional disks.

Availability – When connectivity between a cluster node and LUN/DISK fails, the connectivity will be established where Cluster Shared Volumes (CSV) routes the traffic to over the network to the LUN/DISK, thus allowing the SQL Server instance to allow connections without any failures.

Manageability – Cluster Shared Volumes (CSV) simplifies the management of multiple SQL Server instances on cluster nodes.

Performance – Cluster Shared Volumes (CSV) provide a read-only cache for unbuffered I/O to SQL databases.

Security – Cluster Shared Volumes (CSV) allows integration with BitLocker which allows to secure the deployments outside of the data-centers, such as at branch offices. Volume level encryption allows in meeting the security compliance requirements.

121. How does SQL Server installation procedure differs with using Cluster Shared Volumes (CSV)?
First, we need to setup Cluster Shared Volumes (CSV) instead of traditional cluster disks, this is to be done by System Administrator. Most of the SQL Server cluster instance installation steps are same even while we use Cluster Shared Volumes (CSV), only place where we change settings during the installation process is in Database Engine Configuration – Data Directories tab where we provide the Cluster Shared Volumes paths like C:\ClusterStortage\SQLServerInstance\ for data root directory and for user database data and log files.

122. How do I see the physical database files which are on Cluster Shared Volumes (CSV)?
We can browse to the Cluster Shared Volumes (CSV) just like we browse any other folder through windows file explorer. We need to check the path C:\ClusterStorage directory and under that the folder which we specified during the installation for the respective database files, which will have our database files.

123. Does Disk resource move to another node during the SQL Server instance failover process?
No, disk resource is not part of the SQL Server group where we have our SQL instances, so disk resource will not failover when we failover a SQL Server instance.

124. What all cluster resources move during failover of SQL Server instance?
Below are the cluster resources which move to new node when we perform a failover of SQL Server instance from one node to another node.

SQL Server Network Name
SQL Server IP Addresses
SQL Server
SQL Server Agent

Any other resources in the group file fileshare or third party backup resources, etc.

125. What is the state of Cluster Shared Volumes (CSV) during the failover of SQL Server instance?
Cluster Shared Volumes (CSV) remains ONLINE and is not affected during the time when SQL Server instance is failed over from one node to another node. There is no requirement to failover the disk resources to the node where SQL Instances are running.

126. Can you upgrade SQL Server 2008 SP2 Standard Edition to 2008 R2 Developer Edition?

You can’t change the version of the installed instance as far as I know, but you could install a second instance with the Dev edition, or uninstall the Standard edition, install Developer edition and attach the user databases.

 127. Does upgrade advisor analyze the remote instances?

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

128. How to upgrade a SQL Server 2000 to SQL Server 2008?

That said, what kind of upgrade are you doing? Are you planning an in-place or side-by-side upgrade? The different approaches will result in different checklists. The safest approach is the side-by-side upgrade. You can do this either by using a backup and restore or dettach/attach of the database files. I’d suggest using the backup & restore as the safer approach. Here are the things I’d do:

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

129. Have you ever prepared a checklist while performing an upgrade?

Yes Of course!!!

• Identify which databases need to be upgraded (ie are still on older versions of SQL Server)
• Of those databases, which are not supported on more recent versions of SQL Server?  This is one for the appropriate vendor – so do we have contact details for the vendor?
• Of those non-supported databases, if this is just because the vendor hasn’t tried it, are we able to try and will they support is in our efforts?
• Identify maintenance routines and scheduled jobs associated with database
• Identify dependencies upon older technologies (eg DTS rather than SSIS), and work out an upgrade path for these
• What applications depend upon the database?
• What UserIDs are required?
• How do we configure the application to point to a new database?
• What else needs to be changed?  (eg middleware servers)
• Are parts of the database subject to Replication?
• Is the database part of a Log Shipping routine?
• Is the database part of a Mirror set?
• What’s the recovery plan for the database?
• What’s the backup plan for the database?
• Are there any SSRS jobs relating to this database?
• What are they?
• Where are they?
• How do we migrate these across?
• What else depends upon those reports?
• …and similarly, are there any OLAP / SSAS dependencies?

It might also be worth thinking about the amount of data in the database:

• How much data have we got?
• How fast is the database growing?
• For how long do we need to retain this data?
• Can we archive anything off to improve performance?

Of course, all the above forms part of your database documentation, so it should be easily to hand, right?

The other things to work out include:          

• How do we test this thing to ensure the migration is successful?
• How do we rollback if it isn’t successful?
• Point of contact for the supplier / vendor / development team
• Point of contact for the customer(s) / user(s)

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

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

131. Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005 server?
No. The only way to move a SQL Server 2008 database to a SQL Server 2005 server is by transferring the data using a method such as Data Transformation Services (Import/Export),SSIS, bcp, or use of a query between linked servers.

132. How long will it take to upgrade my SQL Server databases?

Many factors affect the amount of time needed to upgrade SQL Server Databases. Depending on the complexity of each database, Size of databases, the hardware platform, number of processors, disk subsystem, and amount of RAM plays a significant part in the amount of time required for the upgrade. Selecting “data validation” during the setup increases the amount of time needed to perform the upgrade by a factor of two. Some typical times for the upgrade process are:

Size of Database	Estimated Time Required to Upgrade
400 MB	Less than 20 minutes.
1 GB	Less than 1 hour.
10 GB	Less than 4 hours.
50 GB	Less than 12 hours.
100 GB	Less than 24 hours.

133. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

If applications or services have open ODBC connections to the SQL Server 2005 server during the conversion process, they may not allow the SQL Server to shut down completely. The conversion process will not proceed on to the next step if it does not receive verification that the SQL Server has been completely stopped.

134. “I’m trying to restore a 25 GB database backup taken from a Windows 2003/SQL 2005 machine to a Windows 2008/SQL 2008 machine in the Amazon EC2 cloud, using a .bak file and the SQL Management Studio. SQL Management Studio reports the restore reaches 100% complete, and then just hangs indefinitely (24+ hours) using a lot of CPU, until I restart the SQL Server service. Upon restart, SQL again uses a lot of CPU activity for what seems to be an indefinite amount of time, but the DB never comes online.”

The database is in the process of being upgraded from SQL 2005 to SQL 2008 when you kill it. Check the ERRORLOG in SQL Server and you should see that the database restore is complete and that the database is being upgraded.

This process is normally very quick, but it can take a while to perform depending on the database, especially if you have a lot of pending transactions in the database which much be rolled forward or backward before the database can be upgraded.

135. How to rollback the upgrade?

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming, whereas in a side-by-side upgrade the legacy instance remains available if a rollback is needed.

136. What are the different ways in upgrading to a higher version?

There are two ways:

In-Place Upgrade: Installs 2008 and overwrite on the 2005 Server.

Side-by-side Upgrade: A new instance will be installed and moves the databases.

137. Give some examples when we consider Side-by-side and In-Place?

Pros & Cons: In-Place

Pros

• Easier, mostly automated
• Generally fast overall process
• Requires no additional hardware
• Applications remain pointing to same server/database name

Cons

• Less granular control over upgrade process
• Instance remains offline during part of upgrade
• Not best practice for all components
• Complex rollback strategy
• Not recommended for SSAS

Pros & Cons: Side-by-side:

Pros

• More granular control over the upgrade process
• Original database left unchanged; allows for testing of new database
• Single occurrence of database downtime
• Relatively straightforward rollback strategy

Cons:

• Usually require additional hardware
• Server/database name changes
• Not practical for VLDB unless utilizing SAN(Beware of “loss of quick roll-back)

138. What are the parameters should be considered while choosing the upgrade process?

Components: A certain upgrade strategy might not be possible because the component does not support it. For example, there is no in-place upgrade for SSIS from SQL Server 2000; Microsoft recommends that you upgrade most SQL Server 2000 SSAS components.

Versions and Editions: The in-place upgrade strategy does not support all paths between versions and editions. For example, to upgrade a SQL Server 2000 Enterprise Edition instance to SQL Server 2008 Standard Edition, you must perform a side-by-side upgrade because SQL Server Setup does not support an in-place upgrade path.

Partial upgrading: To transition only a few databases on a server to SQL Server 2008 and leave the rest on the legacy version, you must use a side-by-side upgrade.

Upgrading over time: To transition databases gradually, a few databases at a time, from a legacy instance to SQL Server 2008, you can only use a side-by-side upgrade.

Effect on applications: If your organization requires minimal disturbance to the existing applications and users, you may want to choose an in-place upgrade if possible.

Availability: Both an in-place upgrade and a side-by-side upgrade require that the databases be unavailable for a certain amount of time. The amount of downtime required depends primarily on the size of the data sets. At first, it might seem that an in-place upgrade would be faster than a side-by-side upgrade because the data is not transferred from one server to another. However, an in-place upgrade also requires time for the installation of SQL Server 2008. In a side-by-side upgrade, SQL Server 2008 is already installed on another instance. If the data transfer proceeds quickly and few changes are needed on the new instance, a side-by-side upgrade might be faster than an in-place upgrade.

139. We have upgraded databases from SQL Server 2000 to SQL Server 2008 and now the upgrade hits the production. Unfortunately a part of application is not supporting SQL Server 2008. Do we need to Rollback entire process to SQL 2000? Is that the only solution? If it is the only way! Since the databases at production transactions are being running and the data has been updated. Please assist us.

However, after the upgraded SQL Server 2008 instance goes into production and starts capturing new data, there will come a point in time when enough new data has been captured that a rollback is no longer realistic. For an in-place upgrade, if you encounter problems after the system is in production, making adjustments or “patches” to the new application would be a better option than attempting a rollback. For a side-by-side upgrade, you could employ SSIS to transfer new data from the SQL Server 2008 instance to the legacy SQL Server 2000 to bring it current. Depending on the complexity of the data, this could be a difficult process.

140. Can you list out some of the compatibility options while upgrading?

For example we goanna upgrade SQL 2000 to SQL 2008:

• Some features do not exist anymore
• Examples: undocumented system stored procedures, DUMP, LOAD, sp_addalias,
• Some are deprecated, Will be removed in future versions of SQL Server
• Examples: SQL Mail, COMPUTE BY, Remote Servers, backup passwords,…
• Some features behave differently
• Example: Access to catalog views (new security on system views)
• Some editions have different features
• Example: Express has no SQL Server Agent

141. What are the different tools available while upgrading from SQL 2000 to SQL 2008?

Primary Tools:

• SQL Server 2008 Upgrade Advisor
• DTS xChange

Secondary Tools

• Microsoft® Assessment and Planning Toolkit 3.2
• SQL Server 2008 Upgrade Assistant
• SQL Server Best Practices Analyzer
• System Configuration Checker
• SQL Server Profiler
• SQL Server: Deprecated Features Object Counter
• Other tools

142. Error while upgrading to SQL server 2005 from Sql server 2000.

“while upgrading my sqlserver 2000 with version 8.0.2039 to SQLserver 2005, msxml6.msi failed to upgrade and while trying to uninstall it is try to look the msi file which missing from the path”

MSXML 6 services installed in my server is a lower version of SP2 msxml6-KB954459 and not allowing the upgrade, because setup trying to upgrade MSXML6.MSI and it could not locate the file in the previous installation and Of course it got missed.

So, we have worked out here to download the same version from Microsoft website and then extracted the MSXML6.MSI file to some location and then we tried to un-install the MSXMl service and it went successfully.

Later, again we have installed the msxml6-KB954459-enu-x86.exe and upgrade of SQLserver 2000 to 2005 went smooth without issues and applied latest servicepack 4 successfully.

143. How to Upgrade to SQL SERVER 2005?

• We can directly upgrade instances of SQL Server 2000 Service Pack 3 (SP3) or later, and instances of SQL Server 7.0 SP4 or later, to SQL Server 2005. We can perform most upgrade operations through Setup
• Before running Setup to upgrade to SQL Server 2005, we should first review system requirements and update Matrix
• Before beginning an upgrade
• All SQL Server database files will have to be backed up.
• Appropriate DBCC commands should be run to ensure consistent state.
• SQL Server System databases will have to be configured with autogrow setting to ensure that they will have adequate disk space.
• All startup procedures will have to be disabled; else they will block the process of upgrading.
• Replication log should be emptied and Replication will have to be stopped.

• Another factor that needs to be taken into account while preparing for an upgrade, are the features that have been deprecated in SQL Server 2005.
• Once all the above has been done, SQL Server 2005 setup has to be run and the instance installed.
• The upgrade Advisor does not get installed automatically when SQL Server is installed. It has to be installed separately.
• After reviewing system requirements and upgrade Matrix, run SQL Server Upgrade Advisor to analyze the instances of SQL Server 2000 and SQL Server 7.0.
• Upgrade Advisor produces lists of issues that we must fix before or after upgrading. SQL Server Setup will detect blocking issues that will prevent us from upgrading to SQL Server 2005 (The table alias should not be used in order by clause)
• Thereafter, move user databases to the instance by using backup and restore or detach and attach functionalities in SQL Server 2005. Then register the server, repopulate full text catalogs, update the statistics and run Surface Area Configuration tool. Change the compatibility level to 90 from 80 

144. What are the issues u faced in sql server upgrade?

Common causes

• SQL Server or the machine running the upgrade loses its network connection.
• The database in which you were working has run out of log or data space.
• You are not allowed to perform an update to a table.
• The database is corrupted.
• The database is not available (still in recovery.) It may be unavailable if the upgrade program begins to work before SQL Server finishes performing recovery after startup.
• Unable to restart the server

Can identify the issue from the upgrade log files and resolve the issues and rerun the upgrade advisor

145. What is the sequence to install service packs or hotfixes on an instance of SQL Server that is part of Log Shipping/Database Mirroring/Replication/Failover Clustering environment?

When an instance of SQL Server is configured as part of Log Shipping, Database Mirroring, Replication, or Failover Clustering environment, it is important to install service packs or hotfixes in a correct sequence otherwise we may get unexpected issues.

Log Shipping:

There is no required sequence to apply a service pack or hotfix for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is my preferable to apply service pack or hotfix:

• Apply the service pack or hotfix on the Monitor server.
• Apply the service pack or hotfix on the all Secondary servers.
• Apply the service pack or hotfix on the Primary server.

Database Mirroring:

If you install service packs or hotfixes on servers in a database mirroring environment, you need to determine the role of the servers. If there are many mirroring sessions configured on the server, you need to determine all possible roles that could be. For instance, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role for all mirroring sessions. To do this, follow these steps:

• If a witness server is configured in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the mirroring session.
• If the safety level of the database mirroring session is OFF (asynchronous mode), change the safety level to FULL (this is required in step 3).
• Make sure all database mirroring sessions to be in Synchronous mode and synchronized.
• Pause the database mirroring sessions that are present on the server.
• Install the service pack or hotfix on the mirror server.
• Resume the database mirroring sessions.
• Perform manual failover (all the mirroring sessions on this principal server) to the mirror server so that mirroring server assumes the principal role.
• Pause the database mirroring sessions as step 4.
• Install the service pack or hotfix on the new mirror server (previous principal server).
• Resume the database mirroring sessions.
• If you changed the safety level in step 2, change the safety level back to OFF.
• If the database mirroring session has a witness server, undo the changes made in step 1.

Replication:

In a replication environment, there is no preferable sequence to apply service pack or hotfix for non-bidirectional replication typology. However, for bi-directional replication typology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order:

• Apply the service pack or hotfix on the Distributor server.
• Apply the service pack or hotfix on the Publisher server.
• Apply the service pack or hotfix on the Subscriber server.

 Failover Clustering:

In SQL Server 2005, if you want to install a service pack or hotfix, you must install the setup on the Active node (node that currently runs SQL Server services). When running the setup which will launch simultaneously “remote silence” on all passive nodes.

However, in SQL Server 2008 and SQL Server 2008 R2, the service pack or hotfix deployment is changed to reduce the downtime. Now, you must install the service pack or hotfix on the passive node first. To do this, following these steps:

• Apply the service pack or hotfix on the passive node (or all passive nodes if you have more than one).
• Reboot the passive node.
• Failover the SQL Server failover cluster to the passive node (and the passive node becomes active now).
• Apply the service pack or hotfix on the new passive node (previous active node).
• Reboot the passive node.

146. What are the memory capacity specifications for SQL Server 2008 R2?

SQL Server edition    Maximum memory supported

Datacenter        Operating system maximum

Enterprise        2 TB

Developer        Operating system maximum

Standard        64 GB

Web            64 GB

Workgroup        4 GB (64-bit), OS maximum (32-bit)

Express            1 GB

Express with Tools    1 GB

Express with

Advanced Services    1 GB and 4 GB for Reporting Services

147. What are the maximum capacity specifications for SQL Server 2008 R2?

Bytes per Index Key            900

Bytes per Primary Key            900

Bytes per Foreign Key            900

Bytes per Row                8060

Columns per Index Key            16

Columns per Primary Key        16

Columns per Foreign Key        16

Columns in GROUP BY, ORDER BY    900 Bytes

Columns for Insert Statement        4096

Columns for Delete Statement        4096

Columns for Update Statement        4096

Database Size                524,272 TB

Databases per Instance            32,767

File Groups per Database        32,767

Files per Database            32,767

File Size (Data)                16 TB

File Size (Log)                2 TB

Instances per Computer            50

Instances per Cluster            25

Nested Stored Procedures        32

Nested Sub quires            32

Nested Triggers                32

Clustered Indexes per Table        1

Non Clustered Indexes per Table    999

XML Indexes                249

Parameters per SP’s            2100

Parameters per UDF’s            2100

User Connections            32767

Articles – Merge Publication        256

Articles – Tran and Snapshot Repl    32767

148. What are the components installed with the SQL Server 2005 installation?

• Server Components
• SQL Server Database Engine
• Analysis Services
• Reporting Services
• Notification Services
• Integration Services
• Client Components
• Connectivity Components
• Communication between clients and servers
• Network libraries for DB-Library, ODBC, and OLE DB.
• Management Tools
• Management Studio
• Profiler
• Configuration Manager
• Database Tuning Advisor
• Development Tools
• Business Intelligence Development Studio
• Documentation and Samples
• Books Online
• SQL Server Samples

149. What are the editions available in SQL Server 2005?

• Enterprise Edition (32-bit and 64-bit)
• Evaluation Edition (32-bit and 64-bit)
• Standard Edition (32-bit and 64-bit)
• Workgroup Edition (32-bit and 64-bit)
• Developer Edition (32-bit and 64-bit)
• Express Edition (32-bit only)
• Compact Edition (32-bit only)
• Runtime Edition (32-bit and 64-bit)

150. What are the network protocols that SQL Server supports?

Stand-alone named and default instances support the following network protocols:

• Shared memory
• Named pipes
• TCP/IP
• VIA

Note   Shared memory is not supported on failover clusters.

151. What are the different types of network protocols? Explain each of them in detail

Shared Memory:

Clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer; it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

Server – Machine 1

Clients – Machine 1

TCP/IP:

TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems. TCP/IP includes standards for routing network traffic and offers advanced security features. It is the most popular protocol that is used in business today.

Server – Machine 1

Clients – WAN (Any machine from any network)

Named Pipes:

Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer).

Server – Machine 1

Clients – LAN (Any machine from LAN)

VIA:

Virtual Interface Adapter (VIA) protocol works with VIA hardware. This feature will be deprecated in future releases.

152. What are the basic software requirements for installing SQL Server 2005?

• Microsoft Windows Installer 3.1 or later
• Microsoft Data Access Components (MDAC) 2.8 SP1 or later
• Microsoft .NET Framework 2.0 Software Development Kit (SDK)
• Microsoft .NET Framework 2.0 (Installed Automatically)
• Microsoft SQL Server Native Client (Installed Automatically)
• Microsoft SQL Server Setup support files (Installed Automatically)

153. What are the file locations for Server components?

Server components are installed in directories with the format <instanceID>\<component name>. For example, a default or named instance with the Database Engine, Analysis Services, and Reporting Services would have the following default directories:

• <Program Files>\Microsoft SQL Server\MSSQL.1\MSSQL\ Database Engine
• <Program Files>\Microsoft SQL Server\MSSQL.2\OLAP\ for Analysis Services
• <Program Files>\Microsoft SQL Server\MSSQL.3\RS\ for Reporting Services

154. What about SSIS, Notification services and client components?

SQL Server 2005 Integration Services, Notification Services, and client components are not instance aware and, therefore, are not assigned an instance ID. Non-instance-aware components are installed to the same directory by default: <system drive>:\Program Files\Microsoft SQL Server\90\. Changing the installation path for one shared component also changes it for the other shared components. Subsequent installations install non-instance-aware components to the same directory as the original installation.

155. List out the default path for the SQL Server components.

Database Engine server components

\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Binn\

Database Engine data files

\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\

Analysis Services server

\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Bin\

Analysis Services data files

\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Data\

Reporting Services report server

\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services

\ReportServer\Bin\

Reporting Services report manager

\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services

\ReportManager\Bin\

SQL Server Integration Services

<Install Directory>\90\DTS\

Notification Services

<Install Directory>\90\Notification Services\

Client Components

<Install Directory>\90\Tools\

Components that are shared between all instances of SQL Server 2005

\Program Files\Microsoft SQL Server\90\Shared\

156. Can we change the directory while adding new features?

Can’t! You must either install additional features to the directories already established by Setup, or uninstall and reinstall the product.

157. What are the log files generated while Installing\Upgrading\Applying (packages) SQL Server on Windows machine? (SQL Server 2008 / R2)

Summary.txt (SQL 2005)

This file shows the SQL Server components that were detected during Setup, the operating system environment, command-line parameter values if they are specified, and the overall status of each MSI/MSP that was executed.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\.

Note:

To find errors in the summary text file, search the file by using the “error” or “failed” keywords.

 Summary_engine-base_YYYYMMDD_HHMMss.txt

Generated during the main workflow

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap

\Log\<YYYYMMDD_HHMM>\

Summary_engine-base_YYYYMMDD_HHMMss_ComponentUpdate.txt

Generated during the component update workflow.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Summary_engine-base_20080503_040551_GlobalRules.txt

Generated during the global rules workflow.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Detail.txt

Detail.txt is generated for the main workflow such as install or upgrade, and provides the details of the execution. The logs in the file are generated based on the time when each action for the installation was invoked, and show the order in which the actions were executed, and their dependencies.

Location

%\Microsoft SQL Server\100\Setup Bootstrap\Log

\<YYYYMMDD_HHMM>\Detail.txt.

Note:

If an error occurs during the Setup process, the exception or error are logged at the end of this file. To find the errors in this file, first examine the end of the file followed by a search of the file for the “error” or “exception” keywords.

Detail_ComponentUpdate.txt

Detail_GlobalRules.txt

MSI log files

The MSI log files provide details of the installation package process. They are generated by the MSIEXEC during the installation of the specified package.

Location

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log \<YYYYMMDD_HHMM>\<Name>.log.

Note:

At the end of the file is a summary of the execution which includes the success or failure status and properties. To find the error in the MSI file, search for “value 3” and usually the errors can be found close to the string.

ConfigurationFile.ini

The configuration file contains the input settings that are provided during installation. It can be used to restart the installation without having to enter the settings manually. However, passwords for the accounts, PID, and some parameters are not saved in the configuration file.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

<YYYYMMDD_HHMM>\

SystemConfigurationCheck_Report.htm

Contains a short description for each executed rule, and the execution status.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log

\<YYYYMMDD_HHMM>\

158. How much had disk space required installing the SQL Server 2005/08?

2 GB. Before the installation is started windows installer allocated 2 GB of temporary space for installation.

159. What is the space required for each component in SQL Server?

Database Engine and data files, Replication, and Full-Text Search – 280 MB

Analysis Services and data files – 90 MB

Reporting Services and Report Manager – 120 MB

Notification Services engine components, client components, and rules components – 50 MB

Integration Services – 120 MB

Client Components – 850 MB

SQL Server Books Online and SQL Server Compact Edition Books Online – 240 MB

Samples and sample databases - 410 MB

(Note that samples and sample databases are not installed by default.)

160. List out the Windows machines which supports the SQL Server 2005 Enterprise edition.

32-Bit:

Windows 2000 Server SP4

Windows 2000 Advanced Server SP4

Windows 2000 Datacenter Edition SP4

Windows Server 2003 Server SP1

Windows Server 2003 Enterprise Edition SP1

Windows Server 2003 Datacenter Edition SP1

Windows Small Business Server 2003 Standard Edition SP1

Windows Small Business Server 2003 Premium Edition SP1

Windows Server 2008 Standard (32 bit and 64 bit)

Windows Server 2008 Enterprise (32 bit and 64 bit)

Windows Server 2008 Data Center (32 bit and 64 bit)

Windows Server 2008 Web Edition (32 bit and 64 bit)

64-Bit:

Windows Server 2003 64-Bit Itanium Datacenter Edition SP1

Windows Server 2003 64-Bit Itanium Enterprise Edition SP1

Windows Server 2008 Itanium

161. What are the security considerations for a SQL Server installation?

Before Installation:

• Enhance physical security
• Use firewalls
• Isolate services
• Create service accounts with least privileges
• Disable NetBIOS and server message block

After Installation:

• Run SQL Server services with the lowest possible privileges.
• Associate SQL Server services with Windows accounts.
• Use Windows Authentication for connections to SQL Server.
• Always assign a strong password to the sql account.
• Always enable password policy checking.

162. What additional software requires for installing SSRS 2005?

• Microsoft Internet Information Services (IIS) 5.0 or later is required for Reporting Services installations.
• Microsoft Internet Explorer 6.0 SP1 is required for installations of the Report Designer component of Reporting Services.

163. Which component performs the configuration check while installing the SQL Server 2005?

As part of SQL Server 2005 Setup, the System Configuration Checker (SCC) scans the computer where Microsoft SQL Server 2005 will be installed. The SCC checks for conditions that prevent a successful SQL Server installation. Before Setup starts the SQL Server 2005 Installation Wizard, the SCC retrieves the status of each check item, compares the result with required conditions, and provides guidance for removal of blocking issues. All of the SCC check items are network enabled; checks can run on a local computer, as well as in remote and cluster situations.

164. What are the precautions needs to be taken before installing SQL Server 2005?

• Be sure the computer meets the system requirements for SQL Server 2005
• Review Security Considerations for a SQL Server Installation.
• Run SCC to identify the blocking issues and resolve before go ahead.
• Make sure you have administrator permissions on the computer where SQL Server will be installed. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.
• Back up your current installation of SQL Server if you are running an instance of SQL Server on the computer where SQL Server 2005 is to be installed
• Verify that the disk where SQL Server will be installed is uncompressed. If you attempt to install SQL Server to a compressed drive, Setup will fail
• Exit antivirus software while installing SQL Server
• Stop all services that depend on SQL Server, including any service using Open Database Connectivity (ODBC), such as Internet Information Services (IIS). Exit Event Viewer and registry editors (Regedit.exe or Regedt32.exe).

165. List out the Instance-Aware and Instance-Unaware Services

Instance-aware services in Microsoft SQL Server 2005 include:

• SQL Server
• SQL Server Agent
• Analysis Services
• Reporting Services
• Full-Text Search

Instance-unaware services in SQL Server 2005 include:

• Notification Services
• Integration Services
• SQL Server Browser
• SQL Server Active Directory Helper
• SQL Writer

166. What is the installation log file location for SQL Server 2005?

Summary.txt:

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG

All other files:

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files

Here you find logs for each individual component as well as the actions performed at the time of installation.

167. What information stored in Summary.txt?

This file has information on the installation start and stop time, installed components, machine name, product, version and detailed log files.  Although this file differs slightly based on selected installation components, this file does not have any user names, passwords, service accounts, ports, etc.  What this file does have is references to the detailed files which will be reviewed next.

168. Is there any possibility to find out the “sa” password from log files?

Clear Passwords not stored at anywhere. But !

When it comes to clear text passwords being stored in the detailed log files, it appears as if the sa password validation and confirmation is logged, but the actual password is never stored in clear text during the database services installation.  As an example, the files listed below reference the usage of the sa password:

• SQLSetup0002_MachineName_SQL.log
• SQLSetup0002_MachineName_Tools.log
• SQLSetup0002_MachineName_WI.log

Stored in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files

After reviewing the SQL Server 2005 installation log files, it appears as if temporary files are referenced.  Do these local files have clear text passwords?  Based on files located at the below location C:\DOCUME~1\ProfileName\LOCALS~1\Temp\*.tmp these files appear to be cleaned up after the installation process and do not appear to pose any additional vulnerability because they do not appear to have the passwords in clear text.

169. What are the prerequisites for installing SQL Server 2008?

Microsoft SQL Server 2008 setup requires Windows Installer 4.5 and .Net Framework 3.5 SP1 to be installed. The Windows Installer 4.5 and .Net Framework 3.5 SP1 can be installed independently or it can be installed by double clicking SETUP.EXE within SQL Server 2008 installation media. Both Windows Installer 4.5 and .Net Framework setups are also available in <Drive>:\x86\redist folder of installation media. Once Windows Installer 4.5 and .Net Framework 3.5 SP1 is installed the system requires a restart of the operating system.

170. I have applied SP3 on 2005 instances. Where we can find the log files?

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap

\LOG\Hotfix\Summary.txt (On that day)

Other files like component wise files are also located in the same folder.

171. What is Slipstreaming?

It is a term used to describe merging original source media with updates in memory and then installing the updated files. Slipstreaming has been supported by Windows Operating systems for awhile but has just been added to SQL Server 2008 service pack 1. Slipstream allows you to go to the latest and greatest, currently service pack 1 and a CU for service pack 1. Prior to service pack, we supported just update the setup file also referred to as “Patchable Setup”. Since the release of SQL Server 2008 Service Pack 1, it is recommend to use the slipstream procedures instead as patchable setup since the entire product can be updated and you will be using the latest and greatest. Any scenario (install, upgrade, addnode) that is supported by the original media is supported when slipstream.

172.  I have installed SQL 2008 Database Engine component to an instance and now I want to add features to this instance using slipstream. How do I add features to an existing instance?

In theory you just run the slipstream build again to add features. However, setup will fail since setup does not correct detect that it is installed and needs to be patched versus installed.  In this case, you need to add features using original media and then patch using the service pack installer. We don’t support features within an instance at different levels, so ensure features within an instance are at the same patch level.

173.  I have slipstreamed media, but I just want to install the original media (RTM). How can I just install the original media?

You need to use your original media.

174.  What if I did not copy the ia64 and x64 folders to my original media which is necessary for slipstreaming. Can I still install x86?

Technically, it will work. But we do not recommend skipping merging all architectures since at some point, a user might install one of these architectures.  If you merges x86 but not x64, you media is inconsistent. If at a later point in time, you run x64, the result will be unpredictable.

175. I already have SQL Server 2008 instance and now I want to update to service pack 1. Do I need to use slipstream?

No, slipstream will not work when the product is already installed.

174. Does PCUSource need to be an absolute path?

No, it can be a relative path, such as “.\PCU”. However, when you launch setup.exe your current path needs to point to the setup.exe.

175. I am hitting the error: “The setting ‘PCUSOURCE’ specified is not recognized.”

If you have followed the slipstream instructions, ensure the path to PCUSOURCE does not contain spaces. The setup does not handle the spaces as expected.  The quick workaround is to rename the PCUSource path so it does not contain any spaces. You can confirm this is the problem by viewing the Detail_ComponentUpdate.log located at %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<last session>. Search for “PCUSource”. If you see commas in the PCUSource path, this is the issue:

174. Can I slipstream in any CU?

In general, yes.  However, you need to ensure the CU will work the original release (RTM) or SP.  For example, if you are slipstreaming Service Package 1 (SP1), then only CUs based on SP1 can be slipstreamed into the installation. If you are not slipstreaming a service pack, then you need to ensure the CU is for the original release.

175. After I slipstream the original media and service pack 1, and then uninstall the service pack is it the same as just install the original media?

Essentially, it is the same. However some of the components (e.g. SNAC, MSXML 6, and Setup support files) can’t be rolled back. The core features: Database Engine, Reporting Services, Analysis Services, and the other shred components will be rolled back.

176. Can I use slipstream to upgrade from SQL 2005 to SQL Server 2008?

Yes.

177. Can I use slipstream to upgrade from SQL 2000 to SQL Server 2008?

Yes.

178.  Is there a way to find out when a patch / sp was last applied to an instance?

• Start SSMS, open help menu and go to “about”
• Run Select @@version
• Run SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
• Run XP_READERRORLOG and from the result set we can find the version details
• If nothing worked as far as I know only option there would be to look at the add/remove programs list (or dump this data from the registry to make it easier to deal with) and get that info from there. SQL Server doesn’t track that within the system databases.

179.  I have submitted a Query to SQL Server from an Application and I got the reply as “data inserted successfully”. Can you demonstrate what the processing done inside?

When you submit a query to a SQL Server database, a number of processes on the server go to work on that query. The purpose of all these processes is to manage the system such that it will provide your data back to you, or store it, in as timely a manner as possible, whilst maintaining the integrity of the data.

All these processes go through two stages:

1. Relational Engine

2. Storage Engine

At Client:

1. User enter data and click on submit

2. The client database library transforms the original request into a sequence of one or more Transact-SQL statements to be sent to SQL Server. These statements are encapsulated in one or more Tabular Data Stream (TDS) packets and passed to the database network library

3. The database network library uses the network library available in the client computer to repackage the TDS packets as network protocol packets.

4. The network protocol packets are sent to the server computer network library across the network

At Server:

5. The extracted TDS packets are sent to Open Data Services (ODS), where the original query is extracted.

6. ODS sends the query to the relational engine

7. A connection established to the relational engine and assign a SID to the connection

At Relational Engine:

8. Check permissions and determines if the query can be executed by the user associated with the request

9.  Query sends to Query Parser

• It checks that the T-SQL is written correctly
• Build a Parse Tree \ Sequence Tree

10. Parse Tree sends to Algebrizer

• Verifies all the columns, objects and data types
• Aggregate Binding (determines the location of aggregates such as GROUP BY, and MAX)
• Builds a Query Processor Tree in Binary Format

11. Query Processor Tree sends to Optimizer

• Based on the query processor tree and Histogram (Statistics) builds an optimized execution plan
• Stores the execution plan into cache and send it to the database engine

At Database Engine:

12. Database engine map a batch into different tasks

13. Each task associated with a process

14. Each process assigned with a Windows Thread or a Windows Fiber. The worker thread takes care of this.

15. The Thread/Fiber send to the execution queue and wait for the CPU time.

16. The Thread/Fiber identifies the table location where the data need to be stored

17. Go to the file header, checks the PFS, GAM and GSAM and go to the correct page

18. Verifies the page is not corrupted using Torn page Detection / Check SUM and writes the data

19. If require allocates new pages and stores data on it. Once the data is stored/updated/added in a page, it updates the below locations

• PFS
• Page Header – Checksum / Torn Page Detection (Sector info)
• BCM
• DCM

20. In this process the

• Memory manager take care of allocating buffers, new pages etc,
• Lock manager take care of allocating appropriate locks on the objects/pages and releasing them when task completed
• Thread Scheduler: schedules the threads for CPU time
• I/O manager: Establish memory bus for read/write operations from memory to disk and vice versa
• Deadlock\Resource\Scheduler Monitor: Monitors the processes

21. If that is a DML operation, it picks the appropriate page from disk and put the page in Memory.

22. While the page is available on Memory based on the ISOLATION LEVEL an shared / exclusive / update / Schema lock issued on that page.

23. Once the page is modified at Memory, that means once the transaction completed the transactional operation logged into log file (.ldf) to the concerned VLF.

24. Here we should understand that only the operation (T-SQL statements) logged into ldf file. The modified page waits in memory till check point happens. These pages are know as dirty pages as the page data is differ in between the page on Disk and Memory.

25.   Once the checkpoint happens the page will be written back to the disk.

26. Once the process is completed the result set is submitted to the relational engine and follow the same process for sending back the result set to client application.

27. The connection will be closed and the SID is removed

180. Explain effects of Min and Max memory configuration options

The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.

181. Can you describe SQL Server Memory Architecture?

SQL Server dynamically acquires and frees memory as required. Typically, an administrator need not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.

SQL Server supports Address Windowing Extensions (AWE) allowing use of physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems. This feature is deprecated from Dinali 2012.

SQL Server tries to reach a balance between two goals:

• Keep the buffer pool from becoming so big that the entire system is low on memory.
• Minimize physical I/O to the database files by maximizing the size of the buffer pool.

182.  Do you have any idea about Buffer Management?

A buffer is a 8kb size in memory. To reduce the I/O operations from database to disk buffer manager use the buffer cache. BM gets the data from database to buffer cache and modifies the data and the modified page is sent back to the disk

The buffer manager only performs reads and writes to the database. Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.

183. What is an Active Log?

The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. This is the section of the log required to do a full recovery of the database. No part of the active log can ever be truncated. All log records must be truncated from the parts of the log before the MinLSN.

184. What is “Write Ahead Transaction Log”?

SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk.

Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database. A page modified in the cache, but not yet written to disk, is called a dirty page. The internal process that actually goes on:

1. Copy of the data pages are pulled and placed in buffer cache
2. Applied the operation on the pages that are on buffer cache
3. Write the log record details (Pages modified) to Disk
4. Write / flush /apply the page to the disk

If step 4 happens before the step 3 then rollback is impossible. SQL Server takes the responsibility of writing the log details to disk before flushing the dirty pages.

185. What are the CheckPoints in SQL Server database?

Checkpoints flush dirty data pages from the buffer cache of the current database to disk. This minimizes the active portion of the log that must be processed during a full recovery of a database. During a full recovery, the following types of actions are performed:

• The log records of modifications not flushed to disk before the system stopped are rolled forward.
• All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup. Also, stopping a server issues a checkpoint in each database on the server.

186. Can you talk about “Transactionlog” Logical and Physical architecture?

 “Transactionlog” Logical Architecture:

Each log record is identified by a unique number called LSN (Log Sequence Number). A log record contains the LSN, TransactionID to which it belongs and data modification record.

Data modification record: It’s either operation performed or before and after data image

• When recorded “Operation Performed”

• Transaction committed – Logical Operation is permanently applied to the data
• Transaction rollback – Reverse Logical Operation is applied to the data.

• When Recorded “Before and After Data Image”

• Transaction committed – Applied the after transaction image
• Transaction rollback – Applied the before transaction image

“Transactionlog” Physical Architecture:

The transaction log is used to guarantee the data integrity of the database and for data recovery.

The 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 only time virtual log files affect system performance is if the log files are defined by small size and growth_increment values. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database startup and also log backup and restore operations

187. Have you ever heard the word “Files” or “File Groups” in SQL Server?

Files: There are three types, Primary- .mdf, Secondary – .ndf and Log files – .ldf

File Groups: There are two, Primary File Group – All system tables and User Defined – Depends

All secondary files and user defined file groups are created to optimize the data access and for partitioning the tables.

188. What are the advantages and disadvantages over using filegroups?

Advantages:

• Using filegroups, you can explicitly place database objects into a particular set of database files. For example, you can separate tables and their nonclustered indexes into separate filegroups. This can improve performance, because modifications to the table can be written to both the table and the index at the same time. This can be especially useful if you are not using striping with parity (RAID-5).
• Another advantage of filegroups is the ability to back up only a single filegroup at a time. This can be extremely useful for a VLDB, because the sheer size of the database could make backing up an extremely time-consuming process.
• Yet another advantage is the ability to mark the filegroup and all data in the files that are part of it as either read-only or read-write.

Disadvantages

• The first is the administration that is involved in keeping track of the files in the filegroup and the database objects that are placed in them.
• The other is that if you are working with a smaller database and have RAID-5 implemented, you may not be improving performance.

189. What is a page in database systems and tell me something about pages?

The fundamental unit of data storage in SQL Server is the page.

Page size is 8kb means 128 pages = 1 MB.

Page starts with the header of 96 bytes that is used to store page number, page type, the amount of free space on the page, and the object id that owns the page. The maximum size of a single row on a page is 8060 bytes. But this restriction is relaxed for tables which are having varchar, nvarchar, Varbinary, Taxt or Image columns.

190.  Any idea what is “ROW_OVERFLOW_DATA allocation unit”?

When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit.

This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page.