Friday, July 29, 2011
Thursday, July 14, 2011
One thing that surprised me was that the downloads for the databases were only the MDF (data) file – the log file was not included. After fiddling unsuccessfully with attaching it using the UI in Management Studio (no, I didn’t think of deleting the log file name from the file list in the UI – I’d assumed it was required and didn’t realize that if you did not list a logfile that it automatically treated it as an ATTACH_REBUILD_LOG command), I finally decided that it would be sensible to actually read the instructions. Technet provided me a very simple query to attach the database
CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG ;
Worked like a charm.
I modified the query and attached the AdventureWorksDWDenali database in a similar manner then ran a few quick SELECT queries on various tables to see what they contained. I was pleasantly surprised to see that the OLAP database’s dimDate table contained English, French and Spanish day and month names.
I then launched the BI Development Studio and opened the AdventureWorksDWMultidimensionalDenali project (provided in samples pages on Codeplex). I verified the connection information in the datasource and successfully deployed the cube.
If everyone knew how easy this was, I’d probably be out of a job.
I’m sure that there are other ways to pull this data, however, in some environments your permissions may restrict you from using any method other than this to pull the data.
Tuesday, July 12, 2011
Run this script on a Dev box to set the recovery to SIMPLE for all databases to avoid the above scenario. It works on SQL 2000, 2005 and 2008 SQL servers.
NOTE: it is recommended that PRODUCTION servers use FULL recovery mode rather than SIMPLE.
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
where Name not in ('tempdb') -- cannot set recovery for Tempdb.
ORDER BY 1
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
set @cmd = 'ALTER DATABASE ' + @database +'
SET RECOVERY SIMPLE'
EXECUTE sp_executesql @statement=@cmd
FETCH NEXT FROM DatabaseCursor INTO @Database
For your homework, you can substitute a WHILE loop for the cursor.
If your environment is running mostly SQL 2008 (or higher), please check out SQLChicken's article on setting up Policy Based management to handle ensuring that the dev boxes are all set to Simple Recovery Mode.
Monday, May 2, 2011
1) Using default install settings for file growth
Despite numerous examples from live systems showing that those settings are not appropriate for our product's databases, we frequently see new customers with all their databases set to the default 10% growth setting, despite the statement in the "best practices" documentation that tells them otherwise.
2) Bloated SQL error logs
Many times when customers report having issues and we're called in to examine what's happening with their SQL server, we find that we can't open the SQL Error Logs because the customer's SQL server hasn't been restarted in a long time and the errorlog is so bloated that it's too big for the UI to open in a timely manner. The simple fix, of course, is to set up a SQL job that runs sp_cycle_errorlog periodically.
3) Not doing ANY index maintenance
Frequently, when I hear about SQL performance issues, I find that the customer has turned off the regular index maintenance jobs "because they take too long". Eventually, this results in painfully out of date statistics, severely fragmented indices and terrible performance.
4) Shrinking databases as "maintenance" to "free up disk space"
I try my best not to use profanity or to scream (loudly) when I see this enabled on customer servers. I just take a deep breath and forward the following links to the guilty party:
Paul Randal: Why You Should Not Shrink your Data Files
Brent Ozar: Stop Shrinking Your Database Files. Seriously. Now.
5) Developers "testing" code on production
Don't get me started....
6) Poor backup plans not even close to SLA requirements
High volume OLTP Production database, full recovery with log backup once a day at midnight and full backup once a day at 1AM - but their SLAs say they have to completely recover from failure within one hour. They claim that because the SQL server is clustered, that they don't have to back up the databases more often. Really? Please don't call me when things go south.
7) No disaster recovery plan
... And office in the middle of Tornado alley. Again, please don't call me to resurrect your SQL server when your data center gets destroyed by one of the 500+ tornadoes that went through town. You don't have a copy elsewhere and I can't create something from nothing.
8) Letting idiots have access to the Server room
Believe me: I can't make this one up - it actually DID happen.
9) Not having automated monitoring on servers
You'd think this was obvious, but I've been called too many times to count late at night to hear that someone's server is "down", only to find out the reason the SQL server crashed or the backups failed was because the disk was full. Automated disk monitoring systems have been around for over a decade, yet many of our customers don't have any automated monitoring and I doubt that their IT people check the servers every day since they seem so surprised to discover that their disk has filled up completely.
After just thinking about those 9 items, it's time for a stress pill.
Monday, April 25, 2011
He replied, "@LadyRuna if you're volunteering, you could host it :-D #sqlWIT #sqlRally"
So it looks like I'm hosting the WIT table at the Birds of a Feather event at SQL Rally. Please stop by and say hello if you're there. I've never run one of these before and I'll be making things up as I go along.
Thursday, April 21, 2011
I was talking to my boss yesterday about my professional development plan for this year, and the subject of training came up. He said we only had enough money in the budget to send one person to a conference this year, and since I’d attended SQL PASS Summit last year, they were going to send my coworker this time – meaning I wasn’t eligible to attend any offsite training at all. He suggested I find some books to read to fulfill my professional development plan.
Needless to say, I was not at all thrilled, and mentioned it to my darling husband.
He, too, was unimpressed. He knew how important and useful attending SQL PASS Summit had been for me.
Over dinner, he casually asked me, “Do you think you can take time off from work the second week of May?”
I checked my calendar – I was not scheduled to be on call, so I could take time off.
I replied, “Sure. Why?”
He held my hand and said, “There’s this PASS SQLRally thing in Orlando that week. We have enough vacation money saved up to send you.”
(Queue romantic music and sappy romantic scene….)
Ok, get your mind out of the gutter… The final result is: I’m going to PASS SQLRally and even get to attend one of the Pre-con classes. Hooray for wonderful, amazingly loving husbands! I certainly picked the BEST husband in the world. See you at PASS SQLRally!
Monday, April 4, 2011
Monday, January 24, 2011
As an amazingly wonderful contribution to the SQL community, Paul Randal (blog | twitter) has offered the opportunity for a member of the SQL community to win a free seat at the SQLskills.com 5-day Internals and Performance class in Dallas, February 21-25. To qualify, one has to state in a blog post why you want to come to a class taught by them and why you'd make the best use of the knowledge you'll get from being in the class. I've decided to throw down the gauntlet and accept that challenge.
What would deep SQL Server training provide me?
Although I've been working with SQL server for about 14 years, for the most part it's still a black box. I'm often required to quickly respond to performance and corruption problems related to critical SQL Server systems belonging to my company's customers. I often feel as if I’m staring down the barrel of Mon’s Meg (see picture at right) as I strive to think up what to try next to solve the issues. Normally I wind up following the "well, last time we did -this- and the issue went away..."
For many types of issues involving SQL Server, using past experience to solve them without fully understanding exactly how SQL server functions can produce acceptable results - that is, the issue is cleared, but I cannot explain the failure, nor can I can't elaborate on why what I did worked or why the database failed the way it did.
I often feel as if I’m an archeologist on the Shetland Island unearthing another broch. I know how to handle the items to reduce or prevent damage to them, but may not necessarily have a full understanding of what they are or how they’re really supposed to be used.
Understanding how SQL server stores and retrieves data is crucial to efficiently finding solutions to complex issues within extremely aggressive SLA timeframes. This is even more important when users are wanting to take advantage of the new features of SQL 2008, because structures such as data compression, sparse columns and Filestream can drastically impact a server's ability to perform as the users wish it to.
Many times when I’ve needed quick assistance with solving database issues or explaining to yet another customer why one should not shrink databases daily as “maintenance,” I’ve referenced Paul’s blog to provide the explanations for me. I’m hoping that by attending this training, I will learn what I need to know so that I don’t feel so sheepish when defending the necessary fixes for SQL issues.
I would make use of the knowledge learned in Paul & Kimberly’s class almost every day at my job. It could also help me advance from just another regular SQL person to a senior level or even expert level SQL person.
Finally, I have a friend living about an hour outside Dallas, TX who has a mixed herd of Barbado sheep and fainting goats – proving that sheep and goats can live together happily (Paul knows I have 5 goats).
*ALL photos taken by me on our trip to Scotland in July 2000 (well, except the last one, which was taken in 2009 at my friend’s place in Texas).
Friday, January 21, 2011
It’s Friday, the end of another long work week, and just as we have the monthly T-SQL Tuesday in which all SQL people are encouraged to post something related to a particular topic, the female half of Midnight DBA, the dazzling newly-minted MVP Jen McCown (blog | twitter) has declared this Friday, “Un-SQL-Friday”. For this Un-SQL-Friday, she asked us:
“Read this blog, and then write whatever you want about Tech Giants. Be sure to mention in your blog that you’re writing for Un-SQL Friday, and link to this post. Oh, and have it up any time before the weekend (Saturday Jan 22) hits, mmkay?”
“If you were asked to fill the shoes of __<name of tech giant>_ , could you do it?”
I think the picture to the right answers the question quite succinctly as “Well, I can plug the top of his boot, but I’ll need a lot of other material to fill the rest.” That is, I may not be at the same skill level on that topic as the one who wrote the book on it, but with enough effort and time I could get there. Much of the “greatness” comes from that person having researched and documented something that I haven’t yet encountered. The "SQL Gurus” are the ones who wrote the books I’m buying or actually worked on making SQL server what it is today.
I could rattle off a litany of names (as some of the other un-SQL participants did), or I could
SELECT TOP 10 GuruName FROM SQLServerGurus
and see what that returns to me. Or I could direct you to the listing on the right-hand side of my blog which includes links to many of the SQL blogs that I frequent. Either way, you’ll see that there is no shortage of knowledge available and I thank each and every one for all of the information that they’ve freely provided which has made my job much easier to do.
Tuesday, January 11, 2011
It seems that a lot of us are making similar resolutions for 2011, so if you think you've seen this list before, you probably have. I'm not copying other people's lists, it's just that many of us in the SQL Server Community are seeking to accomplish similar things this year (birds of a feather and all that).
RESOLUTION: BLOG at least once a Month on SQL topics
I started my blog in July 2010, and have been rather lazy about updating it. In 2010, I posted 15 articles, of which only 1 was 100% about SQL. I did have 6 others I tagged with "SQL" or "SQL PASS", but they were not focused specifically on SQL. That's not a particularly good record, especially since the purpose of starting this blog was to write about SQL and NOT about sewing, random thoughts or my goats, even though those are excellent topics for blogging. The good news is that by posting this article, I can check off January's SQL post...a good start to any set of resolutions. Of course, I DO have at least 11 other SQL blog posts that I must create.
RESOLUTION: READ Books on SQL Server 2008
POST reviews of the SQL Server 2008 Books on my BLOG
This is a dual resolution, thanks to Brent Ozar (blog | twitter), who very kindly sent me a copy of "Professional SQL Server 2008 Internals and Troubleshooting" which he co-authored with 6 other people. As a condition for giving it to me, he asked that I write a review of it. (Brent: I'm reading it now - really!!). Reading books on SQL will certainly help me better understand the inner workings of SQL and hopefully advance my career. Blogging about what I read will not only ensure that I absorb the information I read about but also will fulfill my blogging resolution.
RESOLUTION: WRITE a PRESENTATION on a SQL Topic
Volunteer to PRESENT on a SQL Topic
As a SQL professional who has been working with SQL server since the days of SQL 6.5, I really ought to volunteer to present at a SQL-related event - be it a SQL Saturday, SQL User Group meeting, 24 Hours of PASS, or PASS Summit itself. This resolution is a two part resolution since the second part won't happen unless I actually have something prepared to present. I've been hiding in the background whenever calls have gone out for volunteers to speak because I "have nothing to say" and "have no idea what I can present about." Anyone who has heard me talk about sewing and costuming at the Science Fiction and Anime conventions that I've been attending knows that I have plenty to say (well, plenty to say about sewing, costuming, and a few other hobby-related topics) and don't fear getting up in front of 50 or so people and talking their ears off.
RESOLUTION: Become a SQL MVP
This is likely the hardest one for me to accomplish - especially since it requires others to recommend me for the award. However, if I work to exceed the other goals listed above, I might actually have that within reach.
What do you think? What are YOUR resolutions for 2011?
Just wanted to say that I'll be speaking at SQL Saturday Phoenix on 17 March 2018 on " Power BI: The Magic of M. " My pre...
I've been playing with PowerBI recently and have discovered their Gallery of Custom Visuals . I decided to try a few to see what they d...
This post is part of T-SQL Tuesday , a monthly blog party on the second Tuesday of each month which was started back in 2009. This month'...
I've been playing with PowerBI recently, and have discovered their Gallery of Custom Visuals . I decided to try some to see what they ...