Friday, September 28, 2012

Weekly TidBIts (September 28, 2012)

I don't have a lot of experience doing anything very advanced in Excel so I ended up researching some stuff related to Turtle Charts this week. As a result, two Excel articles kick off the Weekly TidBIts today. In fact, despite a busy week, I don't have a lot of SQL Server or MSBI stuff for you until the very last item. It's all good stuff though so check it out if you have time.

Friday, September 21, 2012

Weekly TidBIts (September 21, 2012)

Whoa! It's been a while but I have some neat stuff to share today. SSIS and SSAS/MDX are in the mix along with a special treat if you're "of age."


SQL Server Upgrade Gotcha: Read-only Databases

Let me start off by saying that this is not Microsoft's fault. You really can't expect any different behavior in this scenario. The story goes a little something like this:

We had a couple datamarts that held historical information that could not be recovered from the source so we had them marked "read-only" to prevent them from getting mucked up. These datamarts were copies of another datamart that we do update on a regular basis so they have really long names to explain why they're hanging around in a read-only state.

Well, after the SQL Server 2012 upgrade I didn't notice that the databases got stuck in the "recovery" state. The indicator that SQL Server Management Studio puts at the end of the database name was hidden because the database name was so long. Since no one actually uses these datamarts, no one noticed for a long time. That is, until I needed the disk space for something else. You can't detach a database in a recovery state you know.

Wednesday, September 5, 2012

BI Admin Scripts: Run DTExec with PowerShell

There are a few different ways to run SSIS packages. You can use the DTExec utility. You can create a script that runs it the package via the object model. I think there are some others that I can't remember at the moment too. My scenario required a script that could run a package whether it was built in SSIS 2008 R2 or 2012. Additionally, the 2012 packages could be part of a Package Deployment Model project or a Project Deployment Model project. Read on for a script that covers everything I could think of (so far).

Friday, August 31, 2012

Weekly TidBIts (August 31, 2012)

If you're reading this within a day or two of it being published then I am probably riding roller coasters at Universal Studios Orlando. Try not to feel bad for me.

Wednesday, August 29, 2012

SSIS 2012 Gotcha: DTExec.exe and the Environment Reference ID

Microsoft dropped the ball on this one. The DTExec.exe utility (MSDN docs) takes a parameter called EnvReference or Env for short. You would think that you could pass the name of your SSIS environment or maybe even the path to the environment, especially since the only way to find the ID is to query the catalog database. You would think.

Read along for a PowerShell function that will get the Environment Reference ID for you. In a future post I will put this together with another chunk of PowerShell that you can use to run packages from the standard command prompt or a PowerShell command prompt.

Friday, August 24, 2012

Weekly TidBIts (August 24, 2012)

Another installment of the eagerly anticipated Weekly TidBIts. This week starts off with some more SSIS information but also includes some PowerShell helpers.

Tuesday, August 21, 2012

SSAS 2012 Gotcha: Impersonation Info in Data Sources

I don't know if SQL Server 2012 got pickier about this or what. When we did our upgrade to SQL 2012 we actually replaced our Development and Quality Assurance (QA) servers. As a result, there weren't any databases on there to work with. In order to allow developers to deploy to these servers we created blank AS databases and added a role in the new DB that would allow them to deploy. Unfortunately, this didn't work like it used to (or at least how I thought it used to). Instead, the developers kept getting failures when they tried to deploy along with the following error message:
The ImpersonationInfo for datasource 'ASDBName' contains an ImpersonationMode that can only be used by a server administrator

Friday, August 17, 2012

Weekly TidBIts (Aug 17 2012)

I was locked in a dark room last week working on a project. Well, I guess "locked" is a bit strong and it wasn't exactly dark but I was working on a project. Things didn't go well and I didn't keep track of where I got my information from so I had to start over again. Maybe I will get it right going forward.

Wednesday, August 15, 2012

Ask the BI Admin: How do I get meaningful error information from a Script Task in SSIS?

Most of us have seen the dreaded SSIS Script Task error and struggled to understand what it is trying to tell us. If you haven't seen it before, take a look at this:
Error: 0x6 at Really Bad Script Task: The script returned a failure result.
Mind-numbingly useless. There is a way, however, to get what you need if you put a little forethought into building your Script Tasks.

Monday, August 6, 2012

Folder Security in SSIS 2012

Deploying my first SSIS 2012 Project Deployment Mode project to the SSIS Catalog was easy. Mainly because I was a sysadmin on the server. When I tried to tighten down the security a little bit things got a little confusing. One of our developers were itching to deploy a new project to our development server so I went ahead and added him to the ssis_admins role in the catalog database but this isn't how I wanted things to be moving forward.

Friday, August 3, 2012

Weekly TidBIts (Aug 3 2012)

Here is a collection of BI-related information that I have found in the past week. I can't think of a single week where I didn't learn something new from someone else so I will try to make this a regular occurrence. (Vacations and holidays notwithstanding :)

SQL Coding Standards: Your Argument is Invalid

It may just be me, but it seems like the Coding Standards debate will never end. A Google search for "sql server coding standards" comes up with "About 738,000" results. A search for "coding standards" at SQL Server Central nets 85,666 items (articles, forum posts, and maybe some other things). Everything has ideas on what they think the standards should be. Many of these notions are carried down from years or even decades of experience. I argue that all your experience may just be a crutch holding up the habits you have developed over the years and your argument is invalid.

Tuesday, July 31, 2012

BI Admin Scripts: Process SSAS database

The following script can be copied and pasted into a .ps1 file for you to use in processing all Analysis Services databases on a particular SSAS instance or a specific database. This script was originally designed to process all scripts on a server rather than a single database, thus the somewhat inefficient manner of looping through all the databases to find the one to be processed. I may fix it in the future as well as add some more parameters to handle things like ignoring key errors rather than hard-coding them.

Wednesday, July 25, 2012

Agile BI and Why the BI Admin Needs to Care

The top priority of a BI Administrator is to keep the BI systems up and running and running well. This is the primary area of overlap with the traditional DBA role. Every DBA knows that the best way to optimize performance of a database-related system is to design it right in the first place.

The great thing about the Microsoft SQL Server platform is that, compared to other DBMS and BI platforms, it is really easy to manage. The benefit to this, if not Microsoft's explicit intention, is that the DBA/BI Admin has more time to work with developers to enable and encourage them to build the best solutions they can.

I recently forgot the importance of this Admin-Developer relationship and it is causing a bit of grief.

Friday, July 20, 2012

Attaching Databases to Local SQL Server Data Engine Instance

Don't you hate it when you can't figure out what permissions you need to set on a file or folder to get an application to work? It's especially annoying when all the security settings seem to be in place but nothing seems to work. The problem I am about to describe is documented on Microsoft Connect but has been closed, in my opinion, incorrectly.

Tuesday, July 17, 2012

Remote Desktop and the DBA

Sometime last week, Brent Ozar tweeted the comment below and I sort of took offense (not really):
DBAs who RDP into the SQL Server console, take one step forward. Closer. Closer to the monitor. I need to slap you.
I must admit that I use Remote Desktop sessions into my BI servers rather frequently but I think I have some pretty good reasons. Some might fall into the realm of excuses but I am open to debate on the subject.

Friday, July 13, 2012

Upgrading BIDS Projects to SQL Server Data Tools 2012

Upgrading your Visual Studio 2008/BIDS 2008 R2 projects and solutions​ to the new VS 2010/SSDT format isn't exactly fraught with peril but it isn't a walk in the park either. SSAS and SSRS projects convert seamlessly since those two products didn't change but upgrading those SSIS packages may take a little bit of work.

Tuesday, July 10, 2012

SQL 2012 Upgrade: Series Overview

Here is an overview of the rest of the series leading up to this post.

SQL 2012 Upgrade Part 8: The Upgrade

Wow, what a hectic couple of days. There isn't a lot to tell here as things went pretty much according to plan. We tuned the plan on a Friday by upgrading our Development and QA environments and took to Production on Saturday morning. If you're looking for some SQL Server drama then read on!

Friday, July 6, 2012

SQL 2012 Upgrade Part 7: Upgrade Plan


Alright! All the testing is done and we're ready to get this upgrade done! We had a rough upgrade plan to start with but we learned a lot during the testing process. As a result, the plan changed and became more detailed. Our weeks of testing and analysis paid off and left us in a vary comfortable place.

Wednesday, July 4, 2012

Coming Soon: Ask the BI Admin

Howdy folks!

I will be wrapping up the SQL Server 2012 Upgrade series sometime next week but I still have plenty of content to follow. I would like to add a feature to this site that is aimed a little bit more at the community.

Starting in August, I will answer questions from the community at least twice per month. My focus will be on questions posed in the comments here on the site or via Facebook or Twitter. I am realistic, however, and given that I only appear to have two regular visitors to the site (one of which might be me), I will also be lurking on the forums at SQL Server Central and googling/binging for SQL Server 2012 problems.

So, if you have a problem with some piece of the Microsoft BI platform, please let me know about it. As I said, I will work on posting at least two answers per month. If I get more questions and enough time to find a solution for them I will post more.

One more thing, though. Keep in mind that I might have to contact you directly to get more information about your problem. I will keep these communications as private as possible. I will also keep your identity as private as possible in the solution post if you so desire. There is no need to be embarrassed. I have had problems before that couldn't solve and have even had to call Microsoft Premier Support; in fact, if you have a major Production issue, I suggest you call them before you contact me. They are good bunch of people and they do a great job helping their customers.

Talk atcha soon!

Monday, July 2, 2012

SQL 2012 Upgrade Part 6: SSAS DB Processing Test


One thing we definitely had to be sure of when we upgraded to SQL Server 2012 was that all the Analysis Services databases could be processed successfully.  This process went pretty smoothly but not without a few hiccups. In the end, the issues we ran into were pretty easy to resolve.

Friday, June 29, 2012

SQL 2012 Upgrade Part 5: Query Playback Testing


Trace replay was a very important part of our testing plan. Our desire was to complete the SQL Server 2012 upgrade without the end-users noticing any change.There is no practical way to completely ensure that we could accomplish this but being able to successfully execute a batch of queries that emulates the live production environment would go a long way towards getting the "warm and fuzzy" feeling.

Wednesday, June 27, 2012

Trace Replay via SQL Server Profiler Fills C: Drive

You might notice that SQL Server Profiler fills up your C: drive with temporary files while you're doing a replay. I found this out quickly since my C: drive was rather small and only had about seven gigabytes of free space.When you're running a trace replay from Profiler there is actually a trace running that monitors the activity of the replay. This is how you can see what the results of the replay are. The problem with this is that, by default, the results of the trace are being stored in temporary files on the C: drive.

Tuesday, June 19, 2012

Wednesday, June 13, 2012

SQL 2012 Upgrade Gotcha: SQL 2000 Not Supported in Native Client

We're only a few days from our SQL Server 2012 upgrade and a new issue pops up. Apparently, the SQL Server Native Client 11.0 (the one that ships with SQL Server 2012) does not support SQL Server 2000. I found this out when I ran a script to test the processing of all our Analysis Services databases.

Friday, June 8, 2012

SQL 2012 Upgrade Part 3: Test Prep


Playback trace capture is a simple process but it is imperative that we capture enough data to get an accurate test. This requires something I haven’t mentioned much yet but really should have: communication. If you don’t want to freak out your developers, especially ones that still have scars from their upgrade bites, you want to get them on board early. Not only will the developers like you better, they will be more apt to help you out. You’re probably going to need their help to communicate to your/their customers and to help you get the right test data together.

Tuesday, June 5, 2012

SQL 2012 Upgrade Part 2: Test Plan


While I probably should have worked out the test details earlier in the SQL Server 2012 upgrade planning, some of the details didn’t come out until later in the planning process (after the schedule was pretty much set). Luckily, we didn’t wait too long and it didn’t impact our upgrade schedule.

Friday, June 1, 2012

SQL 2012 Upgrade Part 1: The Test Environment

This post begins a series I will be doing on how we upgraded all the servers used by our BI Team to SQL Server 2012.

The biggest part of our SQL Server 2012 Upgrade Plan is testing. Although Microsoft hasn’t really changed a whole lot in what we do, there is still the off-chance that some minor change will blow up one of our solutions. I am pretty explosion-averse and was about to start pounding my head against a wall trying to come up with a way to test without impacting developers or users. Then I got lucky; one day I was chatting with a co-workers and he had a really great idea for how to create a test environment that would fit our needs.

Tuesday, May 29, 2012

SQL 2012 Upgrade Gotcha: "Windows PowerShell 2.0 already installed" rule failure

SQL Server 2012 requires PowerShell 2.0 to be installed on the machine you're putting SQL 2012 on. So, if you're running an instance of SQL Server prior to 2012 that you want to upgrade on a Windows Server 2008 R1 machine, you will need to install PowerShell 2.0 first.

Friday, May 25, 2012

SQL 2012 Upgrade Gotcha: Network Backup Location

If you have your database backup location set to a network (UNC) path you might run into the following error during an upgrade of the Data Engine to SQL Server 2012. I think the upgrade process runs under the Local System account so it doesn't have permission to modify rights on the network location.

Updating permission setting for folder '\\<network path>' failed. The folder permission setting were supposed to be set to '<some gobbledy-gook that looks permissions and a SID>'.

Thursday, May 17, 2012

Saving changes is not permitted?!

So, you are working on a database where you have DDL permissions, if not db_owner, but SQL Server Management Studio barks at you because you want to insert a new column in the middle of the table. The message you get looks something like this [emphasis added]:
Saving changes is not permitted. The changes you have made require the following tables ot be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
<List of tables>

Wednesday, May 9, 2012

Installing SQL Server 2012 Upgrade Advisor

We're hot and heavy into upgrading our BI environment from SQL Server 2008 R2 to 2012 and plan to go live around the middle of next month. To that end, I am in the process of running the SQL Server 2012 Upgrade Advisor on copies of our production servers. Before we can run it, however, we have to get it installed. That turned out to be a little on the obtuse side.

Greetings Business Intelligence people!


I am a DBA-turned-BI Administrator. I'm not sure I like the term BI Administrator but it's a role that's a whole lot different than the traditional DBA. In fact, where I work, I don't even have to worry about some of the traditional DBA stuff. I am lucky enough to have a great team of folks that make sure my SQL Servers stay up and that all those databases get backed up properly. So, I get to focus on making sure that the performance of our BI systems is up-to-snuff. We use the Microsoft Business Intelligence stack from SQL Server to SharePoint. (Our SSRS implementation is managed by our fine SharePoint team, more luck for me!)

Given all that, this blog is about the administration of Microsoft SQL Server and all it's related products with a focus on BI performance and manageability. Sometimes the content will be relevant for Developers - note the capital D, I was a Developer once and I am proud of it - since a lot of the performance work in the BI space falls to them; however, my hope is that the content here will help administrators get the most of out of their Microsoft BI solutions and help them work closely with the BI Developers to build them right from the start.

Enjoy!
Bryant