Sometimes, I see questions and go, “You know, I’d rather let other people answer this.” And today, I’m gonna let you do it.
My blog post this week about PollGab.com and about its data model brought out all kinds of interesting Office Hours questions. I’ve selected 7 of them that I don’t wanna answer – instead, I wanna hear your answers.
- Gary Kendall: Hey Brent, Do you have any recommendations for version control or auditing to help manage code changes in SQL Server (stored procedures, functions, etc.)?. Developers say “we didn’t change anything” – we know SOMEONE did, but don’t always know who. Thanks! (p.s. nice beach!)
- Azure DBA: Hi Brent, what do you thing of the maximum synapse dedicated SQL pool backup retention of 7 days (42 points). Could this be considered a risk and is there any other way you can backup SQL pool to have a long term backups?
- I’m_a_null_set: Hey Brent. Love your content and classes. I bought your fundamentals bundle a while back which is fantastic. Unfortunately, my employers training budget isn’t enough to cover your mastering bundle. Can you recommend any resources which could help up skill me whilst I save up?
- Arnold: Hi Brent, what are your favorite options to use with sp_whoisactive?
- Roy Hinkley: Hi Brent, is there anything correction action that can be take when estimated rows vs actual rows is off by more than 10x for SQL fulltext search using “CONTAINSTABLE” function?
- DBA Taylor: Hey Brent, big fan of your content! I’m wondering if you know of any “gotchas” with distributed availability groups that are not present with normal availability groups? e.g.The verbiage Microsoft uses in their docs makes it sound more difficult to failover than your typical AG.
- Toucan Sam: Hi Brent, what is your opinion of the new query plan viewer in latest Azure Data Studio?
Got an opinion about any of ’em? Post it in the comments.
43 Comments. Leave new
“Arnold: Hi Brent, what are your favorite options to use with sp_whoisactive?”
My most frequently used:
@Get_Plans = 1
Regarding source control/auditing db changes, I assume you’re talking about DDL changes. Take a look at either DDL triggers or Event Notifications. You can capture details about what and when the change was and who executed it. From there, you can log it to a table, send an e-mail, etc. Of course if someone is really trying to circumvent process, step 1 is going to be to disable the tripwire. At that point, you have a bigger problem.
Regarding Source Control, we use Visual Studio and SQL Database Project type. Incorporates well with TFS, Git, and other source code repository 3rd parties. Early versions (10+ years ago) were a little buggy, but it’s a lot better now. Allows for Schema Compare (so you can reverse-engineer an existing database), deployment profiles (so you can publish to DEV, QA or PROD with minimal click-smithing). Has pre- and post- deployment script capabilities. Has great referential integrity of the meta-data (throws a build error if you define a view against a table that doesn’t exist). Can reference other databases in the same solution, and allow for variable substitution of database names upon deploy.
Jut be aware of what happens if you do things like insert a field in the middle of the table def for a 100 million row table. SQL has to create a new table, migrate data, and drop the old, just like it would do in SSMS, so deploying to existing data can be slow.
This is the method we use; a DDL trigger that records when procedures are changed and by who, and also retains the full CREATE/ALTER statement in an XML column of the logging table each time. This has been very helpful when something fails, as it allows us to see what changed and we can ask the person the changed it why they did it. Ideally we’d get at most of this with good code comments, but you know how that goes.
Gary Kendall: Hey Brent, Do you have any recommendations for version control or auditing to help manage code changes in SQL Server (stored procedures, functions, etc.)?. Developers say “we didn’t change anything” – we know SOMEONE did, but don’t always know who. Thanks! (p.s. nice beach!)
Visual Studio Database Projects, GItHub, and Azure Devops (the cicd part) work fantastic. Deploying, rolling back and schema comparison makes that part of SQL development a breeze. Plus you have visibility of changes in Azure, not to mention the ability to require sign off from stake holders. It requires a bit of set up but the opportunity cost pays for itself after the first deployment.
DLM dashboard is great for this. It’s from Redgate but is a free product. I’ve used it to prove that someone in particular was the one who kept adding indexes and changing procs on an ad hoc basis.
Awww I went to check it out because DLM sounded great but RedGate’s website says this:
“DLM Dashboard will be formally retired on the 21st of March 2022. For those seeking a database drift solution, we recommend the SQL Monitor custom metric for Database Drift.”
From https://www.red-gate.com/products/sql-development/dlm-dashboard/
Still using it on SQL Server 2019 even though it’s retired. Can’t find anything that is as simple (and cheap). Even had it working with SQL Managed Instance at one point. I think I’ll keep using it until it breaks.
Our workplace has DBAs and developers enter production only from a monitored VM. It records the users actions and so a manager can in theory go back and review exactly what occurred on each change request. Developers don’t directly implement code. A single individual implements each CR and access in general is limited, meaning we know who’s been in there, and when, and can review what they did. We use Teams Foundation Server for code version control and as a general practice timestamp most tables with a create date for new records (default constraint to GETDATE() or GETUTCDATE() ) and update date for changes to records.
3. Free:
-SQLSkills just released a library of training videos for free! https://www.sqlskills.com/sql-server-training/sqlskills-insider-sessions/sqlskills-insider-sessions-completed/
Costs less than Brent’s Training:
-Erik Darling’s training classes: https://learn.erikdarlingdata.com/
-SQLSErverFast’s training videos (there’s a lot of reference material for free) https://sqlserverfast.com/the-sqlserverfast-execution-plan-video-training/
4. @Get_Plans = 1
But is the training as good and entertaining as Brent’s? Usually, from what I have seen, most training is dry, boring and not nearly as insightful. Thank you Brent for the training that you do, it is always appreciated, both the free and the paid for training. The knowledge I learned from your Mastering classes has bumped me to a new level in my abilities and was well worth the cost.
I should have read the question before posting my reply to your answer happydba.
Right I wasn’t knocking Brent’s classes at all 😀 Just offering cheaper/free alternatives.
For what it’s worth, Erik’s trainings are funny and entertaining like Brent’s is. Erik used to work with Brent as well, and wrote many articles on this site.
Oh another one is the https://www.groupby.org/ sessions – https://www.youtube.com/channel/UCEHf_UKwG3GMkb9wIVBTeQA
For the “version control” / “who changed this” question, the easiest first step is DDL triggers + some sort of “Audit” database. I wrote this up way back in 2010, but it’s still reasonable (and could be tweaked to exclude maintenance processes) – https://schottsql.com/2010/02/08/ddl-schema-change-auditing-on-sql-server-2005-2008/
We created a database that only allowed inserts into the audit table for the public role and wrote up that trigger to hit every user database, including model, so changes would be tracked. I then had a nightly job sending out the new entries in the table to see what was happening. Could easily tweak that to exclude “approved” logins that make the changes. We’d had issues with devs making changes to the shared dev database, but sometimes forgetting to commit them to source control.
For version control, that’s a bigger problem to tackle and requires discipline and a good process. Start by figuring out if you are going to do “migrations” or “state-based” and go from there. I’d also start small to prove out the concept – make branches, merge main branch into branches, ensure they build, push to main, publish/deploy from there. If it’s not in a branch, it doesn’t go (perhaps barring a “really, really hotfix” branch), but even then it’s worth tracking properly.
Consider having AppDev folks develop against LocalDB on their own machines. Start calling the shared Dev database a CI/CD database and yank all AppDev permissions except db_datareader. Only service accounts from the Dev app server should be allowed to execute anything in a CI/CD database (and the release management system, of course). You can handle test data with environment-specific post-deployment scripts that run locally when the AppDev person does a a pull/get latest. It can be a big shift in mindset, but *most* teams I’ve seen take to it pretty quickly.
DBA Taylor: Hey Brent, big fan of your content! I’m wondering if you know of any “gotchas” with distributed availability groups that are not present with normal availability groups? e.g.The verbiage Microsoft uses in their docs makes it sound more difficult to failover than your typical AG.
I am using a DAG to connect sites for DR in Production right now. The main “gotcha” that I have found is that you have to script everything. There is no GUI for Distributed AGs, so your scripting skills need to be rock solid. Another consideration is how you connect to your AG listeners. Since each base AG has it’s own listener name, we have our apps connected with DNS A Name records that alias to the listener name of whichever side of the DAG is currently the Primary, and control that at fail over time with a PowerShell script.
Overall though it is a solid and dependable solution once you get your scripts tested and reliable.
Thank you for the response. In the official docs it mentions stopping all transactions before failing over, which is not something I would consider feasible in our environment. With normal sync AGs, we can freely fail over with no concern of data loss. I’m also curious if you check the log LSN to ensure what has been hardened on the current primary has been hardened on what will become the new primary when failing over. Again, the docs mention this for distributed AGs, but not for normal AGs.
What I do in my failover script is place both side in SYNCHRONOUS COMMIT, then I have a wait loop to ensure that they are fully synced. Then I step down the primary and do the failover.
:CONNECT
ALTER AVAILABILITY GROUP [AG NAME] SET (ROLE = SECONDARY);
GO
:CONNECT
ALTER AVAILABILITY GROUP [AG NAME] FORCE_FAILOVER_ALLOW_DATA_LOSS;
Don’t let the DATA LOSS scare you, as long as you are synced and you put the PRIMARY in SECONDARY, cutting off all connections, first you are fine.
Sorry the markup got me, LOL
:CONNECT [current primary]
ALTER AVAILABILITY GROUP [DAG NAME] SET (ROLE = SECONDARY);
GO
:CONNECT [current secondary]
ALTER AVAILABILITY GROUP [DAG NAME] FORCE_FAILOVER_ALLOW_DATA_LOSS;
Just for fun, check this out… On-Prem to Azure SQL Managed Instance via a Distributed Availability Group framework… More Azure witchcraft, LOL
https://techcommunity.microsoft.com/t5/azure-sql-blog/managed-instance-link-connecting-sql-server-to-azure-reimagined/ba-p/2911614
I’m Erik, and if you look around here you’ll find a lot of my old blog posts.
I do my own consulting and training now, and if you follow this link you can get the entire package for 75% off.
Thanks!
This ^
There’s gold in them thar blogs.
For the Auditing, i HIGHLY recommend Redgate’s DLM. We’ve been using it for yearrs now and it alerts us when any changes are detected along with who, what, and a rollback script. Amazing and free. No DIY triggers needed.
https://www.red-gate.com/products/sql-development/dlm-dashboard/
1: Version Control – I prefer GitHub or SVN but in a pinch I have used a DDL trigger on the instance. Just beware those can cause all DDL to come to a screeching halt if the DDL triggers breaks due to say a missing audit table.
3: Free Training – Brent Ozar videos, SQLSkills videos, SQL MCM Videos, Red-Gate free ebooks, SQLBits old sessions, SQL Pass old sessions, Video Sites (Pluralsight, LinkedIn Learning etc)
4: sp_whoisactive – Most common and well used is @get_plans=1.
I don’t have enough knowledge to give an informed opinion on the rest of the questions.
I knew I was forgetting one of the good free resources!
The MCM videos are here: https://docs.microsoft.com/en-us/previous-versions/dn912438%28v=msdn.10%29
Yeah… they decided to take down Kimberly Tripp’s killer MCM index videos. I won’t go into my thoughts on why that happened but it’s a real shame.
On the training question… I’ve always been totally amazed that our profession is one of the very few in this world where people with almost casual knowledge of what they’re being hired for and enjoy a fairly high salary will insist on the employer paying for the training for a lot of stuff they should already know and not being willing to spend a dime on their own training nor even a bit pf off-work study time to try to improve their knowledge. And then to ask you where they can find free training… the last time I heard, Yabingooducklehoo” was still online and working fine. Like I said, a lot of folks spend no effort in improving on their own, not even a search.
To wit, I sometimes cringe a bit at how you deal with some questions on “Office Hours” (and I’m an ex-submarine sailor that could swear the chrome off a truck bumper and make a bronze statue try to find a place to hide) but almost immediately make the realization that you don’t pre-filter the questions and I’ve always ended up saying “Ya know… he’s absolutely right”. 😀
Shifting gears a bit, I really enjoy Office Hours, your articles, and your free tools. They all go beyond the call of duty, in my humble opinion, and what you do for this community is incredible and much appreciated. I’m especially appreciative of your index articles/videos (they were the main reason for my deep interest in indexes and the development of sp_IndexDNA™) and for the articles on computed columns (and everything else you’ve written about, as well). Absolutely incredible information there that have made a huge impact for me. Thank you, good Sir and it was a real pleasure to have met you in person way back when.
Awww, thanks sir!
I just recently started filtering the questions this week, too. I felt bad about it because I felt like I should just answer whatever is highly voted, but at the same time… it’s my session, and if I can’t bring value to a question, then I shouldn’t waste the time of the viewers.
Here is question asked by Gary:
Gary Kendall: Hey Brent, Do you have any recommendations for version control or auditing to help manage code changes in SQL Server (stored procedures, functions, etc.)?. Developers say “we didn’t change anything” – we know SOMEONE did, but don’t always know who. Thanks! (p.s. nice beach!)
Here is the solution:
I have used a DBA_Admin_DB database and 4 triggers to store all 4 major Table, View, Stored Procedure, and Function change/alter for all databases at instance level.
Here is an example codes to record a SP change.
create TRIGGER [tr_AuditStoredProdcedureChanges]
ON Database
FOR create_procedure, alter_procedure, drop_procedure
AS
BEGIN
DECLARE @EventData XML
SELECT @EventData = EVENTDATA()
INSERT INTO [DBA_Admin_DB].[guest].[StoredProcedureChanges]
(
ServerName,
DatabaseName,
SchemaName,
TableName,
EventType,
LoginName,
SQLCommand,
AuditDateTime
) VALUES (
@EventData.value(‘(/EVENT_INSTANCE/ServerName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(250)’),
@EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(max)’),
GetDate()
)
END
Tjhe guest table of [DBA_Admin_DB].[guest].[StoredProcedureChanges] will keep every change from our users for one record, all change histories will be kept for developers for to track as code version control.
good luck,
Jim
Q1: I use Redgate SQL Source Control. It has a nice SSMS plugin too.
Q4: sp_whoisActive @get_Additional_info = 1
Thanks Brent as always!!!
In no particular order:
1. I cover that in my fundamentals of tempdb. (Sign up now).
2. That’s security stuff, I usually don’t touch that.
3. Ooh, that’s a bit too hard to answer in a q&a format. Click the “Consulting” link above.
4. If you search for “Slow in the Application, Fast in SSMS”, Erland Sommarskog has your answer.
5. Ooh, great question. I cover this in my “mastering server tuning class” (Sign up now).
6. My question to you is “what problem are you trying to solve?”
7. Why are you asking me? You could just try it yourself!
But seriously in order:
1. Source control. Redgate has a product for tracking changes. We use github and only have automated processes do table changes (DDL), we also audit user actions with extended events.
2. Azure? :shrug:
3. Limited training budget? There are some options in these comments. But training does cost money and the ROI is usually significant.
4. sp_whoisactive options? The only two I have memorized are @get_plans and @get_locks
5. Fulltext CONTAINSTABLE? That’s another shrug from me. Sorry.
6. Distributed AGs? That’s a third question that’s outside my area. 🙁
7. Query plan viewer in ADS? I personally love it. I like that the icons are the same size and aligned. It’s the thing that made me re-evaluate ADS as my main development environment. I’ve been using ADS this week for development and query tuning and I think I like it.
There are two types of questions – good ones and great ones. These are non of ’em.
But seriously
1. That’s coveren in Brent’s Margarita Drinking classes
2. That’s covered in Brent’s Porshe Driving classes
3. That’s covered in Brent’s Scuba Diving classes
4. That’s covered in Brent’s Iceland Hiking classes
5. That’s covered in Brent’s Turtle Saving classes
6. That’s covered in Brent’s Playing Dead by Daylight classes
7. That’s covered in Brent’s Vegas Restaurants classes
Visual Studio has “SQL Server Database Project” project type/template. You import your schema into VS. It creates individual .sql files. You treat it like source code. You check it in and check it out. Add comments. Make branches. Shelve things. Git. Source Gear Vault client-plugin VSIX install. Your choice on the source code control. You make your people do it. But you roll it into your DevOps business process in a nice organized way. That’s what I would recommend. “So you can… go back in time.” (Pragmatic Programmer book)
For Gary Kendall specifically and everyone in general. Any SCM (source code management) is better than no SCM. You don’t know if there’s drift if you don’t know what the DB should be. Generate everything as a create script and start your version from there. Don’t forget about key reference data. A list of countries, states, cities, currencies, FIPS data, etc. that’s critical for running your business should also be in source control. How you configure the database should be scripted so that you can recreate – or detect & correct drift should be included. How you configure the SQL instance (trace flags, cost threshold for parallelism… everything – should be a script.
Act as if your handed the requirement to stand up a new server, and your task is to write scripts to install and configure EVERYTHING. That’s what belongs in SCM.
Don’t forget the ability to run the same script hundreds of times and only affect the “system” once and do it right every time it runs (see idempotent).
1. We use Liquibase: https://liquibase.org/
4. I created a job with the step:
‘exec sp_WhoIsActive
@get_plans = 1,
@get_transaction_info = 1,
@destination_table=’Monitoring.dbo.WhoIsActive”
And I run the job every 10 seconds; this allows me to have an historic of what happened. Don’t forget to create a second job to cleanup the table or your drive will be full.
For version control – SSDT has grown on me. Use with Git.
For auditing changes – I wrote a tool for this a while ago:
https://wisedataman.com/ddl-code-history-tool
This will give you who changed what when…but I stopped working on it as event notifications don’t work well with in memory OLTP. I might update it and post on github at some point though.
I now use DBA Dash for tracking schema changes – this takes a snapshot approach. It tells you what changed when – but not who made the change. It doesn’t track all changes – just the differences between the snapshots. It can do this for agent jobs as well as database schema. This tool is open source and is under active development. It’s useful for much more than tracking schema changes.
https://github.com/trimble-oss/dba-dash
I used SSDT for several years, but it had several pain points:
• On my database, it tends to crash.
• No SSMS integration
• Very, very slow when working with off-site version control systems (using git mitigates this, but doesn’t resolve it).
I eventually paid for DevArt dbForge, having first experimented with RedGate, Liquibase, and ApexSQL. However, please note that I don’t recommend Devart’s solution at all: Devart happens to be a good fit for my workflow, but it has too many problems for me to recommend:
• Licensing system is tends to fail. License tracking is poor.
• History/Comparisons don’t work (in my setup).
• Slow (albeit, better than SSDT)
• Missing a bunch of features (shelving, ignoring, merge scripts, branch/merge, etc.)
• Very poor handling of multiple databases in the same source control.
1. VCS: Not to long ago, went for Git + SSDT + Gitlab: pipeline compatible, automated release with code collaboration and review. Haaa… found memories. I’m quite a fan of state-first db migration/code management.
… and for auditing… home made job to report drifts with… SSDT again! in case somebody shortcuted the standard pipeline.
1. Git is a wonderful tool
2. It depends, RPO/RTO? (https://www.brentozar.com/archive/2018/07/does-your-backup-strategy-achieve-rpo-and-rto-goals-of-the-business/)
3. LOL
4. What do you want to archieve? Consider reading the docs first.
5. Already took the fundamentals and master tuning classes?
6. –
7. Looks like the same as in SSMS?