Just a quick news. Starting from April 2019, the Sql Server release service blog has been moved here: https://techcommunity.microsoft.com/t5/SQL-Server/bg-p/SQLServer/label-name/SQLReleases
For every Database admin, ensuring the smooth performance of the SQL Server is a headache. They need to perform various tasks and tricks for a productive and fast SQL database. One such common task is to keep the Index fragmentation in check. While this is a challenge for the DBAs, Index Fragmentation can be controlled by reorganizing and rebuilding.
Rebuilding Index in SQL Server is the method often used when the fragmentation level goes higher. If you want to improve the performance of SQL database, you have to rebuild the index. But the question is when and how to perform this task. This write-up will focus on the best practices in rebuilding index of SQL Server. We will also discuss when is the right time to conduct this task easily.
Rebuilding Index in SQL Database – Know Why and When to Perform?
It is known to the SQL Server users that database performance gets significantly hampered if the SQL database becomes full of fragmented indexes. As Index Fragmentation keeps increasing along with the database usage, admins should be careful about the fragmentation rate. Depending on the database size, DBAs should fix a schedule when the index fragmentation will be checked using “sys.dm_db_index_physical_stats” command. When this command is run, users can learn about the percentage of index fragmentation in SQL database.
If the percentage is as low as 10%, no additional action is needed. If the level is between 10%- 30%, you have to rebuild the index to enhance its performance. Only when it crosses the 30% bar, the question of rebuilding index in SQL Server comes to the scene. However, some SQL Server experts recommend performing Index rebuilding only when the fragmentation rate reaches 80% or 90%. Since rebuilding index is a resource-consuming task, database admins should consider how much the fragmentation affects the database performance before rebuilding the index.
Best Practices in Rebuilding Index in SQL Server
If you are interested in rebuilding indexes, it is better to follow certain basic rules, known as the best practices in Index rebuilding. For example, if you are using any SQL Server edition other than the Enterprise Edition, then this task should be done offline. Since the feature of Online Index Rebuilding got introduced in SQL Server 2005 Enterprise Edition, any earlier version users need to perform it offline. With Online index rebuilding, the index never goes offline and table also remains available for use during the process.
In case of SQL Enterprise Editions that support online index rebuild, the online process takes more time than offline rebuilding. That is why, offline index rebuilding is highly recommended if the company can afford downtime. To minimize the downtime, it should be done when minimum people are using the database or it should be done along with scheduled maintenance tasks. Therefore, nighttime is perfect to schedule index rebuilding. It is also suggested to conduct this task at least once a week. If you do not have any maintenance window for your database, you can try doing Online rebuilding.
Rebuilding Index in SQL Server consumes a lot of resources. So performing it too regularly will create inconvenience to the database with scarce resources. Database admins should consider their database capacity and resources before scheduling the index rebuilding.
Note: Get to know How to Deal with Index Corruption in SQL Server
Index fragmentation is a common situation in all SQL databases. The productivity of the SQL Server depends on the level of Index fragmentation. Only a controlled Index fragmentation rate is desirable for smooth functioning of SQL Server. Among many approaches that keep the fragmentation level in control, rebuilding Index is a popular one.
In this process, logical index fragmentation is removed, statistics get updated and database page space is emptied. Therefore, users should include this in their maintenance scheduling window. They can also learn about the best practices in rebuilding index in SQL Server from this post. Also, consider the situation of your own SQL database to customize the rebuilding process.
Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2, SP3:
…Stay Tuned, Merry Christmas and a Happy New Year! 🙂
Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2016 SP1, SP2, 2017 RTM and 2014 SP2:
…Stay Tuned! 🙂
Automation, proactive monitoring, repeatability, reducing waste of time and technical debt. This is something you should know about when trying to do some DevOps.
Why automation? Because you can reduce technical debt and the number of failures that can happen with a manual interaction. You can create environments using a provisioning procedure without falling in common pitfalls like security misconfigurations, wrong configurations and botched monitoring.
Talking about SQL Server, immediate and proactive notifications represent a great step forward toward automation.
We automate whenever we want to stop doing a bunch of recurring or tedious steps manually. At the same time, we are also improving the overall quality and we are reducing the amount of things that can (and will) go wrong.
We are also optimising on how we use our time, because we can just ignore what the automation is doing for us and focus on that something that really needs our attention.
Finally, in this modern and notification-based world, emails generate too much white noise to deal with. In this article, we will learn how to integrate SQL Server tasks’ notifications with one of the most used collaboration tools: Slack.
Keep in mind that this is not the only way to get this done. This guide would help you to better understand why we’re doing this (eventually why DevOps), and not strictly how to do it, even if we’ll see a real working example.
Note: Don’t use SQL Server Express edition. This version doesn’t support any SQL Server Agent task as well as the Database Mail, which we’ll need hereafter. Also, about slack, you must create a paid account, because the integration described below will not work with a free profile.
In order to send emails, we will use an SMTP sever. It can be either a private Microsoft Exchange, PostFix, or any other on-premises solutions, together with a cloud delivery service, like SendGrid, SendInBlue, MailJet, or Office 365.
In a team like mine, which uses chat as a daily communication driver, centralizing every business and technical message could be a great step forward for the members of the team in terms of awareness and knowledge sharing. Business roles can use that tool as well, so we can chat to each other switching topics between tech and functional discussions. It’s just a matter of how Slack (in our case) is configured with channels and naming conventions. A good setup helps us to better organize our meetings, small talks and any other topic related to implementations. This is a cool argument to speak about, but a little bit out of the scope of this guide. We will focus on notification bots instead.
SQL Server is able to send emails with its built-in features out-of-the-box, but we’d like to centralize every notification inside Slack, gaining the following advantages:
- Instant notification
- Tailored focus (custom sound instead the same popup for all the incoming emails)
- Quickly involve people that are not following the channel by a mention
- Relay the problem description within the chat
- Take actions as soon as the notification is received
The proposed solution
Now, how can we send notifications from SQL Server in an easier way than using custom code or a Slack incoming webhook? Is there any integration or a Slack app? Yes. And guess what? I think you’ll like it because you don’t need to write a single line of code, and you don’t need to choose between CLR, PowerShell or any other language. It’s ironic, but the integration is called “Email”.
The purpose of this article is just to describe Slack as a collaboration tool. Further details are provided here. As we said before, the following samples work only if you get a Slack account.
The Slack Email integration
This is the app to work with: Email. Its configuration is based on a four-step wizard:
- Select the channel (or create a new one).
- When added, set the name and a short description of the new contact (bot) in Slack.
- Change the avatar (it’s important to recognize the bot at a glance)
- After saving, copy the email address the app created for you.
A word about the “Hide this address” checkbox: this is useful if you want to hide the address to any other member of your workspace. You will be the only user able to read it if you check that box.
Type of SQL Server notifications and setup
As a DBA, we’re managing the following types of notifications on a daily basis:
- SQL Server built-in and custom Alerts
- Job execution status
- Integration Services custom emails (within the packages)
- External monitoring tools (which monitor SQL Instances)
With the exception of SSIS custom emails and external monitoring tools, everything is managed by Database Mail. This is a lightweight layer that allows us to send emails directly from a SQL Server Instance, connecting to a SMTP server.
To setup Database Mail you can follow this guide from Microsoft Documentation.
Once this is up and running, you can manage the notifications using SQL Server Operators. An operator is an alias managed by the SQL Server Agent which you can use to send emails and other types of messages, like pagers and Net Send.
Creating an operator is simple, just invoke the following system stored procedure:
USE msdb; GO EXEC dbo.sp_add_operator @name = N'<name here>', @enabled = 1, @email_address = N'<email here>'; GO
If you’re asking what email address you should use, it’s easy to say. You must fill the @email_address parameter with the address returned by the Email app integration for the channel you will send to (email@example.com in the example above). But, what about the name parameter? In my opinion, the best name is the one that helps us to understand where the message will be sent to. Suppose that we’d like to notify something about some index maintenance jobs. We could call the operator Slack Indexes Maintenance, Slack Indexes Maintenance Operator and so on. With such names, you will immediately know what we are going to send to Slack as the topic is related to index maintenance.
Thus, you’ll get the following snippet:
USE msdb; GO EXEC dbo.sp_add_operator @name = N' Slack Indexes Maintenance Operator', @enabled = 1, @email_address = Nfirstname.lastname@example.org'; GO
Slack channels naming considerations
I’d like to share with you my thought about the channel naming conventions. The principles to follow when naming channels, are:
- Readability (clear for everyone)
- Awareness (know what)
- Style and Rules (know how)
- Repeatability (keep using it from now on)
That being said, if the channel name describes a single action (like indexes maintenance in the above example) the operator which will send notifications should be unique. The reason is simple enough: we know that Indexes Maintenance Operator is sending messages to #sql-idx-maint-alerts (readability) and everyone knows that this is a one-to-one communication between a SQL Server Operator and Slack (awareness). Everyone knows that the “sql” channel prefix indicates SQL Server-related notification and the “alerts” suffix indicates that is an issue to pay attention to (style and rules). At the same time, everyone knows how to do the same with another pipeline of messages in the future (repeatability).
On the other hand, using a general purposes channel, like #sql-maint-alerts, allows us to be ready to future changes. Suppose that index maintenance will not be the only operation we’re executing in our servers (and typically isn’t). Does it make sense to create a new operator called for example, Database Concurrency Check Operator, which sends to a specific purpose channel? Clearly not.
In the end, a generic purpose channel gives the opportunity to hold more than one topic. All the notification sent to that channel should be, let’s say, of the same category to avoid too much generalization.
These solutions (one channel for more operators or a one-to-one solution) work equally well, it’s just a matter of how you’re designing your Slack channels. I suggest to avoid the “one channel to rule them all” pattern, because you’ll get thousands of mixed notifications without any clear idea behind them. After all, a noisy channel with messy content is something that will not be considered for a long time and will be eventually dropped.
Alerts are triggers that communicate to an operator that something went wrong. This Brent Ozar’s article offers a good list of alerts that need attention. Here you can find their descriptions, based on severity. The binding is straightforward. All you need to do is to link the operator to the alert:
When one of those events occur, an operator is alerted. Then, it sends the message using its setup – in our scenario, an email. If the operator uses the Slack Email app, the email will be sent to the Email app, and the integration will redirect it to Slack.
Binding job execution statuses
Let’s see how we can use the notification mechanism to monitor SQL Server Agent Jobs. Each job lets you configure what to do in case of failure, success or completion of its execution. The binding is similar to the alert’s one:
Once the result is collected, based on the configurations you’ve set up, this job will send an email to the app.
Binding custom Integration services email
In order to send an email from a SQL Server Integration Services package (aka .dtsx) you need to configure the SMTP server within the package itself. This is a little out of scope, because it’s not really a SQL Server notification. You can leverage the power of SSIS and prepare a rich HTML-formatted message; the result is nice to read and informative like in these examples:
Cool stuff, isn’t it? It’s simply a .NET script in SSIS, which uses the System.Net namespace. Although the SSIS package is executed within a SQL Server Agent job, the default notification message that SQL generates is not easy to read. The message you always get is:
JOB RUN:<name> was run on <date/time> DURATION: x hours, y minutes, z seconds. STATUS: Failed. MESSAGES: The job failed. The Job was invoked by Schedule xyz (<name>). The last step to run was step xyz (<name>)
Decorating the package with a more detailed email will improve the readability and the accuracy of our notifications.
Setup an external monitor for notifications to Slack
SQL Server is often (hopefully) monitored with specific counters. We’re using PRTG monitoring tool to measure them, and when a baseline changes and a threshold is hit, we send notifications to Slack. How? Again, sending to the Email app integration, specifying the right channel to send to and getting this:
The above report has been truncated. In a complete version of it, you’ll find the complete details of the measures, like the name of the servers, the sensors links, the grid with all the results, and everything you can see inside a PRTG admin portal.
Let’s see a more complete example, using a SQL Server alert. We’ll use the Severity 17 alert. Severity 17 is simple to raise and it describes a missing or insufficient resource when executing a command:
USE msdb; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 017', @message_id=0, @severity=17, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO
Set the Response for the Severity 17 alert to “Notify Operator”, via email:
Run the following severity 17 based t-sql script:
RAISERROR(N'An error occurred Severity 17:insufficient resources!', 17, 1) WITH LOG; --don’t forget to use WITH LOG GO
Go to your Slack account. If you’ve configured everything correctly, you should see the following:
Did it work? Great! If not, continue reading.
If you don’t see the notification try these steps:
- Be sure that your Slack account is confirmed (its email too)
- Once the Slack account is confirmed, check if the channel still exists (CTRL+K -> name of the channel)
- Click on “Customize Slack” in the drop down menu of your Slack client/webpage, then click on Customize App in order to check whether the Email integration is active or not:
- Verify Database Mail configuration (try to send the test email)
- Verify the operator configuration (is it enabled?)
- Verify the alert configuration (did you bind the response with email to the operator? Is it enabled?)
- Verify the SQL Server Agent email profile configuration (is it enabled? Is it the right one?)
There are some disadvantages when using this kind of integration. For example, you cannot customize the message, unless you do it inside a .NET script. The Slack Email Address is publicly available, albeit hard to discover, so anyone can send message to your private slack channel by sending emails to that special address. Again, you cannot send the notification to more than one Slack channel or outside of the Slack world. In reality native SQL email notifications show the same limits, where email addresses of distribution lists are similar to Slack channels.
For our purposes, this is a very-low-effort automation with a high return in terms of value. With a couple of clicks, you can setup an email address representing a Slack channel, and, with little more, you can get notifications in a smart and comprehensive layout.
Everything is kept inside the collaboration chat tool we are using massively, every day. In the end, this example embeds one of the core DevOps principles (automation) and provides huge cross-role and cross-team value, especially when the channels include also network and server teams.
I hope that you’ll give this a try.
Years ago, with SQL Server 2016 release, Microsoft came up with a separated brand new version of SQL Server Management Studio. It’s been a happy day for the SQL Server community and database developers.
Shortly afterwards, our company started to migrate every instances from older version of SQL Server to the 2016, using SSMS 17.*. Developers have already jumped into Visual Studio 2017 and everything seemed to work like a charm, until we started deploying integration services via the new SSMS, after we converted them to 2016 TargetServerVersion (which is NOT the Project Version).
The TargetServerVersion is the SSIS version, also for the deploy operations, while the Project Version setting tells to Visual Studio how to open projects based on .dtproj specifications on the XML projects definitions.
Some days ago I’ve realized that after updating to the latest build of SSMS (17.8.1), the .ispac deployment is actually executing the latest build of the Integration Services Deployment wizard (aka ISDeploymentWizard.exe). As a result every deploy of .ispac files, regardless trying double click or deploying directly from the Integration Services Catalog, the SSISDB, failed with one of the most scaring error message ever:
“[…] The Script Task <unique_name> uses version 15.0 script that is not supported in this release of Integration Services […]“
What? Why? I’ve a workstation with SQL Server 2016 and the related Integration Services 13.0:
The computer I’m speaking of has the same build of mine, nobody has installed any other Integration Services versions anywhere. Andy Leonard explained this behavior in this blog post. Unfortunately, in my scenario, I cannot solve the problem.
Let’s try to explain better.
The workstation I’m working on has SQL Server 2016 (build 13.0.5149.0) and Integration Services 13.0 on Windows 10 Pro. I’ve got a simple package with a single script task which does literally anything:
As Andy suggested us, I’ve changed the TargetServerVersion to SQL Server 2016, so I’ve got C# 2015 compiler for scripts:
First execution and deploy
Executing it locally, nothing happens, but it happens in a green way (success):
Ok, now we’re going to deploy to the other machine, generating the .ispac file and double clickin on it. It’s important to make sure that you’re double-cliking the .ispac file. Do not right click on the Integration Service Catalog project folders because SSMS will execute the latest build of ISDeploymentWizard.exe by design.
When double clicking, the app selector should use the version related to the TargetServerVersion setting of the .ispac. This works for many of my other computers. But for one of them, here is the screen:
Hey! This is 2017 also when clicking on .ispac file. Let’s try to deploy using the new tool. The deploy succeeded.
And now, let’s try to validate the package execution via SSISDB, right clicking on the project itself and selecting “Validate…”. This is the result of the empty script package validation:
I’ve tried on six different machines, five ran successfully and ONLY ONE returned the above error message. Still stuck in the middle.
What have we changed?
Just the setup of SSMS (17.8.1) updating the 17.7 one. Once again, the same setup on all six machines. Five by six worked, this one is trying to kill me.
For some strange reason, something (I assure you all, not someone) has changed the registry in the .ispac application association, maybe when double clicking for the first time the file in a pending reboot (?). We’re still investigating, since we used to avoid any change in production without permissions and processes. That said, it’s weird. And it was so difficult to get.
Easy to say, now that we’ve figured out the root consequence. Not so good, but changing the registry on the key HKEY_CLASSES_ROOT\.ispac with the 130 executable (IntegrationServices.ProjectDeploymentFile.130) fixed the unwanted behavior. The key has been set to IntegrationServices.ProjectDeploymentFile.140 right after the update from 17.7 and 17.8.1.
Instead of changing it via regedit, you can try an “open with…” with “use default” checked in order to force the association between .ispac file and the right ISDeploymentWizard.exe version. But this time, in this machine, it didn’t work. This is the reason why I tried the regedit action.
I have to say a big thanks to Andrea Amantini, one of my peer, which is well known for his ability to find out “a needle in a haystack.”. Strange things happened here. A combination of Murphy’s law, a sort of “black” friday and a pending reboot. Hopefully this helps someone, at least.
The SQL Transaction Log reader is a standalone utility, which helps to view SQL server transaction log files without any difficulty. It is capable enough to preview LDF activity, including Transaction, Time, Name, Query and Table Name.
Moreover, it allows a user to fetch and display all records from the live database. This application offers an option to filter and export as SQL database, as SQL script or CSV file format. It supports to analyze one or more NDF data files without losing a single bit of data. Also, it is compatible with all latest Windows versions, including Windows 10 and its below versions.
SQL Transaction Log reader Tool: Demo vs Licensed
The SQL Log viewer is available in two different versions i.e., Demo and Licensed Version. Let us have a look:
- Demo Version: The demo version of the software is freely available for download and supports to preview of SQL Server Transactions (INSERT, UPDATE, DELETE), preview Login users that has made changes on database tables, transaction Query, Time. But, doesn’t allow to export Transaction details for recovery purpose.
- Licensed Version: The licensed version of SQL Transaction Log reader tool permit users to preview all the records within the database. Moreover, it can open and read the complete log activity in SQL server database. Also, it allow you to export all tables data along with deleted records direct in SQL Server Database or SQL Server Compatible script or CSV format.
What is New Added in SQL Log Reader Tool to Explore LDF
- Allows to View Multiple LDF Files: SQL LDF File Viewer permits you to open and read multiple transaction log files activity in single go. For this, you just need to add parent .ldf file of your database, if you are scanning with offline option. The software will scan and preview transaction activities of all other .ldf database files of that database.
- Analyze Same record Update
Multiple Times and then Delete: SQL log file reader allow you to preview the transactional activity of same record (Updated multiple times and then Delete). This will allow users to track all the database changes for a specific record.
- Auto Fetch SQL Server Name: While scanning the transaction logs of a specific database with Online DB option,The SQL transaction log reader tool will allow you to click on drop-down button to auto fetch the Server Name. In case, the software doesn’t shows the desired Server Name, then you can enter it manually.
Existing Features of SQL Log Viewer
- Preview All Transaction Records: SQL Server log file viewer scans and load all available tables transactional activities, present in SQL database and creates a preview. It will help to view SQL transaction log file with fields such as Transaction, transaction time, Table name and query. Users can view all records with Login Name Authentication for Insert, Delete, Update etc.
- Option to Sort LDF File Elements: The sorting option is available within SQL Log viewer to re-arrange the order of the listed items. Moreover, users can sort each element according to their properties such as transaction, transaction name, transaction time, table name, and query.
- Scan with Online or Offline Options: The SQL log file viewer offers two different scanning options to read transactional activities of your database. The Online option asks for SQL Server information like: Server Name, Authentication Type. In Offline option, the software will allow you to browse .ldf file along with its associated .mdf file and preview the transaction log activities.
- Export Selective Tables: While moving the SQL LDF file queries, you can check or uncheck all tables for saving only selected data. If you want to restore SQL Log file data from the desired table, then, select and export the desired tables direct to SQL Server Database or SQL Server compatible script or CSV format.
Technical Description of SQL Server LDF Reader Tool
|Processor||Intel® Pentium® 1 GHz processor (x86, x64) or equivalent|
|Support RAM||Around 2GB (gigabyte) of RAM|
|Hard Disk Storage Space||Minimum 100 MB space for installation|
|Operating System||Windows 10 & all below editions|
After considering the overall functionality of the software, we can summarize that SQL Transaction Log Reader is a reliable tool that allows a user to read and view multiple .ldf files. The software is capable enough to view SQL transaction log with fields such as Transaction, transaction time, Table name and query. Regardless, we can say that the SQL log Viewer is a robust software due to its great features.