Late start into 2014

Long time no post. There’s a number of reasons for that, all of them “overridden” by my laziness. 馃檪 I had a strong resolution of keeping good progress with the blog, but reality proved otherwise. Anyway, I am coming back to blogging since there are some ideas started or good to be shared.

SQL Server

First of all, SQL Server 2014 is publicly available since April 2014, and it’s hot. I’m not willing to copy/paste official marketing statements, but In-Memory OLTP (former Hekaton) and buffer pool extensions seem very promising, especially now, when the hardware prices are declining and there is more capacity available (in terms of both hard drive and memory). I tried it at CTP2 stage and I’m going to return聽to RTM for more testing action聽– I still haven’t experienced it live in production.
I also have some drafts of XML-related posts – how to process XML in SQL Server and mostly how to extract data to XML. This topic was relevant to me at some point and I had problems finding good comparison with execution statistics. Since I’m revisiting this, I’ll wrap it up in a series of posts.


With a change in my job, I’m getting to know Oracle. I’m responsible for deploying an application that is using Database Engine and is hosted on WebLogic Server (both of them 11g). This is a completely new experience for me, so it’s possible some posts related to those areas, for example transition from SQL Server to Oracle.


Earlier on this year, I challenged myself and passed three exams that are required for MCSD: Application Lifecycle Management. I am starting to think in terms of ALM more and more. Last year I made some experiments with TFS 2013 while it was still in Preview stage, focusing on Continuous Integration and Continuous Delivery. Some time passed – TFS 2013 not only went RTM but already got Update 2 – and it might be reasonable to come back to that as Microsoft is incrementally adding new releases to each update. Another point worth noting here is that Microsoft is giving a TFS to everyone for free at, of course with limited functionality but even the free plan gives you unlimited projects for up to 5 users, 60 minutes of build and 15000 virtual user load minutes, so a great place to start.


Phase change cooling of Celeron 430 on DFI DK P35 馃檪 -50 Celsius on CPU done!

I came back actively to overclocking – I liked pushing an envelope a lot since my beginning with computers. I got myself a single stage phase change cooling unit (which is basically a stripped refrigerator – see the picture to the right) and a load of CPUs and RAM dating between 2002 and 2010. There’s going to be a lot of fun with that and for those who are interested you can follow me on hwbot.

And I decided to finally change the boring Andreas theme to something more modern. 馃檪


What I worked with in my life (in terms of computers)

OK, so this is mostly off-topic as the main course is concerned. Yet I thought I might share the list of stuff I worked with over the years.

I am in my thirties now and my first contact with computers was in late 80s, when I played the unforgettable “River Raid” on Atari 800 XL thanks to courtesy of my uncle. This Atari became later mine and I spent time first playing with it, but thanks to it my first non-gaming computer endeavours were about 20 years ago. I was 11 by that time and I first discovered Basic (which sucked on the Atari, especially if you had a tape drive) and then 6502 assembler. I even聽managed to create a simple game in the assembler (I was 13 or so).

Then came the PC. I had a 80486DX2 in 1995, which was a powerful machine then. Of course first steps were games (ID’s Doom and Doom 2, to be exact), but since I had only 4 MB of RAM (yes, memory was then counted in megabytes), I needed to squeeze every possible free byte in order to make the games run. This led me to batch files and multi-boot on MS-DOS 6.22 and further configuration topics like setting up EMS/XMS, UMB聽(anyone remembers those?). Then I came basic to Basic – Microsoft QBasic, which was included in MS-DOS – and tried to create a playable game. After all, QBasic turned out not to be the most friendly and performing and I got my hands on C handbook by Kerrigan/Richie. Using this and Borland C++ 3.1 compiler I made my first steps into programming. It was 1996.

Then I discovered 80386 assembler – I tried to code some graphic effects using it (up to some success, even). I ended up with almost complete聽River Raid clone with animations done in back buffer, most of it coded in C and graphic library in assembler. After that, in summer 1997 I got a quick job by an acquaintance for developing a search engine for website using Perl and text files (no one ever thought it’s NoSQL back then, heh). I saw Windows NT 4.0 and IIS for the first time.

Then I went off the grid completely using computer only for recreational purposes until I started a computer science course on university in 2002. I had contact with many techs (most of them obsolete now, as they were those days as well) – Pascal, C/C++, C#, Java, sockets, assembler, Ada, SQL, even Novell Netware. In the mean time I picked up PHP, JavaScript and some Python, had some XML experience (XSD/XSLT, XQuery, XPath). This list will wrap up my last five years (more or less since I started my professional career):

  • server operating systems – everything Windows – Servers 2003/2008 (with or without R2) and 2012. I didn’t have much experience with NT/2000 though. I had an episode with Linux – Debian 4 – it did work, but that’s all.
  • client operating systems – mostly Windows – 2000/XP/7/8 (consciously skipping Vista). I also tried others – Ubuntu (didn’t like it) or Debian with聽a GUI (didn’t work for me).
  • virtualization:
    • platforms – Virtual Server 2005 R2 (yes, someone still uses it :P), Windows Server 2008 R2 and 2012 w/Hyper-V and Windows 8 with Hyper-V Client. Hyper-V is cool, especially in version 3.0, can’t argue with that.
    • systems – SCVMM 2008 R2/2012 overseeing a home lab consisting of two Hyper-V hosts; it’s more than enough for a proof of concept 馃檪
  • database servers – SQL Server from 2000 up and I feel best with 2005 or newer. I also tried Oracle 11 and have聽a prepared environment for playing with newest DB2 in home lab.
  • web聽servers:
    • IIS 6/7/7.5/8 – included in Server 2003/2008(R2)/2012. I successfully managed to make several classic ASP websites work with IIS 7.5, and it’s tough, IIS from 7 up brings many breaking changes to ASP, which need to be dealt with.
    • Apache 2.0/2.2 – did a pretty standard config, nothing out of ordinary. Most complex thing聽I did were rewrite rules.
  • application servers:
    • SharePoint – I know how to use it and I know how to install it. Installed WSS 3.0 on Windows Server 2003 R2 (that’s easy) and Windows Server 2008 R2 (that’s more difficult, since it’s not out-of-the-box), apart from that some lab installations of SharePoint Server 2010 and 2013. I might administer it but it would need a lot of motivation for me to think about becoming a SharePoint developer.
    • BizTalk – I tried to install 2010 in the lab but couldn’t make it work properly, still willing to give it another try, though 馃檪
    • Tomcat on Windows – installed it but had absolutely no clue what to do it what it after that, so tossed it away.
    • SAP聽Business Connector (rebranded WebMethods Integration Server) –聽I did almost everything with it (versions 4.7/4.8), from installation through administration to development. Good points – it’s Java聽and it聽works; bad – it’s Java and sometimes it doesn’t work.
  • scripting:
    • VBScript – just a few maintenance scripts
    • PowerShell – even more maintenance scripts, but I’m all in for it
  • SAP:
    • I can read ABAP and most certainly I would be able to write but I didn’t
    • I know my way around PP/MM modules
    • I know just a bit about WM module
  • programming:
    • I wrote a few useless apps in C# to help me in the office
    • I refreshed my knowledge of JavaScript by getting to know AJAX and jQuery
    • Even tried to do some stuff in VBA for Excel/Access

What about future? Software world is constantly evolving and it’s speeding up year after year. You have to learn new things if you want to keep up with it. My resolution for next year is the following:

  • have a basic experience with analytics as BI will be more important wig Big Data gaining more and more
  • keep interest in cloud solutions
  • try out some NoSQL (anyone can recommend a product?)
  • work out on application design, including scalability and performance

Contained databases in SQL Server 2012 overview

SQL Server 2012 features a lot of new improvements in all of its aspects. Microsoft boasts their cloud readiness in all the new products – Windows Server, System Center and SQL Server as well. This post will describe one of the Azure-like features – contained databases.

A contained database, as stated in Books Online, is a database which isolated from other database and SQL Server instance. This means that part of the metadata which is required for database operation is included in it. In SQL Server 2012 two containment model are introduced:

  • full containment – all the metadata exists in a database and there is no dependence on SQL Server instance
  • partial containment – some of the metadata exists in a database and some on the instance-level

At this moment SQL Server 2012 supports only partial containment of databases which implies also that uncontained entities are only identified, where in full mode containment could be enforced (see Aaron Bertrand’s post about containment). There are two ways of identifying uncontained entities in a database – sys.dm_db_uncontained_entities DMV and database_uncontained_usage Extended Event. Beware – both show only those entities which are identified at run time.

The term which is closely related to containment level is database boundary, which separates elements that exist in a database (called database model) from those on the instance level (management model). We can say that contained elements exist within a database whereas uncontained elements cross database boundary. Of course, being uncontained is transitive – if an entity references or depends on another, which is uncontained, it is uncontained as well.

There are several benefits of contained databases:

  • probably the most straightfoward is easy movement of contained databases – since the metadata is stored inside a database, it is easier to move it around. Till now, you could do backup\restore, import\export or detach\copy\attach, but none of these methods takes care of login mapping, server-level security, linked servers, Database Mail settings or SQL Server Agent jobs;
  • following from the previous is the usefulness for database mirroring or AlwaysOn failover – if a database is contained, all you have to care for after failover is to point your connection strings to new location because the database is already there with all the information required to operate
  • database development – developer might not have all the details required to properly set up a database at initial stage and may focus at the job at hand instead of concerning of all possible environmental impacts
  • simplified database administration – in certain cases it is required for a database owner to have elevated permissions to server-level roles; this may be mitigated with database containment

Of course, there is always a downside – since a database is supposed to be contained, collation dependence is also to be redesigned. For a non-contained database there are three collations to be considered:

  • DATABASE_DEFAULT – for user data (default)
  • tempdb collation – for temporary data (default)
  • instance default collation – for variables, cursor names and goto labels

In a contained database we are not supposed to rely upon instance information and tempdb, hence a CATALOG_DEFAULT collation which is used by system metadata and temporary objects in a database (by default equal to Latin1_General_100_CI_AS_WS_KS_SC). There are only two collations to be considered:

  • DATABASE_DEFAULT – for user data and temporary data (default)
  • CATALOG_DEFAULT – for metadata, temporary metadata, variables, cursor names and goto labels.

See more details in Books Online article on contained database collations.

Lastly, there are several limitations of contained databases which follow from separation from system databases and collation changes (taken from Books Online):

  • replication, change tracking and change data capture is not permitted
  • numbered procedures are not allowed
  • schema-bound objects that depend on built-in functions with collation changes
  • binding change resulting from collation changes
  • temporary stored procedures are currently permitted but they are marked for deprecation in future versions of contained databases

Wrapping all up – database containment is an interesting option and it surely will be a hot subject in future versions of SQL Server. It will enhance database portability but there will be some points to consider implemeting them (see collation and supported features). I for certain am looking forward to working with it.

Presentation update

The details of my presentation have been confirmed. I am going to speak about high availability in SQL Server on 8.12, just after Lukasz Grala’s session on new columnstore index feature in SQL Server 2012. I am going to have a 90-minute lecture on availability in general and move on to high availability in context of Microsoft SQL Server.
The slides are available for download – feel free to comment them in any way.
We also agreed to have a lab on HA, but this is going to be in January – 90 minutes of fun with log shipping, replication and mirroring and then 90 minutes of quick setting up of your own free MS SQL Server cluster.

Catch-up and some things to do

Well, I haven’t been posting for quite a while. I had some busy time so some catching-up is in order.
First of all, I had a lot of work which will hopefully be reflected – I have designed and implemented a monitoring for replication (based on SQL Server Agent alerts), what may not be the most challenging task, but given the corporate environment around, all approvals and management involved, it grew up to a heroic task at some point. I will definitely share some of my experiences, as much as I hate replication.

Second, the DBA quiz is finished, and it is in order to provide an answer I expected. I guess the question itself was hard, since there were only 6 answers. I’m happy that all of them were correct and consistent up to some details.

I would also like to carry on the index subject since I promised four parts of index theory, posted only one, second and third are still unfinished. I’d like to complete part two this week and part three and four next week.

I have also proposed to my manager two courses on databases for colleagues – one called “Database 101”, second – “Database 301”. Initial idea had a warm reception, I’m looking for some details now. I will check if I can share my materials here, since it’s not even decided if it’s going to be in Polish or English.

And I can boast for a moment – I won聽a first thing for a long, long time. Kalen Delaney posted a competition on her twitter (@sqlqueen) where Microsoft SQL Server 2008 Internals books were prizes. I managed to forget about it but I got a very nice e-mail yesterday morning which cheered me up for whole week, saying that a book is waiting for me. I can’t wait to get it into my hands, definitely I will read it聽 once again.