Category Archives: MidnightDBA News

MidnightDBA News

Precon: The Enterprise Scripting Workshop #SQLSaturdayBR

midnightsqlYou are cordially invited to a fantastic precon event before SQL Saturday Baton Rouge! The Enterprise Scripting Workshop is on Friday, July 31, and if you register before June 30 you’ll get the Early Bird rate of $99…not bad at all for a full day’s training.

Oh, and don’t forget to register for SQL Saturday #423 Baton Rouge, too!

Abstract

The DBA life is frustrating: you rarely have time to innovate, because the same tasks fill up your time day after day. Your users are unhappy about how long it takes to resolve “simple” tickets. You have to put big items on hold because special requests come in. As careful you are, mistakes creep in as you get busier.

We’ve been there.

In this workshop, the MidnightDBAs show you how to develop enterprise scripts with a huge range of uses. A good set of reusable scripts will reduce task time from hours – or even days – to just a few minutes, and eliminate mistakes from your environment.

You’ll learn:

  • Enterprise philosophy – tackle simple tasks with the whole environment in mind
  • Single data store – define the benefits and uses of a single central database for common-use data and metadata
  • Choice of tools – choose the best tool (Powershell, T-SQL, SSIS, or other) for the job
  • Environment ground work – prepare your environment for enterprise scripting
  • Real world scripts – work through dozens of enterprise scripting issues (like alerting, error handling, multiple SQL versions, and more) as we develop a real enterprise script in class

This session is for database administrators, with a basic understanding of Powershell. It’s for anyone who touches backups or security, maintains databases, troubleshoots performance, monitors disk space, or any of a hundred other DBA tasks. Enterprise scripting is for anyone who has more tasks than time.

http://www.MidnightDBA.com/Jen

Check out Minion Enterprise, our new enterprise management solution for centralized SQL Server management and alerting! 

Admin, MidnightDBA News, Minion by MidnightDBA, Time-savers

Allow_Page_Locks for Reorgs

minion reindex-01There are many settings that get set one way or another in DBs and in tables. Allow_Page_Locks is one of them that you may not be able to do anything about because your vendor may require it and your situation may require it as well. Normally it’s set to true, but it does get set to false and when it does, it typically needs to stay that way.
The problem is that when you reorganize these indexes that have allow_page_locks = false, then the reorg will fail. But the problem is that if you change it, you may see increased blocking issues. So what is there to do?

Well, the answer is Minion Reindex. We allow you to define pre and post code at the table-level that you can use to switch this option on and then off again when the table is through. And better yet, we even give you the code to discover all of these issues in your database and fix them. In the Minion Reindex download folder you’ll find a Queries folder. This folder has a sql file that you can run and it will insert the table-level exceptions with the proper pre/post code. The precode sets allow_page_locks = true and the postcode sets allow_page_locks = false. This way you can still have the setting the way you need it, and perform your index maintenance too.

And of course, Minion Reindex is completely free so download it now and you won’t be sorry.

Admin, MidnightDBA News, Minion by MidnightDBA, Time-savers

129 of 635

This stat is one of the coolest things you’ll see in Minion Reindex.
FragStats

Gathering fragmentation stats in large databases can take a long time and you have no insight into what’s going on.

With Live Insight, Minion Reindex allows you to see what’s going on every step of the way, including how many indexes you have left to gather stats on.

And we even give you the name of the table and index that’s currently having its stats gathered.  (Our reindexing operations aren’t black boxes. I use this almost every day and I honestly don’t know what we ever did without it.)

NOTE: This feature is turned on by default. Just in case you’ve accidentally turned it off, just make sure that for the current database you have LogProgress = 1 in the Minion.IndexSettingsDB table.

Live Insight…by specific table

It gets even better. You may not be interested in Live Insight for every table.

In this case you can turn off Live Insight for certain tables, or turn it off for an entire database and then turn it on only for certain tables.

This is just one of the many useful features we have in our log.

And don’t forget: you can configure at the database level specifically or use the default DB settings by configuring the MinionDefault database in the Minion.IndexSettingsDB table.

I hope this helps you configure and use Minion Reindex better.

Admin, MidnightDBA News, Minion by MidnightDBA, Time-savers

Order by Index Usage in Minion Reindex

Last week, a user asked if he could order index maintenance operations at the table level, based off of index usage. And of course, the answer to that is definitely yes. We designed Minion Reindex to have flexibility to address custom preferences like this without a lot of hassle, and with no extra jobs.

Minion Reindex already allows table ordering: you can give individual tables a heavier ReindexGroupOrder “weight” (higher numbers are reindexed first). All you have to do is change the order based off of your criteria. We’ve written a script –  MinionReindex-OrderTablesByUsage – that updates the ordering for all tables in a database, based off of usage. You’re welcome to customize this script and change the criteria to whatever you like.

A couple notes on the script:

  • Read the introductory comments for instructions and notes. And of course, review the script well.
  • This might have been easier with a MERGE statement, but we wanted it to be compatible with all versions that Minion supports. The script does an insert or an update based on whether that table is already listed in the “Minion.IndexSettingsTable” table.
  • This script can clearly be modified to meet any number of needs, like ordering tables by row count, or even excluding tables that don’t have enough reads.
  • To use this script, you can either add a job step, or encapsulate it in a stored procedure and add it as a DBPreCode in the Minion.SettingsDB table. It’s that easy.

Feel free to use this script or alter it as you need. And if you make it do something really cool, send it back to us and we’ll add it to the community of scripts on the site. We’ve done some initial testing of this script, but nowhere near the level of testing we put the product through so if you find any bugs let us know. MinionReindex-OrderTablesByUsage

Download Minion Reindex at MidnightSQL.com/Minion

MidnightDBA News, Minion by MidnightDBA

Minion Reindex arrives this Friday!

The MidnightDBA team is announcing the release of a new, free index maintenance solution for SQL Server: Minion Reindex by MidnightDBA.  We designed Minion Reindex to solve all of the reindexing headaches we’ve experienced at dozens and dozens of client sites.

The Bottom Line

Your databases need index maintenance for optimal performance, and most shops don’t have a decent solution in place.  Minion Reindex is extremely simple to implement, and deeply configurable. Junior DBAs will love the ease of use, and expert data professionals will love the extensive settings and features available.

Read more on the Minion Reindex page:

  • Check out the full features list.
  • Get the download link there starting on Friday, October 24, 2014.
  • Join us for the Minion Reindex webinar Monday, October 27.
  • See documentation and tutorials as they come in!

You can ask questions and follow along with the discussion on Twitter using the #MinionReindex hashtag.

Stay tuned for the best thing index maintenance has ever seen…

 

MidnightDBA News

Well Wasn’t that FUN!!!

2 Comments

Imagine my surprise this evening when Jen got a txt from Denny Cherry asking if we were ok after my latest blog post here. I said, what blog post?
Apparently my account has been hacked and someone thought they were being funny. However, looking at the wording in that post, it sounds like a bot to me.

So anyway, I’m fine, nothing has happened, except I about broke a rib running over here to change my password.

Admin, MidnightDBA News, Time-savers, Videos

Move DB Files with Regex

Hey guys, I just posted a new vid on how to use Regex to format DB files so you can easily move them.
The situation is this… you’ve got lots of DBs you want to move to a new drive or to a new server, and you need to write the ALTER DATABASE commands to move all the files, then you need to script the move statements at the cmdline like Powershell. The problem is to be effective, you need some code to parse the filename from the rest of the path.
Here I’m going to show you how to use Regex to parse that out so you can easily build the statements you need. I can write the script to move hundreds of DB files in under a minute. Come watch.
http://midnightdba.itbookworm.com/Video/Watch?VideoId=407

And here’s the demo code so you can follow along.
MoveDBFiles

MidnightDBA News

SQL Saturday Oklahoma City precon: Becoming an Enterprise DBA

1 Comment
Spork instructional module not included. Probably.

We’ll be at OKC this summer for their SQL Saturday on August 23, and we’re presenting our precon “Becoming an Enterprise DBA” August 22!. Get your early bird ticket by July 15, and save over 15%!

Precon: Becoming an Enterprise DBA

August 22, 2014, Norman Technology Center, Moore OK

There’s a real and quantifiable difference between a regular DBA and the leader of an enterprise. Guiding the database effort of a huge enterprise takes business acumen, experience, problem solving, street-smarts, and above all… vision. And quite often regular DBAs are thrown into big enterprises without knowing what it takes to get the job done. In fact, most DBAs get thrown into this situation without even a hint that there’s a difference. And while they do the best they can, they tend to do more harm than good which is why many companies have such a hard time managing their data. What you need is to change your tactic when dealing with large numbers of servers.

The biggest piece of missing training in databases is enterprise training. DBAs are often taught syntax and usage of features, but they’re never taught how to actually use them to plan an enterprise, run an enterprise, or cope with changing requirements, manage difficult users or groups, manage growth, manage vendors, etc. There are so many more requirements to running an enterprise than just knowing how the individual features work. There are disk and backup requirements, auditing requests, end-user reporting, paranoid VPs, and much more. And there’s no way to gain this type of business sense on your own. You have to learn from seasoned pros who have been there many times over.

I’ve built many successful DBA teams in a few companies that were resistant to the change. And I can show you how to do it too. Take your daily tasks down to almost nothing while you increase your visibility in your company (in a positive way).

What you will learn:

  1. How to properly manage a large environment.
  2. Managing disk space and growth requirements across hundreds of servers.
  3. Manage end user expectations.
  4. Build a self-service reporting model to keep DBAs from having to field every day requests.
  5. Reduce security management issues by up to 90%.
  6. How to assess your biggest DBA resource hogs to make the most impact in your environment.
  7. Strategies for dealing with vendors and vendor security issues.
  8. How to hire a competent DBA.
  9. How to staff your company appropriately. Do you know how many DBAs you need?
  10. How to get a bigger raise and make your company pay for training.

And much much more. If you manage an enterprise or would like to then you can’t afford to miss this seminar. No other training exists to take you from being a regular DBA to running and leading an enterprise database effort. And don’t fool yourself; There is a real difference. Get ready to take your skills and your career to the next level.

 

MidnightDBA News

InfoWorld Review: SQL Server 2014, by Sean McCown

InfoWorld Magazine (http://www.InfoWorld.com) has published a new article by MidnightSQL owner Sean McCown, “Review: SQL Server 2014 pushes the pedal to the metal”. The article announces the upcoming release of Microsoft SQL Server 2014 – due out on April 1, 2014 – and reviews some of the top new features.

InfoWorld Magazine is “the leading source of information on emerging enterprise technologies, and the only brand that explains to senior technology decision makers how these technologies work, and how they can use them to drive their business.” Sean’s articles are targeted to this group, detailing what executives need to know about deeply technical topics and products.

Sean has been a contributing editor at InfoWorld Magazine since 2003. His writing is focuses on the database sphere, including database systems, product reviews, and head-to-head comparisons.

 

[InfoWorld] Review: SQL Server 2014 pushes the pedal to the metal
http://www.infoworld.com/d/data-management/review-sql-server-2014-pushes-the-pedal-the-metal-238739

Author bio:
http://www.infoworld.com/author-bios/sean-mccown