Script to Gather Disk Space Details and Database File Free Space Information

It is a common requirement to find the details about the total free disk space, database files total size, free space available in database files, name of the database files, type of database file and the physical location they are located at.

Below is the code which returns Free disk space on a drive, name of database, logical and physical file names of all databases, total size, free space available inside the database files and the physical location where they are stored.

USE tempdb
    GO
      CREATE TABLE #TMPFIXEDDRIVES (
    DRIVE CHAR(1),
    MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES
    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED (
    DBNAME VARCHAR(1500),
    FILENME VARCHAR(500),
    SPACEUSED FLOAT,
    growth int)

INSERT INTO #TMPSPACEUSED
    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed,growth from sysfiles''')

SELECT C.DRIVE,
    CASE 
    WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS DISKSPACEFREE,
    A.NAME AS DATABASENAME,
    B.NAME AS FILENAME,
    CASE B.TYPE 
    WHEN 0 THEN 'DATA'
    ELSE TYPE_DESC
    END AS FILETYPE,
    CASE 
    WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS FILESIZE,
    CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE_MB,
    (d.growth*8)/1024 as AutoGrowth_MB,
    B.PHYSICAL_NAME
    FROM SYS.DATABASES A
    JOIN SYS.MASTER_FILES B
    ON A.DATABASE_ID = B.DATABASE_ID
    JOIN #TMPFIXEDDRIVES C
    ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
    JOIN #TMPSPACEUSED D
    ON A.NAME = D.DBNAME
    AND B.NAME = D.FILENME
    ORDER BY databasename

/*<b>*/DROP/*</b>*/ TABLE #TMPFIXEDDRIVES
/*<b>*/DROP/*</b>*/ TABLE #TMPSPACEUSED

Output returned will be in below format

DRIVE

DISKSPACEFREE

DATABASENAME

FILENAME

FILETYPE

FILESIZE

SPACEFREE_MB

AutoGrowth_MB

PHYSICAL_NAME

S

923.48 GB

DBName

LogicalFileName

DATA

218.16 GB

8500.25

200

S:\DataFile.mdf

DRIVE – Drive where the database file resides.
DISKSPACEFREE – Free disk space available in the drive.
DATABASENAME – Name of the database.
FILENAME – Logical database File Name.
FILETYPE – What type of file it is, Data File, Log File.
FILESIZE – Total size of the database file.
SPACEFREE_MB – Free space available inside the database file.
AutoGrowth_MB – Auto_Growth size of the database file.
PHYSICAL_NAME – Physical location and physical database file name.

Below script will provide with space details of a specific database and its files. This information will be useful to find which database files are large, how much free space is still available in the database files, so that we can shrink the file to release the available free space.

select
       name
     , filename
     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
     , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
     from dbo.sysfiles a 

name

filename

FileSizeMB

SpaceUsedMB

FreeSpaceMB

tempdev

T:\Data\tempdb.mdf

40960.00

4.75

40955.25

templog

T:\Log\templog.mdf

2048.00

365.86

1682.14

tempdata

T:\Data\tempdata.mdf

40960.00

4.56

40955.44

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Setup or Installation Frequently Asked Questions (FAQ) in Interviews

One of the most common tasks which a DBA performs regularly is installation of SQL Server latest versions, patching SQL Server with latest released Service Packs or Cumulative Update patches, Security Updates, migrating from one version of SQL Server instance to higher versions, etc. DBA’s are expected to have very good knowledge about Pre-requisites of installation of SQL Server related products, need to plan, test and install the patches with minimal downtime. Below are some of the common questions asked in interviews about SQL Server Setup.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Backup and Restore Frequently Asked Question and Answers (FAQ) in Interviews Part 1

Backups in SQL Server are considered as one of the most important thing which need to be performed regularly to get our data back in case of unexpected issues or a disaster. As a DBA it is very important to be very familiar with the Backup and Restore concepts. Almost in every SQL DBA interview, there will be questions on Backup and Restore.

Below are some of the top SQL Server Backup and Restore Frequently Asked Interview Question and Answers.

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.

What are Recovery Models in SQL Server?
Recovery Models are part of database options and determines the allowed possible restore sequence. Recovery model basically dictate SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models as mentioned below.

Full Recovery Model in SQL Server – SQL Server FULL recovery is very commonly used recovery model allows SQL Server to store all transactions data in the transaction log until a transaction log backup occurs or the transaction log is truncated. Full recovery model is the most complete recovery model which allows us to recover all of the data to any point in time, as long as all backups are available.

SIMPLE recovery model in SQL Server – SIMPLE recovery model is the most basic recovery model in SQL Server where every transaction is still written to the transaction log, but once the transaction is committed and the data has been written to the data file the space used for that transaction can be reused for new transactions. As the log file is getting reused, we cannot perform log backups and thus cannot recover data to point in time. We can only restore an FULL database in case of disaster and causes loss of data. This recovery model is used for databases which are not critical and where data loss is acceptable.

Bulk-Logged Recovery Model in SQL Server – In Bulk-Logged Recovery Model all transactions are logged similar to FULL recovery model except for transactions which involve bulk operations like BULK INSERT, CREATE INDEX, SELECT INTO, etc. These operations are not fully logged in the transaction log and therefore do not take as much space in the transaction log, but does not allow point-in-time recovery of database during the time when there are bulk transactions. This recovery model is used if there are regular bulk operations which happen on a database.

How to plan a backup strategy in SQL Server?
There are various types of backup options available in SQL Server and as a DBA we need to plan what backups need to be performed and how frequently those backups need to be performed.

The planning for database backups change based on server to server, or can be different in different environments depending upon Size of database, importance of data, amount of allowed data loss and available storage to store the backups.

Example1:
If the database is small in size and is not very critical and data loss of 24 hours is acceptable, then below would be the backup plan

– Set the database to SIMPLE recover model.
– Perform daily FULL Backups to local disk during the night.
– Copy the daily backups from local disk to Backup Server or Tape.
– At all times, store atleast 3 days of FULL backups on local disk and 1 month of Full backups on Tape.

Example2:
If the database is large in size(100 GB+) and is very critical and data loss of 10 minutes is only acceptable, then below would be the backup plan

– Set the database to FULL recover model.
– Perform weekly FULL Backups to local disk in the night.
– Perform Daily Differential Backup once in a day, except for the day when we are performing the FULL backup.
– Perform Transaction Log backup every 10 minutes.
– Copy the weekly backups from local disk to Backup Server or Tape.
– Copy the daily differential backups from local disk to Backup Server or Tape.
– Copy the transaction log backups from local disk to Backup Server or Tape.
– At all times, store atleast 1 FULL backup on local disk and 1 month of Full backups on Tape.
– At all times, store atleast recent 3 differential backups on local disk and 1 month of differential backups on Tape.
– At all times, store atleast 3 days worth of transaction log backups on local disk and 1 month of transaction log backups on Tape.

How do you recover a database which was crashed or corrupted using database backups?
This is most important questions and is asked almost in every interview. Failing to answer this question may reduce your chances of getting the job.

Normally, you will be give a backup scenario and will be asked on how you will you recover the database using the available backups. Below is the scenario.

Scenario: There is a backup strategy in place where you have weekly FULL database backup which is performed every Sunday at 12:00 AM. There are daily Differential backup which is performed at 12:00 AM, except on Sundays. There are transaction log backups which are performed every 1 hour. Database crashed on Tuesday at 06:10 AM, now you need to recover as much data as possible, how will you recover the data?

Answer: First, we need to restore FULL database backup which was performed on recent Sunday at 12:00 AM. This needs to be restored with NoRecovery option. Next restore latest differential backup which was performed on Tuesday at 12:00 AM with NoRecovery option. Restore all transaction log backups in sequence taken from Tuesday 12:00 AM till 05:00 AM with NoRecovery option and finally restore the last transaction log file taken at 06:00 AM with Recovery option. This should now bring the database online with all the data till Tuesday 06:00 AM. There will be 10 minutes worth loss of data which was stored between 06:00 AM to 06:10 AM.

How will database recovery model impact database backups?
Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE, DIFFERENTIAL and FULL.

– When a database is set to SIMPLE recovery model, only FULL and DIFFERENTIAL backups are allowed. Transaction Log backups cannot be performed.
– Under BULK-LOGGED or FULL recovery mode, all types of backups are allowed.

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.

How can you automate performing database backups in SQL Server?
We can created 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.

 

SQL Server Cluster Shared Volumes (CSV) Frequently Asked Questions and Answers (FAQ) in Interviews

You can find below, some of the Frequently Asked Interview Question and Answers on Cluster Shared Volumes (CSV) in relation with SQL Server.

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.

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.

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.

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.

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.

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.

Does Disk resource move to another node during the SQL Server instance failover process?
No, disk resource is nor 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.

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.

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.

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.

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.

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.

 

Planning and Implementation of Maintenance of Servers with Least Amount of Downtime with SQL Server 2012 AlwaysON Availability Groups

There was a request where we had to perform maintenance on servers which were hosting Primary and Secondary AlwaysON Availability Group Databases. Initially, when the Servers were setup, the hardware was not planned for best performance, so it was decided that we will replace the servers with complete new servers with new and better Hardware. Below is the environment setup.

Servers Server01 and Server02 are running Windows Server 2012 Operating System with One Default Standalone Instance on each server installed with SQL Server 2012 Enterprise Edition. AlwaysON Availability Group with Listener name as “PROD_LSTNR” has been setup between Server01 as Primary AlwaysON replica and Server02 as Secondary replica and this AG has couple of user databases. There is another AlwaysON Availability Group with Listener name as “PROD_LSTNR_RPT” has been setup between Server02 as Primary AlwaysON replica and Server01 as Secondary replica and this AG has ReportServer and ReportServerTempDB databases.

Now, we had to replace both the servers with brand new servers with new and better hardware. This needs to be accomplished with minimum downtime for end users and least amount of manual work to be performed by DBA or SysAdmins.

Below are the High Level steps which we identified and later successfully implemented with minimum downtime and least amount of manual work.

Prepare for Maintenance of AlwaysON Availability Group Secondary Replica
– Let the AlwaysON Availability Group “PROD_LSTNR” run on the Primary Replica Server01.
– Make a list of Report Server Subscription jobs which are currently enabled on Server02. Save it for future reference. If there are any other user created jobs related to Report Server, script and create them on Server01 and keep them disabled for now.
– Backup ReportServer and ReportServerTempDB databases and system databases on secondary replica Server02
– Backup ReportServer Encryption Keys on Server02.
– Backup ReportServer Encryption Keys on Server01.
– Synchronize all logins and resolve any orphan users on Primary Server Server01
– Failover AlwaysON Availability Group “PROD_LSTNR_RPT” to Replica Server01. This causes very short downtime for reporting services.
– Restore the Encryption Keys on Server01 with the backup taken from Server02.
– Remove Server02 from Scale-Out deployment tab using Report Service Configuration Manager on Server01.
– Stop Reporting Services on new secondary server Server02.
– Restart SQL Server Agent on Server01. This will create or enable Report Server Subscription jobs on Server01 automatically. Enable any user created report server related jobs on Server01.
– Test the reports to make sure they are working. If you have configured reports data sources to use AlwaysON Secondary databases read_Only databases, then make sure that AlwaysON Availability Group settings allow Primary replica to allow both Read/Write connections.
– Turn off SQL Services on Secondary replica Server02.

Steps for System Administrators:
– This is critical step and to be done by System Administrators. Take down Secondary replica Server02 and copy the consistent server snapshot of Secondary server Server02 in to new server with better hardware.
– Rename Server02 to Server02_Old and rename new server to Server02 and change the IP addresses same way.
– Add new secondary server Server02 with powerful hardware back to the network and domain.
– Now, you should be able to see that AlwaysON Availability Groups start synchronizing and after some time will get fully synchronized.
– Now that the secondary replica maintenance is complete, next we need to prepare the primary server for the maintenance to replace the server with new server with powerful hardware.

Prepare for Maintenance of AlwaysON Availability Group Primary Replica
– Backup all user and system databases on Primary replica Server01.
– Backup Encryption Keys on primary and secondary server Server01 and Server02
– Make a list of all application or user created jobs which are enabled on primary replica Server01 and disable the jobs on primary server Server01.
– Failover AlwaysON Availability Group “PROD_LSTNR” to Secondary Server Server02 and now this will become new primary server.
– Enable all application or user created jobs as we noted earlier.
– Test the Application.
– Failover AlwaysON Availability Group “PROD_LSTNR_RPT” to Secondary Server Server02 and now this will become new primary server.
– Stop the reporting Services on new secondary server Server01.
– Restart SQL Server Agent Service on new primary server server02. This will create or enable report server subscription jobs on new primary replica Server02.
– Restore the Encryption keys on Server02 using the backup taken from Server01.
– From Report Server Configuration Manager, remove Server01 from Scale-Out Deployment.
– Test the reports to make sure they are working. If you have configured reports data sources to use AlwaysON Secondary databases read_Only databases, then make sure that AlwaysON Availability Group settings allow Primary replica to allow both Read/Write connections.

Steps for System Administrators:
– This is critical step and to be done by System Administrators. Take down new Secondary replica Server01 and copy the consistent server snapshot of Secondary server Server01 in to new server with better hardware.
– Rename Server01 to Server01_Old and rename new server to Server01 and change the IP addresses same way.
– Add new secondary server Server01 with powerful hardware back to the network and domain.
– Now, you should be able to see that AlwaysON Availability Groups start synchronizing and after some time will get fully synchronized.

– Now that the new secondary replica Server01 maintenance is complete, next we can failover the AlwaysON Availability Group “PROD_LSTNR” to Server01 where it was running as primary before, enable all the user created jobs on Server01 and disable them on Server02. Let the AlwaysON Availability Group “PROD_LSTNR_RPT” run on Server02 and use the read_only copy and offload the reporting from primary server.
– Test the application.

We have successfully followed these steps in our environment, but you should test this solution thoroughly on your lab or test environment before proceeding with the production changes. This post is provided “AS IS” with no warranties or guarantees.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 

Configuring SQL Server 2012 Logshipping on AlwaysON Availability Group Databases with Successful Failover of AG and Logshipping

Configuring SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG) is supported in SQL Server 2012 or higher. We can failover AlwaysON Availability Groups between Primary and Secondary Server and also move or enable the Logshipping from new Primary Server to DR server. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02.

Now we can setup Logshipping from Server01 to Server03 just the same way how we setup logshipping between any two server databases as mentioned below.

How to Setup Logshipping between two servers?
– Connect to SQL Server instance from SSMS using instance name as Server01.
– On Server01, right click the database you want to use as your primary database in the log shipping configuration, and go to its Properties and go to last tab “Transaction Log Shipping”.
– Select the “Enable this as a primary database in a log shipping configuration” check box, which will now enable the logshipping configuration options to choose backup, copy and restore settings.
– Under “Transaction log backups”, click on Backup Settings.
– In the Network path to the backup folder box, type the network path to the backup share to use it for placing the transaction log backups.
– If this backup share is located on the primary server itself then type the local path to the backup folder in the “If the backup folder is located on the primary server” text box. If the backup folder is not on the primary server, you can leave this box empty.
– Make sure that the SQL Server service account on the primary server has full permissions on the backup share.
– Configure the Delete files older than and Alert, if no backup occurs within parameters.
– You can choose to customize the schedule for log backup and alerts.
– Starting SQL Server 2008, backup compression is supported, so you may choose to enabled it or set it to use Instance wide backup compression configuration.
– Now, under Secondary server instances and databases, click Add.
– Click on Connect and then connect to the SQL Server instance Server03 that we want to use as the secondary server for logshipping.
– In the Secondary Database box, either choose a database from the list or just type the name of the database you want to create.
– On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
– Make sure that you choose correct option above and appropriately prepare the database on secondary server.
– Now go to Copy Files tab, and in the “Destination folder for copied files” text box, put the path of the folder to which the transaction logs backups should get copied. This folder is often located on the secondary server or common share. Make sure that SQL Server service account on secondary has full permissions to this path.
– You may optionally choose to change the copy and alert thresholds as per your SLA’s
– Now move to Restore tab, under “Database state when restoring backups”, choose the No recovery mode or Standby mode option. If you are choosing Standby mode option, then also select that you want to disconnect users from the secondary database while the restore operation is performed.
– If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
– You may optionally alter the restore and alert thresholds.
– If you want, you may also add a third server as Monitor server instance to monitor this log shipping configuration. You need to decide whether you want to use monitor server or not, because, if you want to add the monitor server later, we need to remove logshipping entirely and reconfigure it with monitor server.
– If you choose to configure monitor server as well, then click on Connect and connect to the SQL Server instance to use as the logshipping monitor server.
– Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server. Under History retention, choose the length of time you want to retain a history of log shipping.
– On the Database Properties dialog box, logshipping tab, click OK to begin the configuration process.
– Verify and make sure that logshipping log backup job is running successfully on Primary server Server01 and is creating the backup file on the backup share we provided to place the log backup files.
– Verify and make sure that logshipping copy job is running successfully on Secondary Server Server03 and is copying the backup file from the backup share to path specified during copy settings for secondary server.
– Verify and make sure that logshipping restore job is running successfully on Secondary Server Server03 and is restoring the backup files.
– Check Logshipping report from SSMS on Server01 and Server03, by right clicking on SQL Server Instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

Now we have successfully setup logshipping from Server01 to Server03, Next thing is to failover AlwaysON Availability Group “PROD_LSTNR” from Server01 to Server02, after which we also want to move the logshipping to happen from Server02 to Server03 and disable logshipping from Server01 to Server03.

Disable the logshipping from Server01 to Server03 by following below steps
Connect to SQL Server instance using SSMS with instance name as Server01 and Server03
Disable logshipping log backup job on Server01.
Disable logshipping copy job on Server03 related to Server01 as primary.
Disable logshipping restore job on Server03 related to Server01 as primary.

Perform AlwaysON Availability Group “PROD_LSTNR” failover from Server01 to Server02. Verify to make sure databases are synchronized and no errors related to AlwaysON.

Now configure logshipping from Server02 to Server03
– Connect to SSMS using SQL Server instance name as Server02.
– Configure logshipping from Server02 to Server03 using steps mentioned above under section “How to Setup Logshipping between two servers?”.
– Make sure logshipping is working fine by checking new backup job on Server02 and new copy and restore jobs on Server03 which are related to Server02 as primary.

From now onwards, you can failover AlwaysON Availability Group from Server01 to Server02 or vice-versa and at same time switch the logshipping to be performed from Primary AlwaysON AG database to Server03, by disabling the logshipping jobs related to AlwaysON AG secondary server.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 

SQL Server 2012 Logshipping Backup Job Completes Successfully but No Backup File Created

I encountered a strange issue while working on SQL Server 2012 AlwayaON Availability Group databases which also has Logshipping configured. After failover of AlwaysON Availability Group databases from primary to secondary, and also after moving the logshipping from new primary server to DR server, logshipping backup job was having issues where the job was completing successfully, but was not creating the log backup files. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02. Logshipping has been setup from Server01 to Server03(This is just same way how we setup logshipping between any two server databases). AlwaysON and Logshipping has been working without any problems after the initial setup.

Now, when there was a planned maintenance to replace Server01 and Server02 with new hardware, we can failover AlwaysON AG to secondary server and also configure or move logshipping from new primary server to DR server. We decided to do this with minimum downtime to production users by moving all databases to one of the server and to perform the maintenance on the AlwaysoN AG secondary server.

As part of planned maintenance, we failed over everything to Server01 and performed maintenance on Server02 and added it back to the AlwaysON AG. Verified and AlwaysON AG databases got synchronized in some time and logshipping also was working fine without any problems.

Now we have to perform maintenance on Server01, so we moved the AlwaysON AG “Prod_LSTNR” to Server02, which was successful and databases are synchronized as well. As expected, there is no logshipping configuration on databases now as logshipping was only setup when databases were primary on Server01 to Server03. We decided to configure logshipping from Server02 to Server03 now, so that logshipping to Server03 happens when the AlwaysON AG databases are either on Server01 or Server02.

Disabled Logshipping Backup job on Server01, disabled logshipping copy and restore jobs on Server03, which disabled logshipping from Server01 to Server03. Took server01 down for maintenance and is no more available now. Configured logshipping from Server02 to Server03 which created new backup job on Server02 and new copy and restore jobs on Server03. All logshipping jobs started running without any errors.

Later, maintenance of Server01 finished and was online and got connected to AlwaysON AG “Prod_LSTNR” and successfully synchronized as secondary.

After some time, we started receiving alerts for logshipping that the copy and restore latency was above the thresholds. We checked and found backup, copy and restore jobs from Server02 to Server03 are all running successfully. Logshipping report shows that there was no backup, copy or restore performed since Server01 was back online. Checked the history of backup job and noticed that backup was not performing any backups, it was just skipping performing the log backup on Server02.

Started verifying the logshipping and AlwaysON settings and noticed that AlwaysON AG backup preferences was set to run backups on any replica with priority of 60 for Server01 and priority of 40 for Server02. As now, we have both Server01 and Server02 online and Server02 acting as AlwaysON Primary, log backups cannot be performed on Server02 due to low priority and that Server01 is online. Changed the AlwaysON AG backup preferences to run backups on Primary Server, after which backup job started creating the backup files successfully. Later copy and restore jobs also starting working without any issues or latency.

You can find AlwaysON backup preferences from SQL Server management Studion (SSMS) -> Expand Always On High Availability -> Availability Groups -> Open properties of Availability Group where the databases in part of -> Go to Backup preferences tab, where we can change the settings.

If we see the issue only from logshipping perspective, we could not have identified the issue, looking into the issue as AlwaysON along with logshipping helped us in identifying the issue.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Unable to perform Backup or Restore to Cluster Shared Volume(CSV) Using SSMS GUI in SQL Server 2014

I had been playing with new features of SQL Server 2014 and I encountered an issue with SQL Server 2014 Cluster Shared Volume(CSV) related to Backup/Restore. Refer, if you are new to Cluster Shared Volume(CSV) whose supported has been introduced starting SQL Server 2014. This is a Windows feature which has been available since Windows Server 2012, but SQL Server only started supporting from SQL Server 2014.

I have setup two node Windows Server 2012 cluster and installed one SQL Server 2014 clustered instance. I have added a disk as Cluster Shared Volume(CSV).

When I try to backup a database, I encountered an issue where I tried to perform backup of a database and normally we see file structure where we can browse to choose the location where to backup the databases, but I see a blank file structure, so nothing there to select any path. If I try to specify the backup path manually to CSV disk, I get an error as below. as mentioned in the error, I have checked permissions to the CSV path, but no issues with the permission. I am able to create databases on CSV and access them, but just could not use the CSV path to perform backups/restores.

C:\ClusterStorage\MSSQL\MSSQL12.InstanceName\Backups

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

After some research, I found out couple of workarounds to perform backups or restores.

Alternative 1 – Use T-SQL for performing backup from SSMS Query Analyzer

Alternative 2 – Follow below mentioned steps

– Add a regular cluster disk(Non Clustered Shared Volume(CSV)) to SQL Server Group in the cluster from Cluster Administrator and then add the new disk resource as dependency to SQL Server Group.

– From SSMS, right click on the SQL Instance properties, go to database settings tab, copy the existing path and save it for future reference and then change the path to the new regular disk which we just added, this is just like how we used to be in SQL Server 2012 or previous versions.

– Now try to perform the backup and you will be able to open the locate backup file and locate database files dialog with out any errors. We can also specify the backup path manually to CSV, but the path still will not be visible from the GUI to browse.

– Later, the new regular storage which we added before could be removed and then change back the default backup location to CSV path which we saved before changing to to new disk in Instance properties -> Database Settings tab.

– You should not encounter any more issues and now you can also see CSV path from GUI to choose path to backup/restore.

There is a Connect bug opened for this issue where we cannot see CSV path using SSMS GUI while trying to perform backup/resstore. You may check on the connect page to see any updates on fixing this issue in coming Service Pack/Cumulative Updates.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 

Unable to Open Logshipping BackJob Properties for Database with AlwaysON AG Created

Recently while working on a production deployment, faced an issue with SQL Server LogShipping. As, it was planned maintenance, we had to find and fix the issue immediately. Writing this blog, so that others can get an idea on what can be done when faced with this type of issue.

Coming to the issue, we had three SQL Server 2012 servers Server01, Server02 and Server03. AlwaysON Availability Group had been setup between Server01 and Server02. Logshipping is setup between Server 01 and Server 03.

We had a planned maintenance where the two servers Server 01 & Server02, hardware was planned to be replaced, so we planned in a way that we will take down one server at a time and perform the maintenance, while the other server will be serving production traffic.

So, we failed-over the AlwaysoN AG with listener name as “Prod_RPT_LSTNR” from Server01 to Server02 which was successful. We disabled Logshipping backup job on Server01, disabled copy and restore jobs. on Server03. Configured logshipping from Server02 to Server03, which completed successfully, new backup, copy and restore jobs started working fine. Now Server01 was taken down for maintenance.

Later, logshipping started failing with errors related to backup path being incorrect. We checked and found that drive where we pointed backup path was removed and we were told that the drive cannot be added back, so we had to change the backup path in logshipping.

We tried to change the path and tried to click on the Logshipping backup job button in logshipping configuration tab, but it was returning below error


TITLE: Microsoft SQL Server Management Studio
------------------------------
SQL Server Management Studio could not save the configuration of 'Server01' as a Primary.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An entry for primary server Server01, primary database ReportServerTempDB does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)
--------------------------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

– After some troubleshooting and research, we found that, when we configured logshipping from Server02 to Server03, we connected to the SQL instance Server02 from SSMS via AlwaysON AG listener name which was Prod_RPT_LSTNR, but later when we tried to change the backup settings we connected to SQL instance Server02 SSMS using server name which was Server02. Now, we connected back again to SQL instance Server02 via SSMS using Listener name, then it allowed us to click on logshipping backup job button and we were able to open the job properties with out any errors.

Running below command on Secondary server, will provide us with the details of logshipping primary database like primary_server, primary_database, backup_source_directory, backup_destination_directory, file_retention_period, etc.

sp_help_log_shipping_secondary_primary
@primary_server = 'SQLInstanceName',
@primary_database = 'DatabaseName'

Running below command on Primary server will provide details of primary database settings

sp_help_log_shipping_primary_database
@database = 'DatabaseName'

Now, important thing to understand here is that, when we configure logshipping in environments where we have multiple ways of connecting a SQL instance, example via AlwaysON Listener name, IP Address, Alias, etc, we need to connect using the same method later as well, if we want to make any changes to the logshipping configuration. This happens because, the SQL Server instance name which we used to connect will be stored in MSDB logshipping metadata and will look for same server name later, so if later we try to connect using another method by specifying different instance name like IP Address/listener/alias, it will mis-match with the metadata and will return errors.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 
1 2