SQL Server Log Shipping Interview Questions.

Which jobs get created after enabling log shopping?

4 jobs get created. Backup job on Primary Server, copy job on Secondary server, restore job on Secondary server and alert job on the Secondary server.

A user started complaining he is not able to access database once failover is done from primary server to secondary server? What is the reason and how to fix it?

The user’s corresponding login might not exist on the Secondary server. We need to create the login of the user in secondary server similar to the login in the primary server.

What are TUF and WRK files?

Transaction Undo File. This file gets created only if the Secondary server is in STANDBY mode. This file holds all uncommitted transactions and SQL Server will use this file to check which earlier uncommitted transactions are committed/rolled back. Accordingly, it will write the data to disk.

Transaction Log backups file extensions will be changed to ‘.WRK’ extension while getting copied from Primary server to the Secondary server. Once the backup file gets copied to the secondary server fully then the log backup file extension will be changed back to ‘.trn’. This helps in blocking restores to happen when the file is getting copied.

What happens if TUF file will corrupt?

Log shipping will fail if TUF file gets corrupted. We need to set up log shipping again to fix the issue.

Can we add new data file to primary database? What happens after we try to add?

Yes, file can be added to the primary database. After adding the file to the primary database, if the same path exists in the secondary server then the file gets added to the secondary database as well. If the same file path doesn't exist in the secondary server then log shipping fails.

Can we add new data file to secondary database? What happens after we try to add?

No, we can't add a new file to the secondary database directly as it will be either in restoring or standby (read only) mode.

Does log shipping support all recovery models?

No. Only FULL and Bulk-Logged recovery models are supported.

How to failover database from primary to secondary?

»    Disable all jobs (backup, copy and restore) on primary and secondary servers.

»    Apply all pending log backups to the secondary server by first copying and then restoring them to the secondary database.

»    Take tail log backup of the primary database with NORECOVERY option. Primary database will go into restoring state after this.

»    Restore all the pending log backups to secondary and finally restore the tail log backup as well with RECOVERY option.

»    This will bring the secondary database ONLINE, now configure log shipping in secondary (to make this as primary).

»    Now secondary (current primary) will start acting as a primary database for log shipping.

»    Now run all the new backup, copy and restore jobs that got created. This will make the old primary as a secondary database for log shipping.

How to find what was the last restored transaction log in log shipping?

There are several ways to find this out:

»    We can use the table “select * from msdb..log_shipping_monitor_secondary”

»    We can check ‘Transaction Log shipping reports’ report in standard reports at the instance level.

»    We can manually check ‘backup and restore reports’ in standard reports at the database level.

»    We can manually verify the backup and restore jobs timings and their history to check when was the last time they executed successfully.

How to apply Service Pack / Hot Fixes when Log Shipping is enabled?

How to install service packs and hotfixes on an instance of SQL Server that is configured to use log shipping?

»    First apply Service Pack on Monitor Server (if you have a Monitor Server).

»    Next apply the same update on Secondary Server (if more than one secondary apply on all secondary’s) of Log shipping.

»    Finally apply the same update on Primary Server.

Can we have different versions of primary and secondary servers in log shipping?

Can we configure log shipping between different SQL Server versions? Like Primary on SQL Server 2012 and Secondary on SQL Server 2014?

Yes, we can configure log shipping from lower version of SQL Server to higher version. But in case of failover you will have issues like we cannot configure log shipping from higher to lower version.

We can configure log shipping from SQL 2012 to SQL 2014. We will receive error similar to below when we try to configure log shipping from higher version to lower version of SQL Server:

Restore failed for Server 'SQL2012'.  (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------

System.Data.SqlClient.SqlError: The database was backed up on a server running version 12.00.2269. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&LinkId=20476

I will keep adding new questions to this list. Please share the questions you are aware of.

#LogShipping Questions, #Log Shipping Interview Questions, log shipping interview questions, Log Shipping, log shipping questions

SQL Server Cluster Interview Questions.

SQL Server AlwaysOn Availability group interview questions.

Thanks VV!!

SQL Server AlwaysOn Availability group interview questions.

Which High Availability features can be used along with AlwaysOn Availability group feature?

»    AlwaysOn Availability group can be combined with Log shipping and Replication.

»    DB mirroring cannot be combined with AlwaysOn Availability group.

Do we need shared storage to create AlwaysOn Availability group?

»    No. Shared storage is not mandatory for creation of AlwaysOn Availability group.

Can we take differential backup in the secondary replica of AlwaysOn Availability group?

»    No. We can only take a log and copy only FULL backups.

How many replicas we can have in AlwaysOn Availability GROUP?

»    1 Primary and 8 secondaries in SQL Server 2016/2014.

»    1 Primary and 4 secondaries in SQL Server 2012.

Which High Availability features support Automatic page repair, if any page becomes unreadable.

»    AlwaysOn Availability and DB mirroring.

How many ALWAYSON Availability groups and AlwaysOn Availability group databases we can have for instance?

»    There is no upper limit as such and it depends on your server configuration. But MS has tested with 10AGs and 100DBs per physical machine.

After adding ndf file to Primary of AlwaysOn Availability group my secondary went to suspended state. What could be the reason and how to fix it.

»    Reason could be file path differences between primary and secondary AlwaysOn Availability groups. If the same file path does not exist in secondary replica then that replica will be SUSPENDED and goes to NOT SYNCHRONIZING state.

»    To fix this: remove DB from the secondary replica of AlwaysOn Availability groups, take FULL backup of PRIMARY database and restore it to secondary, same way restore log backup as well, after restoring all pending log backups then add the secondary DB back to AlwaysOn Availability groups.

How to install service pack when Alwayson Availability Group feature is enabled in SQL instance.

»    Disable automatic failover.

»    First install the service pack in secondary replica.

»    Wait until AlwaysOn Availability groups state becomes SYNCHRONIZED.

»    Manually failover the DB to the secondary replica.

»    Now upgrade the earlier primary server.

»    Failover the DB to the initial primary server.

»    Make the AlwaysOn Availability groups state is SYNCHRONIZED at the end.

What are different availability modes in AlwaysOn Availability groups?

»    Asynchronous-Commit mode:

·         Ideal for DR scenario.

·         High performance.

·         No Automatic failover.

»    Synchronous-Commit mode:

·         High data availability and data protection.

·         Automatic and manual failovers are supported.

References:

https://msdn.microsoft.com/en-us/library/hh510230(v=sql.130).aspx

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

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

I will keep adding new questions to this list. Please share the questions you are aware of.

#AlwaysOn Availability group, #AG, #Alwayson, #AvailabilityGroups

SQL Server Cluster Interview Questions.

Thanks VV!!

The feature you are trying to use is on a network resource that is unavailable. Click OK to try again, or enter an alternate path to a folder containing the installation package ‘sql_bids.msi’ in the box below.

The feature you are trying to use is on a network resource that is unavailable. Click OK to try again, or enter an alternate path to a folder containing the installation package ‘sql_bids.msi’ in the box below.

The feature you are trying to use is on a network resource that is unavailable. Click OK to try again, or enter an alternate path to a folder containing the installation package ‘sql_tools.msi’ in the box below.

While I was working on an SQL Server 2008 R2 Service Pack 3 uninstallation it failed with below errors. Warnings from several forums and blogs came back to my mind like ‘pray to god that your service pack uninstallation will never fail’ , like some old prophecy haunting me. Anyways the result was uninstallation failed and when I clicked on ‘Ignore’ it started progressing further but failed with another same kind of error, clicked on ‘ignore’ and successfully uninstallation failed to uninstall successfully.

To start with, how to uninstall a service pack of SQL Server 2008 R2.

Irrespective of any service pack the uninstallation steps remain same. Here I am uninstalling SP3 of SQL Server 2008 R2 version.

GO to Control Panel ==> Add/Remove Programs ==> View Installed Updates

Now Right click on Service Pack 3 for  and select ‘Uninstall’

That will take you to this window:

Select the features for which you want to uninstall SP3, ideally you need to select all the features:

  

After clicking on “Next” it will take you to ‘Files in Use’ window and once its done click on next

Here you will see ‘Ready to remove features’ window. Here verify all the features got selected and correct instance got selected and other parameters are selected correctly.

Now click on ‘Remove’ and this will start the uninstallation process. You will see the progress as below and even the uninstallation completes successfully if there are no issues found.

Coming to my case obviously it was looking for some disk like ‘Z:\x64\setup’ and obviously, there is no such disk in the server. I searched under 'Programs *' folders if there is any ‘setup’ folder under any of ‘x64’ SQL folders but I haven’t found any. There are different solutions suggested online but as a first step, I copied the setup files of the same version as my current instance which is SQL Server 2008 R2 to the server. Then I searched for the ‘\x64\setup’ and it was indeed there.

Now I started the uninstallation again and when I encountered the above error, this time, I clicked on ‘Browse’ in the error window and gave the path of ‘\x64\setup’ folder from the setup files I copied. After copying, I clicked on ‘ok’ and the uninstallation started progressing and I did the same for the second error as well.

This time, uninstallation completed successfully.

Thanks VV!!

How to convert PowerShell script into an exe.

Task: I want to run a script regularly on different servers.

We face this kind of tasks several times, basic examples like to find when the server was last rebooted or what are the current free spaces of all disks of a particular server and so on.

So basically I have a script to do some ‘xyz’ work and I want to use the same script in many other servers and even my team members also should be able to run it when needed. So the better way to do is converting the PowerShell script into an exe and distribute the exe to all my team members or place it in a shared location where my entire team has access.

How to convert PowerShell script to exe:

1.    To do this first you need to install ‘PowerGUI Script Editor’ if you don’t have it already.

2.    Open ‘PowerGUI Script Editor’: Start à All Programs à PowerGUI à PowerGUI Script Editor

3.    Open new page by pressing ‘Ctrl+N’. Paste the script you want to convert to exe.

4.    Execute the script by clicking ‘Play’ button which will be on top or by pressing shortcut ‘F5’. This is just to test script is working without any errors. My sample script to get last reboot date time is giving results as expected.

             Now steps to convert PowerShell script to exe

5.    Click on ‘Tools’ and select ‘Compile Script’ or shortcut for this is Ctrl+F9. Screenshot below:

6.    Once compile script window opens give the path to save the script as exe, as shown in screenshot below:

7.    Now you have the exe created for our script.

8.    Now you can run your script by just double-clicking the exe. This exe will run fine in any of the machines where PowerShell is installed.

Please share what are the steps you follow to convert your scripts to run on different servers.

Thanks VV!!

How to install NetFx3?

While installing SQL Server 2012 SP1 on Windows 2012 R2 server installation fails with below error:

‘Error while enabling windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again.’

Reason is ‘.NET Framework 3.5’ doesn’t come inbuilt with Win 2012 R2, we need to install it.

Follow below steps to install Netfx3:

Open server manager in the server and click on ‘Add Roles and Features’

Click on ‘Next’

In ‘Installation Type’, by default ‘Role-based or feature-based installation’ will be selected and no need to change anything. Click on ‘Next’.

In ‘Server Selection’ make sure correct server name and IP address of your server is showing. Click on ‘Next’

In ‘Server Roles’ no need to select anything, just click on NEXT.

In ‘Features’ select ‘.NET Framework 3.5 Features’ and click on NEXT.

In ‘Confirmation’ tab you need to specify the correct source path, for this click on ‘Specify an alternate source path’

It will open new window and here in ‘path:’ specify the path where you have the Windows 2012 R2 set up files are there. The general location would be ‘G:\Source\Sxs\’

After giving the correct path click on ‘OK’ and click on ‘Install’.

This finishes the installation of Netfx3. Once you install this you can proceed with SQL Server installation which failed earlier.

Thanks VV!!

13 basic SQL Server Cluster interview questions you should know.

SQL Server Cluster interview questions. Set 1

MSCS(Microsoft Cluster Server):

·         What is Cluster Service? Or What is Microsoft Cluster Service?

·         Main component is Cluster Service, if this is down cluster itself will be unavailable. This service communicates with Resource Monitor and it controls cluster activities and performs tasks like failovers, managing configuration, facilitating communication between cluster components. It tries to minimize the downtime during any failover or failure. Resource monitor is an interface between cluster service and cluster resources.

·         How MSCS verifies cluster resources/service are up and running fine?

·         MSCS cluster manager uses 2 functions LooksAlive & IsAlive to check the availability of the SQL Server resource. LooksAlive checks service status through Windows NT Service Control Manager every 5 seconds. IsAlive checks every 60 seconds by connecting to SQL as a user probe to perform a simple query. These default timings can be changed.

·         Which command MSCS run frequently?

·         MSCS connects to SQL using the MSCS Service Account for IsAlive checks. It should have public rights to SQL and it runs ‘select @@servername’ every time.

·         What is pending timeout in a cluster?

·         Pending Timeout, it can be either Offline pending or Online Pending, is the amount of time application or service required to go offline or online. The default value is 3 mins (180 secs) and it can be changed.

Private and Public Network:

·         What are Private and Public networks?

·         Public network is for internal communication of cluster nodes. A private network is for communication to the outside. Regular internal communication would be heartbeat between nodes.

Quorum:

·         What is Quorum?

·         What is the use of Quorum?

·         Quorum is a cluster concept that store cluster resource information and configuration details in a log file ideally quorum.log. The main use of quorum is during communication failure between nodes.

·         Quorum configuration will have the threshold for the number of failures a cluster can withstand before going down. If any failures happen beyond the threshold cluster will stop running.

·         During any failure, quorum log will be referred based on the voting algorithm, the cluster will make sure cluster services are running on only one node and make sure only one owner for the group. This helps in avoiding “Split” or “split-brain” scenarios.

·         What is Split-Brain or Split in Cluster?

·         Split or Split Brain is a scenario during communication failure between nodes, each individual node tries to bring the resources of another node online assuming the other node is down.

·         What is the recommended size of a Quorum disk?

·         512MB is the recommended size by MS for Quorum disk. Most of the online resources suggest having 1GB size disk for the quorum.

·         It is recommended not to place any IO intensive programs in the quorum.  Heavy input/output traffic from another source could interfere with the cluster's ability to write to the disk, which may cause the quorum resource to fail. If the quorum resource fails, the entire cluster may fail as well. 

·         What are types of Quorums?

·         Node Majority, Node & Disk Majority, Node & File Share Majority, Disk Only Majority are the 4 types of Quorum.

MSDTC(Microsoft Distributed Transaction Coordinator):

·         Do we need to install MSDTC while installing SQL Server Cluster?

·         MSDTC is required if you are using DBEngine & SSIS, Workstation Components or distributed transactions.

·         If only DBEngine or only SSAS is running then MSDTC is not required.

·         How to connect to a failover cluster instance?

·         You must use the SQL Server failover cluster instance name to connect to the SQL Server failover cluster, not the name of the node.

·         Which services of SQL server are cluster unaware?

·         SQL SERVER REPORTING SERVICE (SSRS) and SQL SERVER INTEGRATION SERVICE (SSIS) are cluster unaware.

Please add other important questions related to SQL Server cluster in the comment section below which I have missed.

Thanks VV!!

SQL Server Cluster Interview Questions.

SQL Server AlwaysOn Availability group interview questions.