Search results matching tag 'Tools'

SQLintersection!

KKline — Wed, 27 Mar 2013 13:47:00 GMT

The best emotion to describe how I'm feeling is 'astounded'. I'm astounded that I'm in such august company to be speaking the SQLIntersection (#iSQL) conference. Read the blog post from my first SQL Server mentor, Kimberly Tripp, which tells you all about SQLintersection.

Check out this list of speakers:

Aaron Bertrand, Sr. Consultant, SQL Sentry, Inc. [blog | twitter]
Andrew J. Kelly, Mentor, SolidQ [blog | twitter]
Bob Ward, Principal Architect Escalation Engineer, Microsoft [blog | twitter]
Brent Ozar, Brent Ozar Unlimited [blog | twitter]
Conor Cunningham, Principal Architect, SQL Server, Microsoft [blog]
Grant Fritchey, Product Evangelist, Red Gate Software [blog | twitter]
Jeremiah Peschka, Brent Ozar Unlimited [blog | twitter]
Joseph Sack, Principal Consultant, SQLskills.com [blog | twitter]
Kendra Little, Managing Director, Brent Ozar Unlimited [blog | twitter]
Kevin Kline, Director of Engineering Services, SQL Sentry, Inc. [blog | twitter]
Kimberly L. Tripp, President/Founder, SQLskills.com [blog | twitter]
Mat Young, Senior Director of Products, Fusion-io [blog | twitter]
Paul S. Randal, CEO / Owner, SQLskills.com [blog | twitter]
Paul White, SQL Kiwi Limited [blog | twitter]
Steve Jones, Editor, SQLServerCentral.com [blog | twitter]
Sumeet Bansal, Principal Solutions Architect, Fusion-io [blog | twitter]

Read the list of SQL Server sessions here. On top of the list of outstanding sessions to attend, I'll be giving a keynote on Tuesday afternoon. Witness:

So the only thing between the attendees and the booze in the reception hall is our keynote address?!? Oh yeah, that's going to go down real smooth, isn't it? I'll last about as long as a puny henchman between James Bond and the villain of the movie. Sumeet Bansal, from Fusion-IO, will have to survive until the credits roll. We'll be talking about high performance computing on SQL Server 2012 with an eye towards high availability, AlwaysOn, and Availability Groups.

If you're in Las Vegas, I hope to see you there! If not, you should consider coming to this excellent conference.

-Kevin

-Follow me on Twitter!

SQL Server Data Tools - Business Intelligence for Visual Studio 2012 Released!

KKline — Fri, 08 Mar 2013 12:33:00 GMT

SSDTBI for Visual Studio 2012 enables customers to use the Analysis Services, Integration Services, and Reporting Services project templates within the Visual Studio 2012 shell. The components are delivered as a web download on the Microsoft Download center and will be available through Web Platform Installer. This release is the equivalent functionality to SSDTBI (BIDS) for Visual Studio 2010 that shipped in the SQL Server 2012 box. The team adapted the UI to meet the new Visual Studio 2012 UI design. This release delivers on the SQL commitment to provide BI Project Templates supporting the latest version of Visual Studio, a much anticipated capability. The downloads are publicly available on the Microsoft downloads website now:

Download: http://www.microsoft.com/en-us/download/details.aspx?id=36843

More details from specific Microsoft BI teams at:

RS team blog: http://blogs.msdn.com/b/sqlrsteamblog/archive/2013/03/06/sql-server-data-tools-business-intelligence-for-visual-studio-2012-released-online.aspx

AS team blog: http://blogs.msdn.com/b/analysisservices/archive/2013/03/06/sql-server-data-tools-business-intelligence-for-visual-studio-2012-released-online.aspx

Enjoy,

-Kevin

-Follow me on Twitter!

Express Edition revisited, focus on SSMS

TiborKaraszi — Wed, 30 Jan 2013 17:01:00 GMT

(Note: I have re-written parts of this post in the light of the comments that SP1 of 2012 include Complete tools.)

I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I have a couple of reasons for this:

In my 2011 post, I never tried to connect from Express SSMS to a non-Express database engine.
I want to check if there are any significant differences in SQL Server 2012 Express Edition, compared to SQL Server 2008R2 Express Edition.

It isn't uncommon that people want to have SQL Server Management Studio (SSMS) on their machines; and instead of searching for the install files for the full product, they download the freely available Express Edition and install SSMS from there. This was the main reason for this update post, and the reason I focus on SSMS and the tools in this post.

It turns out that both 2008R2 and 2012 RTM Express editions of SSMS includes a lot, but not quite everyting that the full version of SSMS has. And they don't have Profiler or Database Engine Tuning Advisor. 2012 SP1 Express download does indeed have the Complete tool package.

Basic and Complete
The full SSMS (etc.) is referred to as "Management Tools - Complete". This is only available with the Product you pay for and with 2012 SP1 Express. The only one available with the various free Express downloads (prior to 2012 SP1), is called "Management Tools - Basic". You can explicitly request to install Basic from an install media that includes Complete, but you have to explicitly request that in the setup program. You don't want to do that.

One difference between 2008R2 and 2012 is when you install from a pay-media and select that you want to install Express. For 2008R2, you then only have SSMS Basic available. For 2012, you have Complete. In other words, if you use a 2012 pay-media and select Express to install SSMS, you have the option to have the full-blown SSMS - Complete (including other tools, like Profiler).

The downloads
For SQL Server 2008R2, you have "Express Edition" and "Express Edition with Advanced Services". The former is basically only the database engine, where the later has some Tools (SSMS Basic, primarily). See my earlier blog post for more details about 2008R2.

For 2012, there are bunch of downloads available. Note that if you want Complete tools, you need to download SP1 of the installers. You find SP1 here (and RTM, which you don't want to use, here). SP1 includes Complete tools, and you will see that those downloads are significantly larger compared to RTM. It isn't obvious what each exe files stand for, but scroll down and you will find pretty good explanations. I tried several of these (SSMS only, Express with Tools, Express with Advanced Services). They all have in common that for RTM the tool included is Basic, where for SP1 we have Complete.

So what is the difference between Basic and Complete?

In the table below, my focus was on what isn't in Basic. In general, I don't bother to list functionality which is available in both Basic and Complete. So, if the functionality isn't in the table below, it is likely available in Basic. I might have missed something, of course! And my main focus was on SSMS and the database engine.

Component/Functionality	2008R2	2012 RTM	2012 SP1
Functionality in SSMS
Node for Agent	Y	Y	Y
Graphical Execution Plans	Y	Y	Y
Projects and Solutions	N	Y	Y
Maint Plans, Wizard	Y	Y	Y
Maint Plans, New, designer	N (1)	N (2)	Y
Maint Plans, Modify	N (1)	N (2)	Y
Node for SSIS Catalog	N/A	Y	Y
Tools menu, Profiler	N	N	Y
Tools menu, Tuning Advisor	N	N	Y
Connect Object Explorer to:
Analysis Services	N	N	Y
Reporting Services	N	N	Y
Integration Services	N	N	Y
Tools
Profiler	N	N	Y
Database Engine Tuning Advisor	N	N	Y

(1): The selections are there, but they were dead - nothing happened when you select them.
(2): The selections are there, but I got an error message when selecting any of them.

Update to Rules-Driven Maintenance

merrillaldrich — Wed, 16 Jan 2013 18:34:00 GMT

Back in August I posted a first version of a rules-driven solution for backups, index and statistics maintenance and integrity checks. The system in general has been working well, and has saved my team a huge amount of time and effort. We are coming to the anniversary of its use in production soon.

Today I offer an update that contains a few enhancements, performance improvements and a bug fix.

To recap, this is a system or framework to manage many small databases across many instances on many servers in a policy-based/automated way. The details about how to deploy and how to use the system are in the original post, but at a high level:

First deploy the code across a collection of servers using the included PowerShell script DeployMaintenance.ps1. That will create an administrative database on every instance (or use one that you provide) and populate it with the required tables, procedures, etc. The deployment script will also make a handful of SQL Agent jobs that handle all the maintenance on all the databases.
Visit the instances. If there is maintenance already in place, disable or remove it so as not to perform this work twice. If necessary, change or override the default preferences for this solution in the admin database for each type of maintenance. This includes the time of maintenance windows, whether to use features like differential backups or Litespeed, whether there are databases that should be treated as exceptions, with different settings, etc.
Enable the new policy jobs in SQL Agent.
Monitor to make sure things are running smoothly.

The theory behind this project is to save work configuring servers (efficiency), to ensure that the maintenance is in fact deployed and working for ALL databases in the environment (quality), to ensure that it works in a truly consistent way everywhere (consistency), and to make it so that the servers don’t have to be touched by a DBA when changes happen, such as adding or dropping databases (resiliency).

Changes

The updates in this 1.1 version include

Better reporting of errors. The jobs now write log files to the default error log location for SQL Server.
Better recovery from errors. The jobs in some cases continue on error – for example, one failed backup will not stop the backup job and leave other databases without backups.
Performance enhancements for examining backup history and system counters (mainly % log used).
One bug fix for the log backup logic on a server that uses both log shipping and Litespeed.

Disclaimers

The code for the system is posted here, and you are welcome to download it and try it out. Please note that this is not a commercial solution, and that while you may test or deploy this, you do so at your sole risk. Edits to the code almost certainly will be required for your environment. Please read, understand and test the code thoroughly before you even imagine using this in production. I do not want to cause you or your employer any pain. License for using this solution is GPL 3.0: http://opensource.org/licenses/GPL-3.0. You may use and modify this code, but not sell it.

This has been run on SQL Server versions from 2005 to 2008 R2. It probably works on 2012, but I have not tested it thoroughly.

I run only case-insensitive collation; if you are on a case-sensitive system, then you have my sympathy, and you might have to edit a few things to make this work.

There is coverage for Quest Litespeed backups in the solution; other third-party backup products could be incorporated easily, but have not been. It should be possible to follow the pattern used to handle Litespeed and extend it to any other third-party stored procedures.

If you haven’t done index maintenance for a while, and you flip this solution on you might blow up your transaction log. Be sure that you have plenty of log space and plenty of log backup space, and watch the process the first few runs. This is especially true if the combination of Standard Edition of SQL Server and the preference for Online index maintenance applies in your environment, because the maintenance process will perform index maintenance using reorganize (defrag), which produces a lot of log.

Lastly, this is a solution for the type of environment that has many small databases, not the type with a few big, performance intensive databases. Databases that are large and require detailed attention may not be suitable for a one-size-fits-all solution like this.

Backup and the evil RETAINDAYS option

TiborKaraszi — Sun, 08 Jul 2012 12:25:00 GMT

"So what bad has this option done?", you probably as yourself. Well, not much, but I find it evil because it confuses people, especially those new to SQL Server. I have many times seen people specifying something like 3, and expect SQL Server to keep the three most recent backups in the backup file and overwrite everything which is older than that. Well, that is not what the option does.

But before we go into details, let's look at an example backup command which is using this option:

BACKUP DATABASE sqlmaint TO DISK = 'R:\sqlmaint.bak' WITH RETAINDAYS = 3

The RETAINDAYS is also exposed in the backup dialog in SSMS: "Backup set will expire: After x days".

It is also exposed in Maintenance Plans, the backup task. The option is named "Backup set will expire: After x days". It is only enabled if you select the "Back up databases across one or more files" option, which is not the default option. This makes sense.
The default option is "Create a backup files for every database", which means that every time a backup is performed, a new file is created consisting of the database name, date and time. Since we will see that this option is only relevant when we do append, it makes sense in the RETAINDAYS not being enabled for this choice.

So what does this option do? All it does is make SQL Server return an error message of you try to do a backup using the INIT option (which means overwrite) before the date and time has occurred. In other words, it tries to help you in not overwriting a backup file, using the INIT option, before it is time. You can still overwrite earlier by either using the stronger FORMAT option instead of INIT; or by simply deleting the backup file. Btw, the RETAINDAYS parameter has a cousin named EXPIREDATE, which does the same thing but you specify a datetime value instead of number of days.

Backup generations
So, we have seen that RETAINDAYS do not in any way provide any automatic backup generation handling. There is no such functionality built-in in the BACKUP command. This means that when you are looking for functionality such as "keep backup files three days, and remove older than that", you need to look outside the BACKUP command. I.e., some script or tool of some sort.

I have an example (without the "delete old files" part) here, which I mostly created as a starting point for those who want to roll their own and want to have some example to start with. Many of you are probably using Maintenance plans (the "Create a backup files for every database" option in the backup task, along with Maintenance Cleanup task). Another popular script/tool for this is Ola Hallengren's Maintenance Solution, which you find at http://ola.hallengren.com/.

Analyzing the errorlog

TiborKaraszi — Thu, 05 Jul 2012 11:53:00 GMT

How often do you do this? Look over each message (type) in the errorlog file and determine whether this is something you want to act on. Sure, some (but not all) of you have some monitoring solution in place, but are you 100% confident that it really will notify for all messages that you might find interesting? That there isn't even one little message hiding in there that you would find valuable knowing about? Or how about messages that you typically don't are about, but knowing that you have a high frequency can be valuable information?

So, this boils down to actually reading the errorlog file. Some of you probably already have scripts and tool that makes this easier than just reading every simple message from top to bottom. I wanted to share how I do it, and this is why I wrote my Analyze SQL Server logs article. Check it out. And, feedback is always welcome!

SQLMag = SQL Server Pro. Goes all-digital!

KKline — Fri, 27 Apr 2012 13:30:00 GMT

I was recently chatting with Megan Keller, my long-time editor for the Tool Time column and Executive Editor at SQL Server Pro,DevProConnections, and SharePoint Pro.

I've subscribed to SQLMag ever since it was first put to print back in the late 1990's. (That's a pic of me, on the right, in the shirt given my by SQLMag's first publisher way back in the day). I have many bookshelves in my office, but SQLMag consumes more than half of the shelf closest to my desk. It's that good. Some of my personal favorites and perpetual must-read content are the columns SQL Server Questions Answered by Paul Randal & Kimberly Tripp as well as the always excellent columns Puzzled by T-SQL by Itzik Ben-Gan and all of the great content by Kalen Delaney.

Megan recently told me about the exciting changes afoot there. For starts, SQL Server Magazine’s has new name—SQL Server Pro. This name, as you can tell from its sister publications listed at the top of the blog post, puts all of the Penton Media properties on an equal and consistent footing.

Please take some time to check out hands-on, how-to content from SQL Server experts at sqlmag.com. The on-line magazine features the same great expert advice and writers as before. And you can access my articles on sqlmag.com just as you have in the past.

Suggestions for the Tool Time Column?

And while we're at it, let me know if there are any SQL Server related tools you'd like to see in the Tool Time column! My requirements are that the tool must be free, must be supported, and of course must be relevant to SQL Server professionals.

Enjoy,

-Kev

Follow me on Twitter!

New White Paper: SQL Server Extended Events and Notifications

KKline — Wed, 25 Apr 2012 13:13:00 GMT

SQL Server comes with a wide array of tools for monitoring your environment. There are logs and traces that provide information when errors occur, but these are often used passively to react to events that have already occurred. There's PerfMon, and Profiler, and loads of Dynamic Management Views to check. But where to look?

As database administrators (DBA), we need to monitor our environments proactively and create solutions as issues arise. In this white paper, we will look at a couple technologies – event notifications and extended events – that can help you achieve these goals. With these two features, we’ll look at the error log and deadlocks, and demonstrate how you can get relevant information delivered as it occurs. We’ll also look at ways that run-time errors can be captured and used to help reduce the amount of time required to investigate issues.

This white paper, written by SQL Server MVP Jason Strate (blog | twitter), is a free download but requires a registration. Download the Extended Events white paper here.

And, as always, I enjoy your feedback. Thanks!

-Kev

Follow me on Twitter!

Dev Advice: Make a Tiny Dev Database Act Like a HUGE Prod Database

KKline — Fri, 16 Mar 2012 14:30:00 GMT

Here's an evergreen question. It's a question that never completely goes away. But lately, I've been getting it a few times per week. So I thought it's time to readdress the question, which usually takes some form of the following:

I can't really do effective development on my little dev laptop because our production SQL Server database is 15 gazillionbytes, way too big for my workstation. What's a uber-nerd to do?

Well, maybe they didn't use the word "uber-nerd". But you get my drift, right? The production database is really, really big - unmanageably big for keeping a local copy. So that means the dev either has to create a metadata-only version of the database, which won't produce realistic query plans, or somehow crush their laptop under 15 gazillionbytes of MDF and LDF files.

Actually, you have a better alternative - a clone database, sometimes called a shell database. Here's how I described a cloned database a few years ago here in my Tool Time column for SQL Server Pro Magazine:

In effect, a cloned database includes all of the schema objects of the database (e.g., tables, views, stored procedures), as well as the statistics and histograms (the so-called "statistics blob"). This metadata is quite small by volume but can tell you what estimated query plans look like outside of a large production environment and how those estimated query plans might change when SQL Server is upgraded. Cloned databases are especially useful when the data is confidential, classified, or subject to privacy laws.

The article gives you all the detail you need to effectively and quickly create a small version of a big, ol' production database that produces the same query execution plans as you'd get on the prod server.

If you're struggling with doing development on a big SQL Server database, learn the ropes on cloned databases asap! You'll be glad you did.

Enjoy!

-Kev

-Follow me on Twitter

SQL Live Monitor

TiborKaraszi — Sat, 19 Nov 2011 08:46:00 GMT

I just found this one out there and wanted to share it. It connects to an instance and show you a bunch of figures. Nothing you can't extract yourself with SQL queries, but sometimes it is just nice to have one tool which is very easy to use. Here's what it looks like when connecting to an instance with no load on it:

As you can see, there are some hyperlinked pages as well, and there are also some interesting options (like logging to CSV or for PAL analysis) under the "Option" button. One more thing I liked about it is that there is no installation, just an exe file to download and run. Here is where you find it: http://sqlmonitor.codeplex.com/.