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.
Recall back in January 2011 that I profiled the cool tool, FineBuild for SQL Server, which helps you configure and quickly deploy installations of SQL Server. Not only does FineBuild help you install a new SQL Server using the settings and configuration options you desire, it goes a few steps further by ensuring that any additional programs, tools, and utilities are also installed.
Ed Vassie, the creator of FineBuild, has revised and improved the tools since I last wrote about it. Ed has naturally added a number of bug fixes and documentation improvements. Several new features… [READ MORE]
When you’re developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?
I know that I did just that, for years. It wasn’t until I was trying to performance tune some existing code that I realized I hadn’t actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn’t checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That’s easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn’t need to clear the caches) and adds a metric or two for good measure – literally – so we can better measure what’s happening in that code.
Here’s what my test harness looks like:
/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */
/* Flush dirty pages from the buffer to the database files. */
CHECKPOINT;
/* Flush the data cache and procedure cache, respectively. For DEV environments only! */
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Whatever SQL code you'd like to process goes below.
SELECT SalesOrderID
FROM Sales.SalesOrderHeader H
WHERE CustomerID = 344
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
/* Textual Execution Plans, if desired.
SET SHOWPLAN_TEXT ON;
SET SHOWPLAN_TEXT OFF;
*/
I also like to include the execution plans a lot of the time. You might wonder why I don’t save the execution plans for the GUI in SSMS? Well, I’m a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin’ smarter, not harder, Baby!
So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!
Introducing the Microsoft Broadcaster: A self-service technical content repository that provides you with rich content for your blogs/sites.
On behalf of Microsoft, I would like to invite you to dive into Microsoft Broadcaster. It’s a site that unleashes a torrent of free technical content like Videos, Webcasts, Whitepapers, eLearning, and more, which you can use in your blogs or user group site to drive deeper engagement with your audience and community.
Key Features of Microsoft Broadcaster include:
Download and embed content on your sites without sending traffic away from your site with Microsoft’s full blessing.
Customize site by product (e.g. Windows, SharePoint, or Office), by keyword, or by form factor (e.g. webcasts, videos, podcasts)
There are so many great tools out there for data professionals using Microsoft SQL Server. I really like to see all of these great tools made free to the public. On the other hand, I’m bummed that the tools are cast about in a very decentralized fashion. If you haven’t done migrations before, you might want to start with these good white papers first.
Here are a hand full of cool migration tools worth mentioning:
SQL Server Migration Assistant (SSMA) for Oracle: Migrate from Oracle to SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2. I’m thinking about installing it on my SQL Servers even without even needing to migrate existing Oracle databases to SQL Server. Why? Well as an old Oracle hand, I came to really enjoy quite a few Oracle PL/SQL system packages (kind’a like a SQL Server system stored procedure, but often more powerful). As it turns out SSMA-Oracle includes stored procedures, extended stored procedures, and CLR routines that reproduce the functionality in most all of the cool and powerful Oracle packages like DBMS_PIPES. It’d be nice to have those on my SQL Servers just because I know them and like them.
Microsoft Services for Mission Critical Customers: Many enterprise customers running mission critical applications on SQL Server have asked for more – more service and support for their environments. This is an add-on that costs extra, but it’s worth it for those running the systems that keep the company in business.
If you’ve tried any of these tools out, I’m keen to hear your experiences. Did they work well for you? Did they work, though poorly? Did they fail utterly? Inquiring minds want to know.
I profiled Adam Machanic’s (blog | twitter) excellent stored procedure, SP_WHOISACTIVE, back in August of 2010 in my monthly SQLMag column, Tool Time. Adam has been diligent about maintaining the tool and adding new features. Read the details on my SQLMag Tool Time column.
I first wrote about useful database maintenance scripts on my SQLBlog account way back in 2008. Hmmm – now that I think about it, I first wrote about my own useful database maintenance scripts in a journal called SQL Server Professional back in the mid-1990′s on SQL Server v6.5 or some such. But I digress…
No, not that kind of script!
Anyway, I pointed out a couple useful sites where you could get some good scripts that would take care of preventative maintenance on your SQL Server, such as index defragmentation, updating statistics, and so forth. One of the script kits came directly from Microsoft’s internal database management team. But, alas, they haven’t published any updates in quite a while. On the other hand, the other set of scripts came from Ola Hallengren, who has done a great job keeping his scripts up to date.
Recently, Ola added support for updating column statistics, both in a generalized update and also updates for only those columns whose statistics have been modified. He’s added some other goodies to the latest release, which you can read about here, http://ola.hallengren.com/Versions.html for all details.
In addition, I’d like to remind you of the white paper I wrote a while back called Automating DBA Processes, which covers many aspects of database automation and cherry picks some of the best practices from many different thought leaders, such as Ola, Michelle Ufford (blog | @sqlfool) who will be speaking at the 24HOP soon, and Allen White (blog | twitter).
Disco, aka Discovery Wizard for SQL Server, like John Travolta in "Saturday Night Fever" except the pants aren't as tight
I really like the free SQL Server utility from Quest Software, nicknamed “Disco”, that enables you to discover and inventory all versions and editions of Microsoft SQL Server from the mid-1990′s onward. You can see what it does at http://questkb.com/category/sqlserver/discovery-wizard/.
I’ve had a lot of customers who have faced the challenge of bringing an unwieldy and chaotically growing SQL Server infrastructure to heel. It can be really daunting in a big enterprise trying to find all of the SQL Servers tucked behind third-party applications, Microsoft products like Sharepoint, and various tools that use SQL Server as its data repository.
I’m hearing rumors inside of Quest that Disco may be heading towards the great product dustbin in the sky. Me no likey!
If Disco is a tool you use and enjoy, or it’s a tool you would like to use and enjoy in the future, then please post a comment here and/or send an email to info@quest.com voicing your support for Disco. I’d like to see Quest keep this product free and supported, now and in the future.
Kevin Kline will offer insight into best practices for SQL Server 2005 set up, configuration and queries. He will also discuss tuning for both the server and hardware to help an application perform better after it is deployed. This presentation will offer a better understanding of several SQL Server performance related areas including:
* Hardware design
* SQL Server configuration settings
* Database configuration settings
* Application design
* SQL Tuning
I always enjoy getting the chance to catch up with author, consultant, and Microsoft Clustering MVP Allan Hirt. Allan and I recently presented two sessions covering an overview of high availability in Microsoft SQL Server and, the following week, a demo of how to implement several different kinds of high availability techniques including database mirroring, transactional replication, and Windows clustering services.
We’re joined by my new cohort in crime, Iain Kick, also renowned for his role as the editor-in-chief at SQLServerPedia.
Allan is the author of these and other books:
During the Q&A, we always get a lot of questions. Here are the collected Q’s and A’s.
~~~
Meeting Questions and Answers:
Subject: Pain of the Week We – October 14, 2010
Start Time: 14 October 2010 16:00:00 GMT+1:0
________________________________________
Paul T Asked: General comment….. copying files is where I love the SAN replication. Don’t trust it at all for real database stuff though!
________________________________________
Sherbaz M C P Changes Question To: Please suggest the best back/restore techique for huge databases. Should we go for SAN level backup technologies or snapshots that uses SQL Writer? Storage admins claims that they can restore a 28 TB database in 30 minutes. They are saying that they can even take transaction log backups and restores.
________________________________________
Ameena L Asked: When we talk to our management about SLA, Recovery object and uptime etc. They say just do your best. They expect no downtime but will not define any of this so how we determine what we are shooting for if target is not identified?
Kevin Kline Answered: That’s why actually working through a recovery is so important. I strongly recommend that you perform a full recovery, perhaps to a dev or QA server, and then using that benchmark as the general rules for your SLA.
________________________________________
Stuart A Asked: What do RTO & RPO stand for?
________________________________________
Fadel Asked: at 2 part questions: part1- what is the advantage of having a sql clustering over Oracle RAC where in the first case, we have at least 1 node sitting there doing nothing waiting on the other node(s) to fail, comparing to the second case where Oracle RAC offers both, HA and scalability – part2: what is the new features on sql 2008 clustering compared to sql 2005 clustering – thanks!
________________________________________
Mohammed Changes Question To: Shouldnt clustering considered as a local HA instead of DR since the other nodes are local?
________________________________________
Alan Xu Changes Question To: Is cluster 2008 support sql 2005 instance and sql 2008 instance in the same cluster?
________________________________________
sjohn Asked: What is the maximum recomended distance for a synced mirroring with a reasonable size of pipe
________________________________________
Anita Asked: You mentioned about multi – instance cluster( active-active)? I will be interested in learning more about this. we have active-active in our environment. Is it scheduled for next week?
________________________________________
robert l Asked: Will mirroring work with a large number of “Smaller” databases? 500 2gb databases vs 2 1tb database
________________________________________
Alvinator Asked: When log shipping if you have a large tlog will it ship it in chunks or the entire log file?:
________________________________________
James M Asked: What are the advantages of Database Mirroring over transaction replication? or vice versa
________________________________________
Martin Asked: IDEA – Maybe Quest should provide a Database Invenotry/Request to gather all the information for a HA environment
________________________________________
Martin Asked: I am very interested in finding a holistic view to HA and DR to handle SQL Server. Oracle and even DB2 – are you aware of such a product/s?
________________________________________
Alan Xu Changes Question To: How to handle SSIS in multiple instance?
________________________________________
Nadim Y Asked: I was coming from a consolidation angle – looking to install muliple instances on each node within the cluster. Is there an upper limit or just based on the resources available on the nodes
________________________________________
Mohammed Asked: IS clusteting possible in std edition sql 2008?
________________________________________
Anita Asked: You mentioned about multi – instance cluster( active-active)? I will be interested in learning more about this. we have active-active in our environment. Is it scheduled for next week?
Kevin Kline Answered: Next week is mostly demos of how to set up these techniques. However, there are some resource slides at the end that point you to more websites and whitepapers about each of these specific technologies.
________________________________________
robert l Asked: Will mirroring work with a large number of “Smaller” databases? 500 2gb databases vs 2 1tb database
Kevin Kline Answered: absolutely! however, there is some management overhead in that mirroring is configured on a per database level
________________________________________
Alvinator Asked: When log shipping if you have a large tlog will it ship it in chunks or the entire log file?:
Kevin Kline Answered: It doesn’t ship the transaction log, it shipps the backups/dumps of the t-logs. That’s an especially good use for backup compression. We have a lot of features specifically for this in our LiteSpeed backup tool.
________________________________________
James M Asked: What are the advantages of Database Mirroring over transaction replication? or vice versa
Kevin Kline Answered: We’re on those slides now. =^)
________________________________________
Martin Asked: IDEA – Maybe Quest should provide a Database Invenotry/Request to gather all the information for a HA environment
Kevin Kline Answered: I like it, Martin. I’ll put that in my notebook. Btw, have you seen our “Discovery Wizard for SQL Server”? It does inventorying for SQL Servers.
________________________________________
@SQLRich Asked: I’ve heard that not all of the SQL components are cluster aware, is this true?
Kevin Kline Answered: Yes, afaik, SSIS is one of the biggest offenders in this area. But I think there are others.
________________________________________
Desi Asked: Can you mirror a database to more than one locaton?
Kevin Kline Answered: I don’t recall the exact steps to do this, but yes. Also available with replication and log shipping.
________________________________________
Mohammed Asked: IS clusteting possible in std edition sql 2008?
Kevin Kline Answered: Discussed on slide 11, Mohammed.
________________________________________
sjohn Asked: What is the maximum recomended distance for a synced mirroring with a reasonable size of pipe
Kevin Kline Answered: That’s on slide 10,
________________________________________
Nadim Y Asked: Is there a maximum number of instances you can have in a multinode cluster
Kevin Kline Answered: Not sure what you mean, Nadim. Usually, I don’t see more than 2 actives to 1 passive.
________________________________________
Mohammed Asked: One question before we leave please..i asked this before also. Clustering is more Local HA and mirrroing/ls is DR…is this correct?
Kevin Kline Answered: Clustering across fibre can be miles, sometimes many miles distant, though it’s uncommon. Mirroring and log shipping can be long-distance. Latency varies per technology type and is also another issue as to whether the solution is considered HA or DR.
