Review: Idera SQL Doctor

SQL Server, T-SQL, Tools
Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over. SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong. All that sounds very clinical, just laid out like that. But the fact…
Read More

SQL Spackle

SQL Server, T-SQL, Tools
I previously mentioned how SQL Server Central was listing ideas for articles, primarily for short, quick, pointed articles that they were terming SQL Spackle. Spackle is a term in the US that represents the filler you put into dry-wall to smooth it out or fix small holes, so SQL Spackle is meant to fix small gaps in knowledge or documentation. My first SQL Spackle article was published today. I kind of forgot it was coming. I wrote a quick and pointed outline of how to set up and schedule a server-side trace. This is something I've always advocated, and now I can point to a bit of spackle to fill in the gaps for those who don't know how to get it done.
Read More

Red Gate SQL Source Control

SQL Server, T-SQL, Tools
You just have to love Red Gate tools. They find the small area that they want to cover and then they cover it extremely well. I rave regularly about SQL Prompt and SQL Compare and SQL Search (free one, btw). I've got SQL Data Compare and SQL Data Generator open & working on my desk regularly. I'm dabbling in their other tools fairly often as well. I just like Red Gate tools. I guess my constant & consistent praise is why I'm a "Friend of Red Gate." I like to mention that before I start praising their tools some more, just so no one thinks I'm hiding it. Why would I hide it? I'm proud to say it. I am a Friend of Red Gate! ... anyway... where was I... right, new software.…
Read More

Buy vs. Build

PowerShell, Tools
It's the classic question faced by everyone in Information Services. I know how to do this and I could build software to do it, but I'm a lazy b_____d so I'd rather just pick up a piece of software that does it for me. I love working for large companies because I can usually get them to purchase stuff so that I can loll around stuffing my face all day instead of doing actual work. Unfortunately, not everyone can afford to pick up Microsoft's Operations Manager or Idera's Diagnostic Manager. But you still need to monitor your servers. With buy eliminated, that leaves build. Which, is where this excellent blog post by Laerte Junior comes in. He lays out how to build a wrapper around calls to get Performan Counter…
Read More

Confio Ignite: Part II

Tools
I'm continuing to evaluate Confio's Ignite database monitoring tool. I've had it collecting data on a couple of production servers for about a week now. Based on what I've seen so far, it's looking like a pretty good piece of software. Breaking with the usual tradition, I'm going to talk about the things I'm not crazy about with the software, before I start singing its praises. The first thing, that I thought was missing, but is actually just hard to find, is the ability to look at the query information that Ignite collects, broken down by database. It looks like you should be able to get to it by looking at the Databases tab, but instead you have to first drill down into a time-period, then select specific databases within that time period,…
Read More

Database Design Process

Misc, Tools
Buck Woody recently asked a question; how do you design a database. He outlined the process he followed and asked four questions about how each of us do our work: What process do you follow? How important are the business requirements? What tool do you use to create the design, do you need it to diagram, do you even care about diagrams? What's your biggest pain-point about designing? Funny enough, I haven't done a full on database design in over a year. My company just finished about 6 years of very hard-core engineering work, designing and building or redesigning and building, the majority of our data input and collection systems. Then, I was doing lots of design work. Now, we're either largely in maintenance mode for most of those systems, or the…
Read More

A Lack of Excitement

PASS, PowerShell, SQL Server, T-SQL, Tools
I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven't been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is. The fact of the matter is that I've been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I've blogged before about our use of Microsoft's Operations Manager for monitoring our servers and how we've built custom rules and monitors to keep an eye on things. I've also mentioned how…
Read More

How do -You- use SQL Server

nHibernate, SQL Server, T-SQL, Tools, Visual Studio
I've been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That's a tough one. It would make a much shorter, and easier, blog post to describe the things we don't use it for. However, keeping with the spirit of these tags, I'll try to lay out it. For those that don't know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we'll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70's and 80's propped up by ancient men with pocket protectors, spit, bailing wire…
Read More

Snags With Profiler GUI

SQL Server, T-SQL, Tools
Running the Profiler GUI against a production server is not something you should do. I've outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as part of work I'm doing on a Microsoft PSS call (more on that in another post). We have a procedure on one of our systems that is erroring out, but only on a particular server and only when called from the application, not when it's called, on the same server, from SQL Server Management Studio. I needed to capture some trace events and do it quickly, so I decided to use the GUI, just this once. I put filters on it so that I would only collect certain data, and the database I was collecting…
Read More

Characters

SQL Server, T-SQL, Tools
No, I'm not talking about a Dickens novel. I'm talking about the number of characters in a string. I had a painful time recently because of the word "characters."  If you take a look at the dynamic management view sys.dm_exec_sql_text you can get the queries that have been run on your system that are still in the cache. It's a great utility. Better still, you can get specific statements from the code that are actively running through sys.dm_exec_requests or ones that have run through sys.dm_exec_query_stats. To do this is very simple. Each of these DMV's has a pair of columns, statement_start_offset and statement_end_offset. These columns, and I'm quoting directly from books online measure the "number of character" offset from the beginning of the SQL string and from the end of…
Read More