Goodbye Oracle VC but hello…

wheels_came_off

<insert your own metaphor>

It is with a tinge of sadness that we announce the Oracle Virtual Chapter is no more, but do not despair, all is not lost!

Background

Around 14 months ago the leadership of the Oracle Virtual Chapter changed hands after it had laid dormant for approximately the same length of time as from then to now. Back then PASS were not entirely convinced that there was an audience or market for this Chapter, but we discussed our vision for it’s future and for a time the VC had a stay of execution.

The revised vision for the Oracle Virtual Chapter was to encourage understanding of Oracle based technologies for a Microsoft audience, so we hoped to cover integration topics (for instance SSIS ETL from Oracle data sources) and even showcasing certain Oracle features and contrasting SQL features (i.e. what rocks or fails in each Data Platform) with a view that this could help encourage discussion.

We are still excited by this vision but I think the key problems remain:

  • How do you “sell” Oracle Technology Training to a Microsoft Community?
  • Is there a need for Oracle Technical Training in a Microsoft Community?
  • Should we be trying to “sell” Oracle Technology Training to a Microsoft Community?

The “how” was always going to be a difficult question to answer but in many ways I think is largely dictated by the more important question of need. If there is no need for training then the how you “sell” it will fail regardless. It became quite apparent early on in our efforts to start the ball rolling again that there was a distinct lack of interest in Oracle within the SQLPASS Community if social media responses (or lack of) to our messages was anything to go by. Even during presentations I would talk about the re-launching of the Oracle Virtual Chapter and receive blank stares -and only ever had one single person show an interest during all this time. I could only conclude that the need for Oracle training within the Microsoft (SQL) community was far less than I had expected.

I found the question whether we should even be attempting to “sell” Oracle Technology to a Microsoft Community the hardest one to answer. While many of us work in Multi-Database Platform environments, there is no getting away from the fact that this Community is first and foremost Microsoft centric and exists (in part) because of the funding and support of Microsoft. However it was unlikely that we would help Oracle marketing shift more units (over SQL) since we were mostly trying to speak to an audience with existing and heavy Microsoft deployments. Therefore even though I am sure Microsoft would not see the Virtual Chapter as a conflict of interest in any way, there is no doubting that it is/ was an unusual fit to the existing Virtual Chapter Portfolio.

So ultimately we always ended up coming back to the question of relevance. We believe that the OracleVC is no longer as relevant to PASS’s Microsoft centric audience in the same way that it may have been 5 or 10 years ago. The Microsoft Data Platform has grown so large and integrated (and partnered) with other platforms and technologies (such as Hadoop) that suddenly most of us are either stepping firmly in one camp or the other (at least it feels this way) and integrating or utilizing other technologies into that stack where required or necessary, but obviously there are always exceptions.

The Future

With a shrinking potential audience we have decided to move with the times and change things so the Virtual Chapter has a purpose to exist. First we set out some basic goals that we wanted to address.

The new Virtual Chapter:

  • Must be Microsoft Data Platform centric
  • Must have a potential (existing) PASS audience
  • Should incorporate some of the original revised vision (integration, compare and contrast)
  • Should provide something different but relevant

In the end we came up with the mission statement that the VC would:

“cover all technologies and solutions that may be integrated with (or used alongside) the MS Technology stack, in order to complement or improve existing solutions”

…these may include 3rd parties or pure MS product deployments.

Sessions we could cover might include:

  • AlwaysOn Availability Group hybrid deployments using Azure
  • Big data solutions – contrasting Hortonworks, Cloudera and HDInsight
  • (or even) SSIS ETL from Oracle data sources!

…So you see, the Oracle side of things is not entirely dead and obviously there is lots of room and scope for cross over with existing VCs and we think this is a good thing. It means that there should be a captive audience and (just as important) speakers.

PASS_VC_Hybrid_smallAfter lots of head scratching, there was only ever one name that seemed a right fit…Hybrid Virtual Chapter. Coincidently our existing DNA-like logo feels like a perfect fit for this new name so at least that is one thing that can remain the same. So for now I would like to thank everyone who has worked on the Oracle VC in the past and in particular would like to thank Jen Stirrup (b|t), Wendy Pastrick (b|t) and Elizabeth Jeffs for their immense patience and understanding over the years while we attempt to move things forward. This time our aim is to succeed. Wish us luck!

 

Posted in Community, Events, Personal Development, SQLServerPedia Syndication | Tagged | 6 Comments

Problem removing files from TempDB

I recently ran into an interesting problem while attempting to remove approximately half of the TempDB data files configured in a testing environment. As you might expect, there are various SQL Server tasks that are performed infrequently by a DBA, and this is a good example of one of them. Most environments have usually been misconfigured with too few TempDB data files or wrongly sized usually resulting in the classic allocation page contention problems (which is explained by this excellent SQLSkills article “The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention“), but this particular environment had been temporarily over provisioned with disks and TempDB data files (for testing purposes).

Bonus fact: SQL Server 2016 attempts to automatically address the tempdb datafile allocation page contention problem by defaulting to 8 TempDB data files (or less if the number of cores is smaller). This can be overridden on install through the GUI or by using the /SQLTEMPDBFILECOUNT switch if performing a command line installation. Further optimisations have been implemented such as the adoption of uniform extent allocations by default and auto-growing all files simultaneously -both behaviours would formally have required turning on Traceflags 1117 and 1118. Several other improvements have been made to the performance of TempDB database which you can read about yourself in this (currently preview) documentation.

 

So the plan was to remove each TempDB file one by one, restart the instance (if required) and decommission those spare disks so that they can be returned and reallocated elsewhere in the environment. In order to remove each data file we would:

  1. Empty each file at a time (using DBCC SHRINKFILE).
  2. Remove each file upon empty.

Now there was a time (prior to SQL Server 2005) when we were told to tread very very carefully when shrinking TempDB files, and doing so could result in corruption. This perception remains with some of the longer serving SQL Server professionals (I question myself frequently) but if we take a look at KB307487 article “How to shrink the tempdb database in SQL Server” we can see that it is now safe to do so -although (as the Knowledge Base article states) certain scenarios can cause the shrink operation to fail.

So I ran the following code for each TempDB data file:

DBCC SHRINKFILE (tempdb_15, EMPTYFILE)
go
ALTER DATABASE [tempdb] REMOVE FILE [tempdb_15]
GO

The code succeeded for the first few files, right up to TempDB data file 12, where I hit the following error:

DBCC SHRINKFILE: Page 14:56 could not be moved
because it is a work table page.
Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file "tempdb_12" to
other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdb_12' cannot be removed
because it is not empty.

As odd as this error was (especially since the SQL instance was not currently “in use”) I decided to bounce it but after restarting the instance was again greeted with the same error message! After some very quick Google-Fu I came across an old MSDN Forum question “DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.” I ruled out some of the responses but came across the response by Mike Rose:

“I realize this is an old thread but I have found that in most cases work tables are related to Query Plans.

Try issuing the following commands and the shrinking the tempdb:

DBCC FREESYSTEMCACHE (‘ALL’)

DBCC FREEPROCCACHE

there will be some performance hit for this as SQL will have to recreate its Query Plans, but it should allow you to shrink you TEMPDB.

Hope this helps,

M Rose”

 
Now my initial thoughts were that this response was clearly nonsense since I had just restarted the SQL Service for the instance (thereby flushing the SQL caches), but hey what the hell, why not give it a whirl….

…and success! It worked.

Apart from the flushing of the caches, the outcome was even more strange to me for another reason. Upon reboot TempDB is “recreated” upon startup*1 and I would have expected that fact alone to have fixed the problem but from the behaviour I had experienced, something had persisted across instance restart.
*1 you may want to read this interesting post by Jonathan Kehayias “Does tempdb Get Recreated From model at Startup?

Also of interest to me was whether running FREEPROCCACHE and FREESYSTEMCACHE was overkill so when the opportunity arose I attempted first to try only clearing the systemcache specific to TempDB through:

DBCC FREESYSTEMCACHE ('tempdb')

…and then tried clearing temporary tables and table variables through:

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

…and then by using both together. Sadly these did not seem to work.

On my final opportunity I tried clearing the procedure cache (DBCC FREEPROCCACHE) and after running this a few times it appeared to solve the problem and I was able to remove the TempDB file without error.

 


Clearing system caches on a Production SQL Server instance is never a good thing and should be avoided, but at least the next time I run into this problem I have (what appears) to be a work around to the problem I ran into and at least the requirement to remove TempDB data files should be a rare occurrence for most of us!

I confess that I am left with a few unanswered questions and clearly what I experienced does make me doubt the results somewhat (and my sanity) and there were further tests that I would like to perform at some point. So if you run into this problem yourself I encourage you to try this (disclaimer: your fault if you destroy your SQL Server ;) ) and I would be delighted if you leave feedback of what works for you (and what doesn’t)!

Posted in SQL, SQLServerPedia Syndication, Storage | Tagged , , | 2 Comments

Have you submitted to SQL Nexus yet?

sqlnexus

Nexus noun – “That which unites or binds”

If you have not already heard, SQL Nexus (a next generation SQL Server focused event) will be held on May 2nd – 4th 2016 in Copenhagen, Denmark.

The “idea” for SQL Nexus (w|t) was first conceived in 2012 more as a future pipe dream than anything else and it was something that I had in mind when we delivered the second SQLSaturday Cambridge in 2012 in which we included a dedicated SharePoint track.

At that time, the idea was for a SQL Server centric conference that embraced the whole of the MS Data Platform but SQLSaturday did not feel like the ideal platform for something of the scale required. This vision was discussed with Régis in early 2013 and lay dormant while we were both very busy with alternative commitments. But every great idea needs great people, the right time and the right place and it is through the efforts of Régis Baccaro (b|t) and Kenneth M. Nielsen (b|t) over the last few months that has helped drive this idea forward into reality to what will be a sensational event.

Thanks also must go to the following companies who have been pivotal in making this event possible:


The conference will be held on May 2nd to 4th at Cinemaxx in Copenhagen with one day of pre-conference sessions and 2 full days of breakout sessions. The program for the pre-conference is ready and will feature leading Data Platform professionals including:

  • Allan Hirt (b|t)
  • Itzik Ben-Gan (b|t)
  • Tim Chapman (t)

…and many others!

If you have not yet submitted a regular session then you have until the 15th February 2016, but why delay? You can submit your sessions through the Call for Speakers page.

 
If you would just like to attend then visit the website for more information and register!

Useful links :
Call for speakers
Registration
Main Website : sqlnexus.com
Follow us on Twitter @sqlnexus and use #sqlnexus in your tweets.
And if you have any questions then Email Us.

Stay tuned for more info about the event…and look forward to seeing you soon in Copenhagen!

Posted in Community, Events, SQL, SQLServerPedia Syndication | Tagged | Leave a comment