Category Archives: Sql Server 2012

Monitoring Data Quality activities on your server

In this short post I will show you how to check for the activities run or being run on your Data Quality Server.

First you need to open the Data Quality Client, placed in Start menu –> All programs\Microsoft SQL Server 2012\Data Quality Services. Once you connect to the DQS server, go to the right-most panel (Administration) and click on Activity monitoring button:

image

A new window opens and then you can see a list of the activities that your DQS instance is currently busy (or not) with.

image

In the monitor you can find the following information:

  • Activities, that are performed or currently running on the system (including their start and end time, duration, type, which Data Quality database they have been run against)
  • Activity steps
  • Profiler output (what the activity did in terms of knowledge discovery, data cleansing, etc.)

SNAGHTML13f179b

How to enable PowerPivot and Power View in Excel 2013

I am working on a presentation for our newest local PASS BI Chapter (Bulgaria BI & .NET Geeks) and around the subject I am preparing there are a lot of questions that are popping up. So I decided to show you here how to do some of the stuff I will be presenting.

Before we start though, first we will have to enable PowerPivot and Power View in Microsoft Excel 2013. So now I am going to share exactly this.

So first you open a blank workbook in excel and go to File->Options

options

Once done, go to Ad-ins page, from the add-ins drop-down menu select COM addins and press GO.

add-options

 

After that there is a window opened where you can see all you COM add-ins (both enabled and disabled). So next you have to check PowerPivot and Power View boxes.

add-ins

 

Hit OK and you are done. How to tell that everything is alright? You should check in the INSERT ribbon if you have a Power View button and also if you have a PowerPivot ribbon on top:

 

 

 

powerpivot powerview

 

That is it. Now you are ready to use both add-ins and author reports you’ve never imagined you can do with Excel :)

Enable additional logging for SQL Server management data warehouse collection sets

Today I finally found some spare time to dig into some SQL Server management data warehouse issues we were experiencing. The collection jobs failed for no visible reason and with (believe me) no meaningful error in the logs. So I wanted to enable some additional logging and while searching, I found this (I lost the link, so I cannot reference it :\ … )

If you want to enable additional logging for any of the collection sets you are running, you can use the below statement.

update msdb.dbo.syscollector_collection_sets
set logging level = 2
where collection_set_id = <set id>

If you are wondering what set_id to use, the default ones are:

  • Server Activity – 1
  • Disk Activity – 2
  • Query Activity – 3
  • Utility information – 4

The rest can be discovered using the msdb.dbo.syscollector_collection_sets view.

The difference in logging is that actually if you enable advanced logging, you will be able to see the output of each single component of the SSIS package, used to run the upload jobs for example.

BEFORE:

AFTER:

 

SQL Server 2012 Virtual labs

If you do not have your own lab and you want to access SQL Server 2012 state-of-the art features (such as AlwaysOn), you can easily use Microsoft Virtual Labs. I have to say it is really convenient :) And slick :) I have never used it before, but I am just taking the AlwaysOn lab and I got 3 VMs for a period of 150 minutes, Lab scenarios and additional materials.

How to: Migrate from Failover clustering and Mirroring to SQL 2012 Always On

Hello dear DBAs,

Consider sparing some time and check the resources below, you will find three very nasty but very interesting and deep walk-through in migration from FCI+Mirroring to SQL 2012     AlwaysOn.

How to migrate from FIC+Mirroring to AlwaysOn Part 1
How to migrate from FIC+Mirroring to AlwaysOn Part 2
How to migrate from FIC+Mirroring to AlwaysOn Part 3

Free SQL Server 2012 e-books

In one of my numerous subscriptions these days, I found one very interesting post with a collection of over 85 different free e-books for Microsoft technologies – from Windows development, to Sharepoint and SQL Server. Most of the book in the SQL Server 2012 section are really worth reading. The collection you can find here.

Some of the books are covering quite basic stuff, but hey – those are still good reads. All the SQL Server books are available in .PDF format as well as e-reader formats so you can direct use them for your Kindle for example. So enjoy reading :)

Whitepapers for building an AlwaysON HA/DR solution with SQL Server 2012

After the recent release of SQL Server 2012, Microsoft have been working on developing quite some whitepapers on the subject of AlwaysON. Within this post I will share some very interesting ones that can serve you as a starting point for your learning and consultation on the subject of HA/DR solutions using SQL Server 2012 AlwyasON feature.

Whitepaper: Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

Whitepaper: AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

Whitepaper: Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments, Part 1

and

Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments, Part 2

Note: the last whitepaper is still under its way and Microsoft has not yet released it. However I will keep it here as a container, as once released, those 4 whitepapers will go hand in hand.

Enjoy reading!

Download Microsoft SQL Server 2012 RC0

OK, this is definitely not what I have been expecting around SQL Server 2012. But as usual, Microsoft managed to surprise their users (along with the news around certification, editions, etc. lately) with the release for download of the RC0.

Download Microsoft SQL Server 2012 RC0

The download process requires registration but I believe that should not be a show stopper Smile

The news around SQL Server 2012 are so much and all so big and “life changing” that I would not like to spam you with them. I will just let you read about them yourself Smile

What is new in Microsoft SQL Server 2012 – information on what is new in this version

SQL Server 2012 Developer Training Kit – training kit preview where you can explore new features, technologies and innovations in All Mighty SQL 2012.

SQL Server 2012 Licensing FAQ – interesting questions and answers on the subject of licensing and licensing changes in SQL Server 2012!

Enjoy!

What’s new in SQL Server “Denali”–database restores using SSMS–part 2

In my previous post I shortly introduced you some of the new cool possibilities of SQL Server Management Studio in “Denali”. Now I would like to continue with this and show you a bit more about the database restore part (last time I just showed you how to restore a page). First lets open the Database restore GUI

image

The windows that opens is quite similar to the one we’re so used to but with several differences:

image

In this dialog box I chose to restore database TestBR to the last backup taken. This database is set to Full recovery with Full DB backup on every hour, Diff on every 15 minutes and TLog backup on every 5 minutes (this is for tests sake, do not think that this is my standard approach for implementing a backup strategy Open-mouthed smile)

So, I want to restore my database to the last point possible. Till now that usually means that we have to take manually tail-log backup and then proceed with manual (again) restoring either using the GUI or script writing. Well…in Denali this is done automatically for us. We just have to choose the point to which we would like to recover. Based on that you have a Restore plan created where you can see in very user friendly table what are you going to restore, from what source, etc. In addition to that, on the top of that same window you have a notification message which says that a tail-log backup will be taken of the source database. Well there it is! – you have it all automated, you just need to worry about the availability of the restore media and its integrity Smile

There is one more cool feature on this screen – the Timeline thingy Smile It is used of you would like to restore not to the last backup taken but to a specific point in time in the past.

image

In my case I ‘d like to restore the database to couple of minutes back. After I hit the Timeline button I automatically get this colorful timeline pointing out where I have Full backup taken, where is my Diff and what exactly history I have covered by TLog backups. So my restore point is the red line and it is after my last TLog backup taken. SSMS automatically adds a tail-log backup (check the message on the top of the screen).

image 

This backup is then immediately added to the restore plan. Amazing! Besides that almost everything is done for you (and you do not have to remember to take that tail-log backup), you get and a pretty good visualization of what you want exactly to do and see what your backup strategy is covering.

Once we setup the restore plan, there are couple of other options we have to take care about. On the files tab we can choose where our database files will physically be restored to. You can change the physical path for each file individually or you can do it as “mass” operation by checking the “Relocate all files to folder”.

image

This is extremely useful…especially when you are doing database refreshes.

And finally – the Options tab:

image

Here we also have quite a lot enhancements. The Recovery state is switched to a combo box leaving space for the rest of the stuff( you remember how it is in pre-Denali SSMS – all three options with their simple explanation placed on the dialogue box taking up almost the whole space).  After choosing the recovery state, you have one more new cool option introduced – the “Close existing connections” Options. This is THE THING! Smile I have quite a lot experience with trying to get rid of all users using this database. And besides scripting the restore with adding some ALTER DATABASE <> SET SINGLE_USER it is quite difficult to perform the restore through the GUI. Well now with this checkbox everything seems easier. Besides – this checkbox is doing exactly as we are used to do it manually – before the restore statements it adds a ALTER DATABASE <> SET SINGLE_USER WITH ROLLBACK IMMEDIATE and after the whole restore is completed – ALTER DATABASE <> SET MULTI_USER. Cool, huh? Smile

Well that is pretty much it! A lot of new stuff easing the DBAs life especially the ones that are just loving the GUI and hate scripting Devil

What’s new in SQL Server “Denali”–database restores using SSMS–part 1

After the release of SQL Server Code name “Denali” CTP3 I’ve been playing a lot with it and one of the things that stroke me was the new amazing features concerning database restores using SQL Server Management Studio. You are aware that the old SSMS was capable of doing a ton of stuff but there was also another ton of stuff that it was just not able to do (or suggest you to do). So let me begin with my short introduction to what I have found so far:

Point one is the ability to restore PAGE from the GUI. So far PAGE restores were only for those who can script Smile. Well now we have pretty neat screen where we can do it just with some lame clicking Open-mouthed smile

image

I used one of the corrupted database samples of Paul Randal (blog | twitter) which has a corrupted page (marked as suspect after a DBCC CHECKDB run). the PAGE restore window is a bit rough but for emergencies it will do the job:

image

After choosing the Database, SQL Server automatically extracts the info from table msdb.dbo.suspect_pages and populates the Pages window. You can enter additional pages if you want to using Add button. The tail-log backup is something new that is introduced in the GUI (you will see it and in database restore part) and this section is where you put the filename of the tail-log backup which is going to be performed during the restore. The bottom part is the Backup sets where you can see the last backups available.

In my next post I will try to show you the new stuff around Database restores so stay tuned! Smile