Monthly Archives: August 2014

Trace flags – TF 7806

The trace flag 7806 is necessary when a DBA would
like to use the dedicated administrator connection (DAC) in the SQL Server
Express. The DAC is turned off when you install the SQL Server but it is a good
practice to turn it on after the installation is done. The DBA will really need
the have access to the unresponsive database server and having DAC active
he/she has more chance to do the work. 
However this feature is not supported in the SQL Server Express edition by
default. 

Microsoft has added a trace flag 7806 to enable
this feature in the SQL Server Express.

As the flag is a global flag we have to turn it
on the service level. You should add a parameter –T7806 to the parameter list
and restart the service.

After you did that you are now able to use the
DAC in the SQL Server Express. For example you could try to connect using
sqlcmd tool:

Sqlcmd –S localhostsqlexpress –E –A

I made an assumption that on your local server there is an instance of the SQL Server Express which is called “sqlexpress” and you  connect to this instance using your Windows credentials

When you are connected try to run that query:

SELECT S.session_id FROM sys.tcp_endpoints as E JOIN sys.dm_exec_connections
as S

On E.endpoint_id = S.endpoint_id

WHERE E.name = ‘Dedicated Admin Connection’

 

Last remarks – the DAC can be used only by
sysadmins

SSIS Dashboard v 0.6.1

Yesterday I’ve released the latest SSIS Dashboard update. There quite a lot of new features included that I found to very useful when you have a server full of packages and logs. Here the complete list: Highlighted the feature I think worth the most:

  • Updated Morris.js to v 0.5.1
  • Updated MetisMenu to v 1.1.1
  • Added information on "Child" Packages
  • Added more detail to the "Package Execution History" page. Also added an estimated end time / elapsed time for running packages, using a moving average of 7 steps.
  • Added navigation sidebar in the main page that shows available folders and projects
  • Added support for folders and project filtering
  • Changed configuration file in order to comply with Python/Flask standards
  • Cleaned Up code in order to follow Python best practices (still a lot to do :))

Have you had a chance to give it a try? What features you’d like to see added?

My plans for the next releases is to

  • Add a configuration page so that you can choose the maximum number of rows return (now set to 15) and the time interval you want to analyze (not set to 72 hours in the config file)
  • Use a EWMA instead of the simple Moving Average
  • Do a video to show how to install and use the Dashboard
  • Package everything in only one executable file / directory / VM (I want to be able to offer a xcopy deplyment “all-included”…not only the .py files)
  • Include additional information taken from [event_message_context], [executable_statistics], [execution_parameters_values]
  • Fix the layout for small / medium screens (smartphones / tablet)
  • Add historical / average elapsed time also for Child Packages and Executables
  • Include DataFlow informations

Once all those things will be do, version 1.0 will be ready

If you want to help, fork the code from Github:

https://github.com/yorek/ssis-dashboard

if you want to try it go here

http://ssis-dashboard.azurewebsites.net/

SSIS – Configure a source flat file with a fixed width from an existing table

SQL Server Integration Services (SSIS) can import data from a fixed width flat text file (i.e. no field delimiters), using the Flat File Source component. When importing this type of file, you must set the field names, field widths, and starting positions ahead of time. Typing in this information when there are only a handful of fields is not too burdensome, but when there are hundreds of fields, manually configuring and validating each field can take a long time.

On Agile Data Warehousing

In the last month, I’ve been working on the slide deck of my Agile Data Warehousing workshop. In order to give to it additional value that goes beyond the pure technical aspects, and since now the “Agile” approach is becoming more and more mainstream also (and finally!) on BI, I did a small research to check what one can find on the web regarding this topic. Many things happened from the very first time I presented at PASS 2010, where I first mentioned the need to be Agile (or “Adaptive” as I prefer to say when talking about BI & Agility). In 2011 Gartner, at their BI Summit, stated through the voice of Andy Bitterer that

50% of requirements change in the first year of a BI project

and, as a result, the only possible way to succeed in a BI project is to be able to adapt quickly to the new requirements and requests. The doors to Agile BI were opened.

Agile BI as grown from that point on, until the point that Forrester even started to evaluate Agile Business Intelligence Platform, even nominating Microsoft as one of the Leaders:

Microsoft named a Leader in Agile Business Intelligence by Forrester

I must say I’m not 100% with the definition of Agile BI the Forrester gives, since it puts together to many things (Data Visualization, Automation, Self-Service BI just to name a few), but I understand that they see the things from the end user perspective, that simply wants to “do everything, immediately, easily and nicely” with its data. There is also a definition on Wikipedia (page created on January 2013) that is better, more complete and less marketing-oriented:

http://en.wikipedia.org/wiki/Agile_Business_Intelligence

Beside those definitions, terms like Agile BI and Lean BI became quite common. Of course, with them, came also the idea of Agile Project Management and Agile Modeling. Especially this latter subject seems to be very hot and of course is something that is also close to my interests. Now, I won’t want to go into a deep discussion of the topic, telling you what it good and what is bad. There is already a lot on the web for or against any possible modeling solutions. Data Vault, BEAM, Model-Storming…a simple search on the web and you’ll find thousands of articles. Who’s the best? Should we go for Data Vault? Or for an Inmon-style DWH? Or Kimball? Or something else?

Well…I don’t really care. Or, to be honest, a care just a little bit.

Now, since “Ideas without execution are hallucinations”, and models are ideas after all, it’s my strong opinion that you don’t model the be agile: you “engineer” to be agile. Why? It’s simple: all models are agile…since they are models, and nothing more. Is not a problem to change a model, since it’s “just” a formal definition of a system…(of course, I’m bringing the idea to the extreme here)  and, since we’re assuming that business requirement will be changing, you known in advance that no model that will satisfy them all (immediately) exists (yeah, you can try to model the “Universal Data Model” but it’s going to be *very* complex…). So, the main point is to be able to bring changes quickly, with a measurable quality, in a controlled and economic way.

We all know that the one and only one model that should be presented to the end user is the Dimensional Model. This is how your Data Mart should look like. But how do you model your Data Warehouse is completely up to you. And it will change over time, for sure. So how you implement the process in order to extract, transform and load the data, is the key point. That implementation must be agile. What lies behind the scenes, following the information hiding principle, should be considered a simple “technical enabler” that could change at any time. So, if one prefer to use Data Vault, or Inmon, or just store anything in some hundreds Hadoop server…I don’t see any problem with that. As soon as you have defined an engineered approach with naming conventions, design pattern automation, quality checks, metadata and all the stuff in order to make sure that when you have to change something, you can do the smallest change possible, measure its impact, and test the result.

I’ve been trying to apply Agile principles to BI since 2003…I’ve been through any possible changes that you can imagine (even a complete change of an ERP that was the main source of data) and the most important thing I’ve learned is that the only model that works is the one that is liquid and is able to adapt quickly to changing requirements. I usually start modeling in the easiest way possible, and thus I apply the Dimension Model, and then I make all the changes to it in order to be able to keep

  • All the data at the highest granularity
  • Optimal performances
  • Historical Informations (that may not be visible to end user, but may be needed to correctly transform data)

Which, for complex DWH, means that at the beginning the DWH and the Data Mart overlaps, and that they diverge as the project goes on. In one project we even decided to go for a normalized model of data since the DWH became the source not only for reporting and analysis but also for other, more operative, duties.

Now, in order to be really agile, it’s mandatory to have an engineered approach that make sure that from agility the project doesn’t fall into anarchy. Because this is the biggest risk. The line that separates the two realities it’s very thin and crossing it is very easy. When you have a team of people, or they work as one, or Agile BI is not for you. Otherwise chaos will reign. And to make sure this does not happen, you have to have a streamlined building process, tools and methods (design patterns, frameworks and so on) so that everyone can technically do a good job and technical quality of the outcome is not only proportional to the experience of the person doing it.

It’s really important that everyone who wants to approach Agile BI understand the “engineering” part. I found it always underestimated and in all post I’ve found on the web, I never read someone stressing the importance of that part. That’s why I felt the urge to write this post, and that’s why I’ll go very deep in this topic during my PASS Workshop.

Now, before finishing the post, there is still one thing missing, but vital, for the success of an Agile VI solution: testing. Agility cannot exist if you don’t have an automated (or semi-automated) testing framework that assures you and your users that no errors will be introduced in the data as a result of a change done to satisfy some new or changed requirements. This is mandatory and I’m quite disappointed to see that almost no-one underline this point enough. Forrester doesn’t even took into consideration this point when evaluating the existing “Agile BI Platforms”. That’s a very big mistake in my opinion…since everyone give for granted data quality, but it’s actually the most difficult thing to obtain and maintain.

Testing frameworks are quite common in development, even Visual Studio has a testing engine integrated, and they should become common in BI to. Something is starting to appear (https://nbi.codeplex.com/), but I wish that also big players (Microsoft above all) start to take this subject more seriously. How cool and useful will be a strong integration of testing in SSIS? After DWH/DM/Cube loading one could launch all the tests (maybe done right from Excel, from a power-user, or even created automatically if certain conditions are met…say the generation of year balance) and make sure that the freshly produced data are of good quality.

Just like water. Because data IS water. I won’t drink it if not tested.

Attitudes I Wish I Had Earlier

Mike Walsh ( blog | @Mike_Walsh) tagged me in his post, 4 Attitudes I Wish I Had Earlier as a DBA . You should read Mike’s post, it’s filled with helpful and good advice and tells cool (and not-so-cool) stories. I admire Mike’s transparency. I struggle with the four areas Mike already covered – Work/Life Balance, Empathy, Hero Syndrome, and Getting Things Done. I will not rehash his points. I’m less susceptible to hero syndrome, empathy, and getting things done, but I more than make up for them in…(read more)

SQL Server Hybrid High Availability and Disaster Recovery

As the cloud becomes more and more widely used and the options for SQL Server continue to evolve, more SQL Server shops are looking at using cloud based services such as Azure and Amazon as a valid option for their high availability (HA) failover clustering and offsite disaster recovery (DR) solution. Read this tip to learn about various options.