Automated batch update or delete operation in SQL Server

If we perform update or delete operation on huge number of rows in a large table at once, there are chances that the SQL Server would freeze or would cause outage. And if we try to kill the transaction in the middle, we might end up in a more dangerous “rollback” state. That’s why I spent some time to build this script that helps in automated batch operation in small chunks with constant check for query performance. The script would increase the chunk size based on the time taken by the each execution so that it would end at maximum achievable chunk size that has adequate performance.

Real Time Service Monitoring with vbscript

Service monitoring sometimes become inevitable for any server admin. Here is a script to keep watching a windows service for any change in status. The script will alert you as soon as a service shuts down or starts up with no latency using minimal resources. The sample script here is to monitor sql server related services. You could alter the text pattern as marked to monitor your own service. Save the script to a vbs file and name it like For eg.”ServiceMonitoring.vbs” and double click the file to execute. The script keeps running in the background scanning the service status and sends out HTML email alert if it finds any change in the specified service status.

List out and script all indexes in a database

Its quite common that when there is some upgrade, change or release in application, there are chances that our database objects get modified. It may drop some indexes too hampering performance. I had similar situation in my profession. I had creating multiple indexes to find tune database performance, but then there was an application patch release. All those indexes we manually created were dropped by the application. Soon, there was blocking, deadlocks, long running queries, high cpu and performance became pathetic. Later it was understood that all those custom index we DBAs manually created were mercilessly removed by the application. đŸ˜„

Continue reading “List out and script all indexes in a database”

SQL Server 2012 Daily Health Check

Being a DBA, it is good to have some kind of automated health check report that gets delivered to inbox with quick glance of DB health. Hence this script will be very useful. Copy this script and schedule as a SQL agent job. Provide the mail profile and recipient addresses. The script was built with help from several colleagues and references from different SQL forums.

Continue reading to view and copy the script. Since the script is too long, it could not be posted in homepage.
Continue reading “SQL Server 2012 Daily Health Check”

SQL Server 2014 Licensing

Here I would like to help for common question that every DBA has when it comes to SQL Server Licensing.

Refer http://www.microsoft.com/en-in/server-cloud/products/sql-server/buy.aspx for different licenses available.

FAQs:

1. What is Per User license?

Ans) Its not the number SQL users that could be created. Its the number of people who use the SQL Server on a server.

2. Per core license:

Ans) Its the number of physical cores placed in sockets on a physical machine. Its not the number of cores you see in task manager.

Auto restore multiple databases in One shot using powershell

Below script helps restore several databases on to a SQL Server instance in one go. We just have to place all the .bak files in a folder and execute the script. before running the script, its required to import SQLPS in the powershell prompt as below.

Save below script as restore_all.ps1

Execute the script like in below example.

Automatically add Partition for Partitioned Tables

This stored procedure helps automatically create new partition on a new file group to an already partitioned table. I have tested the script in my testlab. Please review and let me know for any corrections if required.

Continue reading “Automatically add Partition for Partitioned Tables”

SQL Server 2012 / 2014 Always-on in Microsoft Windows Azure

We have heard enough of the new feature in SQL Server 2012 that replaces our conventional Database Mirroring notion. It is the Always-on Availability groups feature. I have carried out several tests setting up Always-on on my virtual data-center in my laptop. One fine day at office, I was assigned a project to build an Always-on availability group in Microsoft Azure.

Let’s first go through the prerequisites.

  1. Availability group replicas must reside in a single Cloud service.
  2. Availability group Listener IP should be same as the public Cloud service IP assigned to each replica.
  3. Two endpoints has to be created for the Cloud Service in Azure dashboard. One for the probe port(Through which the Azure Load Balancing services monitors the health of each virtual machine) and one for Always-on Listener port. Probe port should be different from the Listener port. For eg. Give 59999 for Probe port and 1433 for the always-on listener port.
  4. Install the hotfix 2854082 to enable SQL Server Availability Group Listeners on Windows Server 2008 R2 and Windows Server 2012-based Microsoft Azure virtual machines.

Limitations in Azure:

  1. Each virtual machine in Azure could have only one Ethernet card and one private IP. This limitation stops us from using a private IP for Always-on availability group. Even if we give a private IP for the listener, the IP will not ping or connect over the network.
  2. The client application must reside on a different cloud service than the one that contains your availability group VMs. Azure does not support direct server return with client and server in the same cloud service.
  3. Only one availability group listener is supported per cloud service because the listener is configured to use the cloud service IP address.
  4. If you are creating a listener for a hybrid environment, the on-premises network must have connectivity to the public Internet in addition to the site-to-site VPN with the Azure virtual network. When in the Azure subnet, the availability group listener is reachable only by the public IP address of the respective cloud service. Ref: http://msdn.microsoft.com/en-us/library/dn425027.aspx

I went ahead and started working on it. I asked for 4 virtual servers.
Continue reading “SQL Server 2012 / 2014 Always-on in Microsoft Windows Azure”

Monitor Page splits using Extended Events session in SQL Server 2012

There are several ways to configure and start monitoring page splits on SQL Server. But now in 2012, we could make use of extended events to easily track down page splits.

If you do not want to go through the session wizard, I am sharing the script with which you could create the session in one go.

Continue reading “Monitor Page splits using Extended Events session in SQL Server 2012”

Replication – Adding New Article without Generating Snapshot – By Nideesh

Below article on replication was written by my colleague Nideesh K Vasudevan. I would like to thank him for his easy to understand input on this topic.

There were lot of requirements in replication to add new article with out
running snapshot. Some article explained ‘how to include only the newly
added article in the snapshot with out generating snapshot of all the
article”.

All my play only with different publication properties gave me failed
output but a test to also play with subscription property worked!!!!!!!!!!

3 properties that we need to consider are 1) Immediate_Sync
2)allow_Anonymous in the publication properties and important one is 3)
@Sync_Type in subscription properties.

Immediate_Sync and allow_anonymous publication properties

1) The above 2 values do not determine if a snapshot is needed for a new
article, but it determines if snapshot need to be generated for all the
article or only for new article.
2) If both the above options are enabled/true and when we generate a
snapshot after adding new articles, the snapshot will be generated for all
the articles (new article+old article).
3) If both the above options are disable/false and when we generate a
snapshot after adding new articles, the snapshot will be generated only for
newly created article. (only for new article)

@Sync_type – Subscription property

1) The above option determines if a snapshot needs to be generated for
newly added article.
2) If the value of @Sync_type is set to ‘none’ or ‘Replication support
only’ then its enough we just create table structure on both publisher and
subscriber and enable the new table in the publication. When the new data
comes in, it will get replicated automatically.
No snapshot is needed in this case and the application is working with the
above option (@Sync_type=’Replication support only’). Hence adding new
article by creating table structure on publication and subscription got
replicated with this option enabled.
3) If the @sync_type option is set to automatic then for sure we need a
snapshot for newly added article. – Immediate_sync option determines if
snapshot need to be generated for all article or only new article.

http://technet.microsoft.com/en-us/library/ms181702(v=sql.105).aspx – Use
this link to see about @sync_type option.