I walked into the office this morning and got asked a strange question:  “Does a database you detach and re-attach have the same database id before and after the detach?”.  While it may appear that it does in a simple test, it actually doesn’t – it just takes the next available id.  If you detach database id 5 and then re-attach it, it will retake id 5, and this will mostly work – unless other databases have been removed from the server at some point in the past and there are ‘holes’ in the list of available id’s.  So…”How do you force a database to have a specific database id?”

First of all, you can get the current database_ID of all your databases by running:

select name, database_id from sys.databases

What you will likely notice is that the numbers go from 1(master) to roughly the number of databases on your instance. SQL Server assigns the next available database id. What that means is that if you have a clean install database id’s 1-4 will be taken by the system databases and the first database id you create will be 5 and the second 6 and so on. The exception is when you delete\detach a database – this creates a hole which is the next assigned number.  So if you have created databases with ID’s 5-10 and detach the database with id 7, the next database you attach or create will take database_id 7.

The upshot of all this is that if you ever want to assign a specific database id to a new database you need to do the following:

1. If all your database id’s are sequential and the next available number is the id you want – just create the database.
2.  If all your database_id’s are sequential and the database_id is already assigned, detach the database that is currently using it, create the database you want to take that ID and then re-attach the database you dropped.
3. If you want to assign a number that is greater than the currently available database_ids, simply create databases until the next available id is the number you are after, then create the database you want to take that id, then remove all the filler databases you created to get there.

I’m still unsure what the usecase for this is – but now you know how to do it.

If you run the following check on your schedulers:
select * from sys.dm_os_schedulers
You should see them all Online, either Visible for user processes or hidden online for internal database engine processes. Unless you are using Affinity mask you should not see any as offline if you are using Enterprise Edition – right?
With SQL 2012 the licensing changes from a processor to a core model. This led to a need for two editions of Enterprise edition – one for people with Enterprise agreements to transition their servers, and one for new core based installations.
This led to some confusion as the Enterprise Edition designed for upgrading your current Server+CAL licensing from 2008 R2 to 2012 has a 20 core limit(or 40 with hyperthreading). If you are running the Enterprise Edition of 2012 and if you have more than 20 cores, we recommend you take a peak at sys.dm_os_schedulers to make sure you are getting all of what you are paying for.

So – how to fix it?  The process to upgrade from Enterprise(Core Limited) to Enterprise(Core unlimited) is the same as any other Edition upgrade.  You need a copy of the correct install media and run setup.exe.  Then just choose Edition Upgrade from the Maintenance screen:

When you get to the license screen you want to see this:

Be warned you will get an error that tells you this upgrade will break Always On Availability Groups if you are using this feature:

Do not disable your availability groups.  You don’t need to.  Mutter a curse about Microsoft not fixing known bugs, click okay and then forget about it.  Your Availability Groups will continue to function perfectly fine.

I hate the feeling that I am spending my whole day reacting to other peoples requests and problems, but this has become more and more of an issue since I moved into a new role last October.  There’s not many free blocks of 30 minutes in my day.  But spending 30 minutes to learn how to get more blocks of 30 minutes to do stuff in seemed to make sense.

I highly recommend anyone who feels like they are being bombarded with time-sucking requests from all directions to schedule themselves 30 minutes when they can sit uninterupted and watch this video:

https://www.pluralsight.com/resource-center/webinars/watch–on-demand-webinar–3-productivity-hacks-for-your-tech-tea0

It’s very well articulated and presented and will hopefully help you with both your productivity and your feeling that you have lost control of your days.