SQLChicken.com

SQL Server DBA Tips & Tricks

By

Missing Crystal Reports 10.5 runtime?

The other morning I was migrating an application from a desktop machine to server. Some real fun with this project includes zero documentation from the developer (he was a contractor who did a rush job and left). Thankfully I have experience migrating applications from dev to acceptance to prod so I knew to look for missing assemblies and whatnot.

My big headache this morning, and the source of this posting, was the missing Crystal Reports assemblies since this was developed in Visual Studio 2008. My first indication of a problem was that locally (on the server) I tried pulling up the page and got greeted with the following:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly ‘CrystalDecisions.CrystalReports.Engine, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304′ or one of its dependencies. The system cannot find the file specified.

Source Error:

After some quick Google searching I came across this post at Egghead Cafe with my solution. They provided me links to download the runtime I needed to install. For the sake of helping others I have decided to host the files as well just in case those links die from that link. After installing the runtime on the server I restarted IIS by issuing the iisreset /restart command from the command prompt. After the restart I was able to successfully pull up my page without a problem.

Download Crystal runtime files here (both x86 and x64)

Share

By

SA Does Not Mean Speedy Access

This quick write up comes courtesy of a tweet by Jonathan Kehayias (@SQLSarg) yesterday morning (Please Note: OH means overheard, meaning that statement is something Jonathan overheard. He’s WAYYYYY too smart to actually spread something that dumb as valid advice) . Here’s the tweet:

sqlsarg-tweet

Ok, so I’ve seen a couple of stupid things written up in the last few days but this one just might take the cake. If you have been a DBA for any amount of time then you’ve more than likely come across a vendor application that uses the ‘sa’ account for access to the database. I won’t get into details about the sa (or system administrator) account here but check out this article by Ken Johnson at SQLServerCentral.com about it (check out the discussion thread as well to learn more).

So what exactly is wrong with that statement in the tweet? Well, as stated by Jeff Smith (@hillbillyToad) this morning:

hillbillytoad-tweet

“Ok Jorge, stop making fun of me”. No, as long as you access things using sa for “simplicity” or “optimization” I’m going to beat this over your head like an Acme mallet. Using ‘sa’ account for everything is akin to being handed the keys to the bank and being told “yeah, go ahead and make your deposits and withdrawals from your own account but try not to touch anything else while you’re digging around the vault”. Seriously, I’m not kidding. Handing someone the ‘sa’ account is handing them the keys to your SQL kingdom. Think about it, if you write an application that is accessing your database with FULL admin rights, what if someone performs a SQL injection attack and drops your production tables for kicks?

SQL Injection: It happens

Listen folks, I know that security can be a pain but it’s there for a reason. Don’t get lazy and just assume the user needs an admin account to access the database because 9/10 times it doesn’t. You could probably get by fine on creating a new schema with write/read access and maybe EXECUTE stored procedures permissions. In fact, secure yourself from SQL injection attacks by wrapping your code in stored procedures in the first place.

There’s a ton of resources out there to learn how to properly secure SQL Server. Get up to speed by reading up about Security and Protection on MSDN. There’s also tons of videos and demonstrations out there. Check out the Quest Pain of the Week webcast on SQL Injection courtesy of Brian Kelley (@kbriankelley) and Kevin Kline (@kekline). Finally (WARNING: Blatent self-promotion inbound) make sure to check out SQL University’s security week from Semester 1. Bottom line is if someone tells you “this application needs to run as sa”, have them give you a detailed explanation as to why. Part of your job as a responsible DBA is to protect your data and your database servers. If they simply don’t know any better then offer to educate them on schemas, security groups, etc.  And remember, “because its an optimization” is a stupid answer.

Share

By

IO You an Explanation

This blog post is going to be relatively short and sweet as my expertise in the storage realm is limited. Yesterday I had the pleasure of learning some new and interesting things about NetApp’s SAN technology that I thought was interesting and would share with you.

For  this month’s T-SQL Tuesday #004 meme is hosted by Mike Walsh of StraightPath Solutions (@Mike_walsh on Twitter). For my post I don’t have a solution so much as a nugget of information to pass along I found interesting. I was speaking to a consultant yesterday about a few things and the topic of his experiences with Oracle DBAs-vs-SQL Server DBAs in terms of his experience with them in regards to storage (SAN) consultations. Clearly this perked my ears up and I asked him to explain. He went on to tell me that in his experience he’s seen that the Oracle DBAs he’s come across come across as rather paranoid and never believe anything he tells them despite showing them whitepapers direct from storage vendor on the matter. On this particular matter we were talking about how NetApp has a best-practice recommendation that seems rather contradictory and (rightfully so) the DBAs were skeptical and would continue asking the same question over and over again despite having it already answered…over and over again. What’s that recommendation you ask? Well in NetApp world they have what are called Aggregates which are  nothing more multiple RAID groups. Here’s the excerpt from Wikipedia about it:

NetApp supports either SATA, Fibre Channel, or SAS disk drives, which it groups into RAID (Redundant Array of Inexpensive Disks or Redundant Array of Independent Disks) groups of up to 28 (26 data disks plus 2 parity disks). Multiple RAID groups form an “aggregate”; and within aggregates Data ONTAP operating system sets up “flexible volumes” to actually store data that users can access. An alternative is “Traditional volumes” where one or more RAID groups form a single static volume. Flexible volumes offer the advantage that many of them can be created on a single aggregate and resized at any time. Smaller volumes can then share all of the spindles available to the underlying aggregate. Traditional volumes and aggregates can only be expanded, never contracted. However, Traditional volumes can (theoretically) handle slightly higher I/O throughput than flexible volumes (with the same number of spindles), as they do not have to go through an additional viritualisation layer to talk to the underlying disk.

Ok, so what’s so different about that? Well that’s not the part that’s interesting. What’s interesting is NetApp’s Best Practices for Oracle 11g explicitly states:

For Oracle databases it is recommended that you pool all your disks into a single large aggregate and use FlexVol volumes for your database datafiles and logfiles as described below. This provides the benefit of much simpler administration, particularly for growing and reducing volume sizes without affecting performance. For more details on exact layout recommendations, refer to [2].

Now think about that for a minute. As a SQL Server DBA you’re probably having a mental breakdown as I did when first slapped with this one as they’re essentially telling you throw all your eggs in the same basket, its better for you. Well this is where our conversation got interesting as he started breaking down for me exactly how Aggregates worked, how NetApp’s algorithms function, and WHY this best practice exists and isn’t as bad as it appears at first glance. Apparently because of the way NetApp’s Aggregates work the more you expand your Aggregate (read also: add more disks) you’re actually helping improve performance as you’re adding more spindles to it and will help performance along. At this point of this post you storage guys are probably ready to tear me a new one as I may or may not be explaining this correctly/accurate to which I re-state, “I’m not a storage guy, I’m a DBA learning something new and attempting to relay this information as best as I understood it.”

Which brings me to the point of my post. As a DBA crazy things like a best practice recommendation that doesn’t make sense can and will come up in your career. Should you question them? Without a doubt! After all, it’s your bacon on the line after these guys are gone. The important part however is the learning. Ask questions, realize the differences between technologies and understand the how’s and whys. In this post I talked about NetApp’s solution but EMC works differently as well as has different terminology. It may not be your job to be a SAN admin but as a DBA I think its essential to understand all the technologies involved in your configuration and work with those responsible to come up with the best solution that works for you. There are plenty of resources out there to garner knowledge from, they’re just a quick Boogle search away

Follow the hashtag #TSQL2sDay on Twitter to check out everyone’s posts.

Share

By

Quest Virtual Training – SQL Server Training on DMV’s

Quest Software put on a great all-day training event today on all things DMV courtesy of Brent Ozar (@brento on Twitter), Kevin Kline (@kekline on Twitter) and Ari Weil (@aweil on Twitter).

If you missed this great live event, don’t fret as you can catch the videos on-demand here. One of the amazing things about this event is also the fact that in-chat there are literally hundreds of folks swapping information and resources. Unfortunately when you watch it on-demand you miss out on some of those great chat conversations. Well fear not! I’ve saved the chat log and made it available for download for you so you can see the kind of interaction this great SQL community has. As was mentioned in chat several times, with the economy being the way it is training opportunities are priceless. So check out the on-demand recordings and definitely check out the chat logs as in conjunction with the live event and Twitter (event hashtag is #questQA)

Quest-Virtual-Training-chatlog (.zip format)

Share