ApexSQL Log is an amazing tool for reading the transaction log and reading the transaction log can form the basis of a lot of great features, functionality. In this case, we’ll look at how you can use ApexSQL Log for transactional replication to keep a reporting database up to date with your production system.
August 7, 2018Why is database continuous integration and delivery so hard and how to make it easy; SQL Server database DevOps pain points and solutions
Database DevOps pain points
Traditionally the database has always been the bottleneck for full adoption of DevOps. For a variety of reasons continuous integration and delivery of databases is just harder than client applications. The reasons for this include the following
August 7, 2018What is SQL Server database continuous integration (CI)? – a high level conceptual overview and walk thru of the steps in a SQL database CI pipeline
If you’ve never automated builds in SQL Server before and but are still CI-curious, then this quick high-level overview, should introduce to you the basics and maybe even lead to you wading into the water a little deeper
First, continuous integration means that you will rapidly iterate changes, so that each time a change is formally made, usually by a source control commit, then the target system, a database in our case is automatically destroyed and recreated with the change. Furthermore, the entire test environment is recreated with a combination of static and test data, any automated tests and/or reviews are conducted, and the build is fully documented.
July 26, 2018SQL execution plan analysis tool must have’s
SQL execution plans AKA SQL explain plans are one of the greatest assets when it comes to tuning, optimizing, and troubleshooting our queries. Transact-SQL is a declarative language that can be used to query data without requiring a human interaction to specify an exact SQL execution plan to be followed. This simply means that we are not telling SQL Server how to go and fetch the data, we’re just telling it what data we want it to get. So, think of an execution plan in SQL Server as a map. It is a map that the query optimizer is drawing that represents an efficient SQL execution plan to retrieve the data.
July 13, 2018Top things you need in a SQL formatter tool
How to troubleshoot SQL Server performance problems using the SSMS Performance Dashboard
When it comes to SQL Server performance, you might not think of it as a big deal because of today’s modern hardware but every once in a while, your office phone will start ringing off the hook, your email inbox will start blowing up… SQL Server has been brought to its knees. What happened?
That’s what we are going to look at in this article. The main goal is to identify what some of the common problems are and take a close look at the SSMS Performance Dashboard tool. This tool is available out of the box for DBAs allowing to diagnose and resolve their SQL Server performance issues quickly and easily for all supported versions of SQL Server from SQL Server 2008 to SQL Server 2017.
May 17, 2018How to master SQL Server performance baselining to avoid false positives and/or missing alerts
Baselining SQL Server metrics have significant advantages over traditional predefined alert thresholds when monitoring SQL Server performance. What’s more, when tracking some wait types statistics, it is a must-have as there is no other way to interpret collected performance data correctly otherwise. However, to be able to learn how to use performance baselining and to understand its full potential some advanced knowledge will be presented first
May 11, 2018How to implement compliance with GDPR for SQL Server
Starting May 25, 2018 all worldwide organizations and companies that process, store or in any other way handle personal information of EU citizens have to comply with the General Data Protection Regulation (GDPR) or face heavy annual fines and penalties. In order to comply with GDPR standard, organizations will have to protect and empower all EU citizens data privacy and reshape their approach to ensure that the security of the personal information is met as per the requirements and have full transparency on any data access, processing or more.
April 20, 2018How to create and optimize SQL Server indexes for better performance
Indexes are paramount to achieving good database and application performance. Poorly designed indexes and a lack of the same are primary sources of poor SQL Server performance. This article describes an approach for SQL server index optimization to increase query performance.
March 19, 2018Visual tools for monitoring SQL Server query performance
There are several ways (techniques) of monitoring query performance and finding slow queries such as System Dynamic Management Views, SQL Server Profiler, SQL Server Extended Events.
Recent to the scene, are couple of features (tools) have appeared that make monitoring of query performance make easier than it used to be.
March 5, 2018How to optimize SQL Server query performance – Statistics, Joins and Index Tuning
Have you ever gotten a new computer, hooked it up and said: “this computer is blazing fast, I love it”? I have. A year from then, I was like “this computer is so slow, I need a new one”.
Performance is a big deal and this was the opening line in an article that was written on How to optimize SQL Server query performance. The initial article shows not only how to design queries with the performance in mind, but also shows how to find slow performance queries and how to fix the bottlenecks of those queries. I’d highly recommend reading the above article first because this one would give a lot more meaning but also because it’s an appendix to this topic.
February 28, 2018How to optimize SQL Server query performance
Performance is a big deal. No matter if we’re talking about applications in which users click buttons to display data or if we’re writing a query directly into let’s say SQL Server Management Studio (SSMS). Nobody likes to click a button, go get a coffee and hope the results are ready. As computers get faster and technology moves forward users get more impatient and want things right now; without having to wait.
January 23, 2018Documenting individual build changes (vs an entire database) in a SQL Server continuous integration pipeline
Continuous integration is great, and the more iterations, the greater it is. But with all of those changes being integrated it can be easy to lose track of what has actually changed, and when, not if, something fails reconstructing a documentation trail can be a challenge.
If a label is being created with each build, then the label “image” can be restored, depending on the source control system, or even better, you can use ApexSQL Diff to compare the latest label with the previous and see in detail exactly what changed.
But with more frequent iterations (and CI processes are considered optimal when they are triggered as frequently as possible, even on each commit), creating a label for each individual commit, or even a small group of changes, isn’t practical. As changes pile up, it can be even more difficult sorting through the change logs for auditing or diagnostics purposes.
January 18, 2018How to create and use DML triggers in SQL Server using real world examples
First thing first, a little background on triggers. Although often maligned, in certain circumstances they can be very helpful… once you get to know them. For example, sending an email from SQL Server could be done with a trigger based on some anticipated event.
In this article, we will focus on a case with T-SQL data modification when an Insert, Update or Delete statement hits the data in our table. As the name applies the trigger will fire and then we can make decisions on what next based on new data that is coming in or old data that is getting replaced. This is what triggers are most used for.
January 12, 2018SQL Server true detective – Solving the case of the broken database
In our case, someone has inadvertently dropped a foreign key from the specific table using the following SQL script which has left our database “broken” and it causing problems with client operations:
January 8, 2018How to monitor and detect SQL Server round-trip performance issues using custom designed metrics
For every query issued by the application, time is needed to reach the SQL Server and then the time needed for results to get back to the application. As all communication between an application and SQL Server goes via some network (LAN or WAN), network performance could be a critical factor affecting overall performance. Two factors affect network performance: latency and throughput.
December 8, 2017How to update a SQL database from source control while avoiding dependency errors
Working with a database under source control has many benefits. Beside tracking all the changes made against a database, including the information what the changes were and who made them, you can also track history of committed versions of all database objects which can be restored on a database at any point.
However, one of the benefits which will be explained in this article is comparing and syncing two copies of the same database. Using ApexSQL Source Control, all objects that are missing in one copy of a database can be created/restored in the second copy of a database, preserving the integrity of both databases.
December 7, 2017How to add self data change auditing capabilities to commercial, shrink-wrapped software with a SQL Server backend
There are thousands of distributed. commercial software applications aka shrink-wrapped, from small shareware apps to large corporations, that use a SQL Server backend. These systems, although distributed, could often benefit from and sometimes require data change auditing at the client location.
Although ApexSQL sells a successful, enterprise auditing tool for SQL Server, ApexSQL Audit, this tool is often not appropriate for distributed, commercial software.
November 29, 2017How to analyze query performance characteristics in SQL Server
Introduction
There is a maxim that comes from the world of Python programming: “There should be one – and preferably only one – obvious way to do it.” (See The Zen of Python) in the references. While that is a good goal for any language, it is a difficult goal to achieve. T-SQL is no exception here!
Consider a simple problem: You have a customer transaction table with dated rows. You are asked to produce a list of the top transactions per day. “Top” could be defined as the one with the most items, the highest value, the most important customer or a variety of other criteria. However, you define it, at some point, you will want the maximum (or minimum) value of one of the columns. Then you will want to output the entire row (or rows, in the case of a tie) matching that value. Sounds simple, right? Well, it actually is simple, but the number of ways to do it may surprise you!
November 22, 2017How to execute an unattended best practices review against a database
One of the many tasks of the DBA in charge on a development project is to make sure the team stays consistent in their code. Routine checks for SQL best practices such as naming conventions, right data types usage, problematic things like cursors, potentially fatal things like Deletes without Where clauses can cause a lot of headaches.
November 10, 2017
