Upcoming Events

Weekly Database DevOps Live Chats – a new experiment on YouTube

How to Make Your 2020 Monitoring Strategy a Success – Wed, Nov 20th – 8 AM Pacific / 11 AM Eastern – Register

Essential Practices for High Performing Database DevOps Teams – Tue, Nov 26th – 8 AM Pacific / 11 AM Eastern – Register

Why the Database is at the Heart of DevOps Success – Fri, Nov 29 – 6:00 AM Pacific / 9:00 AM Eastern / 3PM CET – ScaleUp 360 online conference – Register

Managing and Automating Test Datasets for DevOps – Weds, Dec 4 – 7:30 AM Pacific / 10:30 AM Eastern – Register

Recent Recordings

Redgate Evangelist YouTube Channel: Tutorials on Database DevOps -New videos each week – Watch

Fast and Reliable Development with Redgate Solutions for SQL Server – Watch

Implementing Data Masking for NIST Compliance – 1 hour – Watch

How Developers and DBAs Collaborate in a DevOps World – 40 minutes – Watch

How DevOps Keeps DBAs Safe from Being Automated Out of a Job – 1 hour – Watch

DevOps: What, who, why and how? – 1 hour – Watch

Can This Team Succeed at DevOps? Panel discussion – 1 hour – Watch

Automating SQL Local Security Policy Rights: PoSH and NTRights

The Basics on Local Security Policy Rights and SQL Server… There are a couple of local security policy rights that are not granted by default in SQL Server setup that I’ve been setting manually for a few years now: Lock Pages In Memory Allows large page allocation Prevents the SQL Server process from being paged out Perform Volume Maintenance Tasks Instant initialization on data files It’s a fairly click-heavy process to add the permissions for these through the Local Security Policy GUI. I prefer that these permissions be granted to the local security group for sql server that’s created in Windows, and that really requires a lot of clicks, unless you can remember and enter a group name like this without any typos: SQLServerMSSQLUser$servername$MSSQLSERVER  or SQLServerMSSQLUser$servername$INSTANCENAME By the way, what is up with using the $ in the group names?  If someone can tell me, I would love to know.…
Read More

Are you Slipstreaming? The Very Best Way to Install SQL Server!

Filed under: Awesome things I learned at SQLPASS! Somehow, I didn’t know about slipstreaming installations of SQL Server until last week. I heard about them at SQLPASS in Allan Hirt’s session on installing SQL Server 2008 on Windows 2008 clusters. What’s Slipstreaming? Slipstreaming is creating a single installation directory and process for installing SQL Server along with any Service Packs (SP) and/or Cumulative Update (CU). You can use unattended installation files with a slipstream installation, just like normal. So in other words, you get a single, smooth, optimized install, completely configurable to run from the command line! I love imaging, but I like this even better because it’s easy for me to keep different configuration files for standalone vs clustered installs, and the installation works on different hardware profiles. Every time I want to move to a new SP and/or CU, I can quickly and easily create a slipstream drop…
Read More

SQLPASS Day 2- Optimization Timeouts and All about TLogs

No Comments
SQLPass unfortunately can’t last forever, but happily it’s still going strong. Here’s some highlights from my Day #2. Paul Randal Knows Exactly What’s Going on in Your Transaction Log… A definite highlight of day 2 was attending Paul Randal‘s session on Logging and Recovery in SQL Server. I’ve read Pauls’ blog posts on this topic and attended his classes before, but even being familiar with the material I find I always learn something from his talks. You just can’t beat being strong on the basics! I took a lot of notes in the session, this is my favorite excerpt from my notes: SQL Server must reserve space in the TLOG so that it can roll back the active transactions, if needed. Once a VLF no longer contains log records that are required, it can be cleared This is done by a log backup in full or bulk_logged recovery models, or…
Read More

SQL PASS Day 1: To Free or Not To Free the Proc Cache?

No Comments
Yesterday was day 1 of SQL PASS 2009. I am attending a variety of sessions on execution plans this year, and along the way I heard three very different opinions yesterday on managing the procedure cache in presentations. Rule of Thumb: The “it depends” answer is usually right. Opinion 1: Never Ever Clear the Proc Cache on a Production Server This first opinion came in a good, solid presentation on using execution plans for troubleshooting. There were some good examples of when you want sql to look at the statistics and trigger generating a new plan, and when you don’t. (AKA when parameter sniffing is a good or a bad thing.) But the speaker was wholeheartedly against clearing the proc cache in production. While I can definitely see this being true for some systems, I have definitely seen advantages of clearing the proc cache on others (more to come below),…
Read More

The Case of the Undroppable Database

No Comments
Once Upon A Time there was an Orphan Database I needed to drop a formerly-logshipped database on our warm standby server. When attempting to drop it, I found that it failed because it was a logshipped database from a replication publisher. Hmm. The database was still in standby/read only mode, so I couldn’t run any system sprocs in the database itselt to clean up replication. Uh oh. My colleague Gina suggested I just run a restore command with recovery to bring it online so I could run those sproc and then strip out replication. Sounds great! But that didn’t work, because the file it needed to bring it online had been deleted from the file system at some point. Someone must have figured it was OK since this database hadn’t been actively logshipped in a while. Oh boy. At this point, Think Very Carefully About What You Don’t Want To…
Read More

Who’s Using All that Space in tempdb, and What’s their Plan?

This post contains a script that I adapted from the fantastic SQL Server Storage Engine Blog.

It comes in handy in my job all the time! Sometimes tempdb is filling up, but sometimes I just want to monitor the amount of tempdb and check out execution plans of heavy tempdb users while watching performance on a server. It just really comes in handy more frequently than I would have thought before I started using it.

Read More