Wednesday, August 17, 2016

Extremely slow log shipping restore in standby mode ...


Recently I was working on an environment where the requirement was to use a log shipped secondary database for reporting purposes. The catch here was that the restore of all the transaction log backups needed to complete within a fixed time.
I understand that the restore of transaction logs are dependent on a number of parameters like disk performance, number of transactions present in the log backup, resource availability on the server where the restore is occurring, But considering that all the factors remain constant, there is a basic difference between a secondary log shipped database in No Recovery and in Standby mode. In Standby mode, a partial recovery is done with the help of a transaction undo file to get the database into a read-only state. This partial recovery operation and undo that occurs while performing the next log restore (with the use of the transaction undo file) requires time and resources and can slow down the time of restore for each transaction log backup that is restored on the secondary database, especially if there are resource contention issues on the server.
There can be a significant amount of time saved when operating usingnorecovery as compared to standby mode for log shipping. Since it is not supported to directly modify the log shipping jobs, you can create your own job which executes the necessary T-SQL commands and invokes the necessary log shipping jobs in the correct order.
The correct flow of events would be:
1. Change log shipping restore mode to norecovery using the stored procedure sp_change_log_shipping_secondary_database
2. Start the log shipping restore job
3. Change the log shipping restore mode to standby using the stored procedure sp_change_log_shipping_secondary_database
4. Initiate the log backup job on the primary server
5. Initiate the log copy job
6. Initiate the restore job in the same order to ensure that the new restore mode is affected as an operating mode changes only after a new transaction log backup is restored.


Tuesday, February 09, 2016

Free SQL eBooks on 2016

Just a heads-up that Microsoft have published 2 new free eBooks for 2016 as follows:
-          Introducing MS SQL 2016
-          Enterprise Cloud Strategy

They can be downloaded from the following location:

Wednesday, September 23, 2015

Access locked out SQL instances, i.e. sa account or windows/sql logins with sysadmin passwords are lost or disabled

David Lister from SQL Services ...   Info on Breaking into SQL ;) 

PSExec tool gives you an option to run a remote process or an application using System account, if SYSTEM account has permission on that application. Although there are many more features that are 

For example, if SQL Server has NT AUTHORITY\SYSTEM as one of its logins, then you can use this account to login to SQL Server. However, you can’t use SQL Server Management Studio or sqlcmd to login to SQL Server using NT AUTHORITY\SYSTEM. Try it yourself!

However, PSExec gives you an opportunity to login to SQL Server using NT AUTHORITY\SYSTEM. Once you download PSExec tool bundle from http://technet.microsoft.com/en-us/sysinternals/bb897553.aspxuse command prompt and type following command:

D:\PSTools>psexec -i -s "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Here,

–i allow the program to run so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session.

-s runs the process in SYSTEM account.

The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM. If NT AUTHORITY\SYSTEM has a login authority to the server, you will be able to login to the SQL Server. If you do not use “–i” switch, you won’t be able to launch management studio! So, make sure you use –i and –s both!

However, if there is a deny to this login, for example because of Trigger etc., then you won’t be able to login to SQL Server using this technique.



Saturday, January 25, 2014

2012 Feature Pack

The SQL Server 2012 Feature Pack is a collection of stand-alone packages which provide additional value for SQL 2012. 
You can download the feature pack from here: http://www.microsoft.com/download/en/details.aspx?id=29065