I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job. The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps SSIS Server Operation Records Maintenance...
One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code....
So one of the really nice sponsors at SQL Saturday had a vending machine that was rigged up to release a treat when you sent a tweet. Like so I'm at #sqlsatcambridge home of @redgate #redgatetreat #red668 — Matt Smith (@matt40k) September 10, 2016 and give you… In mine had a SQL Prompt license. At first I was a bit disappointed, I hoping for SQL Test license or a full blown SQL toolbelt but the more I’ve been using it, the more I’m loving it....
Unfortunately this year I’ve not been able to make any of the pre-cons, however the (free) community day looks epic – the hardest part is going to be selecting which session to attend, the final one looking the worst. Aghhh!!! I’m going to have to roll a dice or something! CDC – Change Data Capture in Detail Uwe Ricken What’s new in the SQL Server 2016 for a BI Professional...
Back in SQL Server 2008 Microsoft introduced auditing, specifically the Database Audit Specification. It’s pretty good – despite the title I do actually think its nice feature, it pretty much works and doesn’t have much of a performance impact, my problem is not much love has gone into it since it was released in SQL2008. It claims to meet various regulations such as the EU data Protection Directive, HIPAA, PCI DSS and to be fair, I’m sure it does....
One of the annoy parts of building a warehouse is building the staging database, its an important first step. The staging database is replica of the source systems. BIML can provide away to accelerate this process, however its not perfect. BIML is designed to create SSIS packages – and it does this very, very, well. SQL database objects, not so well. SSDT does this well. The first step in the designing a warehouse is discovery, Azure Data Catalog is an excellent tool for doing the discovery, it allows you to connect to a wide array of data source types and gather the meta data that can be used to build the staging database....
I was at event that Simon Sabin arranged, SQL SERVER TOOLS and SSDT shape the future and one of the questions that was asked, sorry, I forgot who asked (joys of leaving it too long before blogging about it – I want to say Gavin) was, How does (Windows) Containers fit into SQL Server? This is basically Microsoft integrating the Docker technology into the Windows OS, so you in effect, ship a more complete solution....
Another day, another problem. Todays problem was incorrect column length which was causing a SSIS package to fail. Historically I use a freeware tool to compare the different environments schema, but lately I just don’t bother, I have continuous deployment so its easier just to hit the deploy button then it is to try and figure it out. Well today I decided I wanted to it old school – although I’ve been using the SSDT SQL compare in my deployments, I’ve not actually used it in terms of viewing the differences....
There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing. Today I am looking at the SSAS auditing – you can find out more about it on TechNet. Olaf Helper has published some TSQL code for querying the audit data -on the Script Center. But first, we need a table to store the data, here is a TSQL script for creating the table:
I recently discovered the ability to reference other database project (I know, I know), anyway, this cause my lovely automate build\deployment process to fail. Investigating the error led me to, yes, you guessed it a Jamie Thomson’s blog (from 2012). Thankfully, I managed to figure out a clean solution, the DacPac solution on Octopus Deploy library has an option to pass Profile Name, which is the publish profile XML file. He is the cut-down version I’ve used, just the missing variables
I’ve previously blogged about how I was going to detail how I build and deploy my Microsoft BI projects and to be honest, I’ve been putting it off. The main reason is my work has been looking at Visual Studio Team Services, formally Visual Studio Online and I was looking at using the Build functionality that is included. This should make the whole setup process a lot easier for anyone else trying to replicate my setup....
Today, my pull request for the SSAS deployment script was merged into the Octopus Deploy Community Library. So what is Octopus Deploy and why should I care? Octopus is a friendly deployment automation tool for .NET developers. But don’t be fooled. Octopus Deploy isn’t just limited to .NET developers deploying hip new cloud based applications, it can do much, much more. I currently use Octopus to deploy Jasper reports to our 6 HR environments, I also use it to deploy our HR interfaces which are a series of SSIS packages and SQL objects which uses Microsoft SQL Server 2008....
One of the problems we have is documentation and how to keep it up-to-date. We tend to deliver a new batch of reports as part of a CR (Change Request) then make changes as part of BAU (Business as usual) unless its a particularly complex or time consuming change. The problem is often BAU changes happen rapidly often within a short time frame and time isn’t always allocated to updating documentation....
Another little gem, clearing out those pesky recent projects from your menu on Visual Studio 2012. Fire up RegEdit and navigate to: Files: HKCU\SOFTWARE\Microsoft\Visual Studio\11.0\FileMRUList\ Proj: HKCU\SOFTWARE\Microsoft\Visual Studio\11.0\ProjectMRUList\ Usual disclaimer http://nathondalton.wordpress.com/2011/10/13/clear-visual-studio-recent-project-and-files/
I’ve created a new SQL user along with a new role in our warehouse db project using Visual Studio 2012. CREATE LOGIN [newuser] WITHPASSWORD=’{{ RANDOM PASSWORD }}’,DEFAULT_DATABASE=[Database] GO CREATE USER [newuser] FORLOGIN [newuser] WITHDEFAULT_SCHEMA=[dbo] GO ALTER ROLE [newrole] ADD MEMBER [newuser] GO Problem is, the user is disabled. When you do it manually via SQL Management Studio, it’s enabled and working. Manually enabling the account doesn’t allow it connect either. A quick Google revealed:...