I was recently contacted by Webucator, an online training services provider, and asked if they could turn a recent post of mine (#SQLServer – Where does my index live?) into a video. They are promoting their SQL Server classes by doing a free series called SQL Server Solutions from the Web using (with permission) different blog posts from around the web. Wish I’d thought of this sooner; enjoy!
Today, I got asked by one of my DBA’s about a recently deployed database that seemed to have a lot of filegroups with only a few tables. He wanted to verify that one of the tables was correctly partition-aligned, as well as learn where all of the indexes for these tables were stored. After a quick search of the Internets, I was able to fashion the following script to help. The script below will find every index on every user table in a database, and then determine if it’s partitioned or not. If it’s partitioned, the scheme name is returned; if not, the filegroup name. The final column provides an XML list of filegroups (because schemes can span multiple filegroups) and file locations (because filegroups can span multiple files).
WITH C AS ( SELECT ps.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
JOIN sys.destination_data_spaces dds ON dds.data_space_id = f.data_space_id
JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id
UNION
SELECT f.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
)
SELECT [ObjectName] = OBJECT_NAME(i.[object_id])
, [IndexID] = i.[index_id]
, [IndexName] = i.[name]
, [IndexType] = i.[type_desc]
, [Partitioned] = CASE WHEN ps.data_space_id IS NULL THEN 'No'
ELSE 'Yes'
END
, [StorageName] = ISNULL(ps.name, f.name)
, [FileGroupPaths] = CAST(( SELECT name AS "FileGroup"
, physical_name AS "DatabaseFile"
FROM C
WHERE i.data_space_id = c.data_space_id
FOR
XML PATH('')
) AS XML)
FROM [sys].[indexes] i
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY [ObjectName], [IndexName]
I’ve been intrigued by the whole concept of Big Data lately, and have started actually presenting a couple of different sessions on it (one of which was accepted for PASS Summit 2014). Seems only right that I should actually *gasp* blog about some of the concepts in order to firm up some of my explanations. Getting started with Hadoop can be quite daunting, especially if you’re used to relational databases (especially the commercial ones); I hope that this series of posts can help clear up some of the mystery for the administrative side of the house. Before we dive in, I think it’s only fair to lay out some of the initial challenges with discussing Big Data in general, and Hadoop specifically. Depending on your background, some of these may be more challenging than others.
Welcome to the wild, wild west. If you come from a commercial database background (like SQL Server), you’re probably accustomed to a mature product. For Microsoft SQL Server, a new version gets released on what appears to be a 2-4 year schedule (SQL 2005 -> 2008 -> 2012 -> 2014); of course, there’s always the debate as to what constitutes a major release (2008 R2?), but in general, the core product gets shipped with new functionality, and there’s some time before additional new functionality is released.
Hadoop’s approach to the release cycle is much looser; in 2014 alone, there have been two “major” releases with new features and functionality included. Development for the Hadoop engine is distributed, so the release and packaging of new functions may vary within the ecosystem (more on that in a bit). For developers, this is exciting; for admins, this is scary. Depending on how acceptable change is within your operational department, the concept of rolling out an upgraded database engine every 3-4 months may be daunting.
Hadoop is an open-source product, so if you’re experienced with other open-source products like Linux, you probably already understand what that means; open-source licensing means that vendors can package the core product into their product, as long as they allow open access to the final package. This usually means that commercial providers will either bundle an open-source product with their own proprietary side-by-side software (“we interface with MySQL” or “we run on Linux”), or they release their modified version of the software in a completely open fashion and earn revenue from a support contract (e.g., Red Hat). In either case, it’s an ecosystem, not a canned product.
Hadoop technically consists of four modules:
However, take a look at the following framework from Hortonworks (the Microsoft partner for Hadoop):
Lots of stuff in there that’s being developed, but isn’t officially Hadoop. it could become part of this official stack at some point, or it may not. Other vendors may adopt it, or they may not. Each of these components has their own update schedule (again, change!), but there is some flexibility in this approach (you can upgrade only the individual components); it does make the road map complex compared to traditional database platforms.
Perhaps the hardest thing to embrace about Big Data in general (not just Hadoop) is that the nomenclature doesn’t necessarily line up with the driving factors; a Big Data approach may be the best approach for smaller data sets as well. In essence, data can be described in terms of the 4 V’s:
A distributed approach (like Hadoop) is usually appropriate when tackling more than 1 of these four v’s; if your data’s just large, but low velocity, variety, or value, a single installation of SQL Server (with a lot of disk space) may be appropriate. However, if your data has a lot of variety and a lot of velocity even if it’s small, a Big Data approach may yield considerable efficiency. The point is that big data alone is not necessarily the impetus for using Hadoop at all.
Big Data & Hadoop are complex topics, and they’re difficult to understand if you approach them from a traditional RDBMS mentality. However, understanding the fundamentals of how Big Data approaches are evolving, disparate, and generally applicable to more than just volume can lay a foundation for tackling the platforms.
So, I’ve been trying to find more technical fodder for blog posts lately in order to get in the habit of blogging on a regular basis, so I thought I would explore a few of my higher-ranked answers on StackOverflow and provide a little more detail than that site provides. This is my highest-rated answer (sad, I know, compared to some posters on the site):
Determine a table’s primary key using TSQL
I’d like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?
My answer:
This should get you started:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.CONSTRAINT_TYPE = 'Primary Key'
Besides the obvious faux pas of using SELECT * in a query, you’ll note that I used the INFORMATION_SCHEMA views; I try to use these views wherever possible because of the portability factor. In theory, I should be able to apply this exact same query to a MySQL or Oracle database, and get similar results from the system schema.
The added benefit of this query is that it allows you to discover other things about your PRIMARY KEYs, like looking for system named keys:
SELECT tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.CONSTRAINT_NAME, ccu.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.CONSTRAINT_TYPE = 'Primary Key' AND ccu.CONSTRAINT_NAME LIKE 'PK%/_/_%' ESCAPE '/'
or finding PRIMARY KEYs which have more than one column defined:
SELECT tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.CONSTRAINT_TYPE = 'Primary Key' GROUP BY tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.CONSTRAINT_NAME HAVING COUNT(*) > 1
This blog post is way overdue (check the dates in the errror log below), but I promised our sysadmin that I would write it, so here it is. Hopefully, it’ll help some of you with this aggravating issue. During an upgrade of our SQL cluster, we ran into the following error as we attempted to upgrade one of the instances:
2014-04-15 23:50:14.45 spid14s Error: 574, Severity: 16, State: 0.
2014-04-15 23:50:14.45 spid14s CONFIG statement cannot be used inside a user transaction.
2014-04-15 23:50:14.45 spid14s Error: 912, Severity: 21, State: 2.
2014-04-15 23:50:14.45 spid14s Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2014-04-15 23:50:14.45 spid14s Error: 3417, Severity: 21, State: 3.
2014-04-15 23:50:14.45 spid14s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
Google wasn’t helpful; there’s apparently lots of potential fixes for this, none of which helped. The closest match we found was that we had some orphaned users in a few databases (not system databases), which we corrected; the upgrade still failed. We eventually had to contact Microsoft support, and work our way up the second level technician. Before I reveal the fix, let me give a little more background on how we got orphaned users.
You see, shortly after we upgraded to SQL 2012 (about a year ago), we did what many companies do; we phased out a service offering. That service offering that we phased out required several database components, including a SQL login associated with users in the database, and several maintenance jobs that were run by SQL Agent. When we phased out the service, those jobs were disabled, but not deleted. Our security policy tracks the last time a login was used; if a login isn’t used within 60 days, it’s disabled. 30 days after that (if no one notices), the login is deleted. Unfortunately, our implementation of this process missed two key steps:
The latter was the key to our particular situation; the upgrade detected an orphaned job even though that job was disabled, and blocked the upgrade from going forward. Using trace flag –T902, we were able to start the server instance and delete the disabled job. We then restarted the server without the trace flag, and the upgrade finished successfully.
Resources:
Find and fix all orphaned users for all databases.
Brent Ozar Unlimited’s sp_blitz will find jobs that are owned by users other than sa.
Next Tuesday, I’m loading up Big Blue, and driving over to Birmingham to present at the Steel City SQL Users Group. I’ll be talking about the Agile DBA. Should be fun!
Speaker: Stuart Ainsworth
Summary: Agile development is all the rage, but how do the principles apply to database administrators? This presentation will introduce the basics of the Agile Manifesto, and explain how they can be applied to non-development IT work, such as database administration, maintenance, and support. We’ll cover scrum (one of the most popular development methodologies) and kanban, and identify some of the common struggles with implementing them in an organization. This is an interactive discussion; please bring your tales of success and your horror stories.
About Stuart: Stuart Ainsworth (MA, MEd) is a manager working in the realm of financial information security. Over the past 15 years, he’s worked as a research analyst, a report writer, a DBA, a programmer, and a public speaking professor. In his current role, he’s responsible for the maintenance of a data analysis operation that processes several hundred million rows of data per day.
So after my last post, my developer friend came back to me and noted that I hadn’t really demonstrated the situation we had discussed; our work was a little more challenging than the sample script I had provided. In contrast to what I previously posted, the challenge was to delete nodes where a sub-node contained an attribute of interest. Let me repost the same sample code as an illustration:
DECLARE @X XML = '<root> <class teacher="Smith" grade="5"> <student name="Ainsworth" /> <student name="Miller" /> </class> <class teacher="Jones" grade="5"> <student name="Davis" /> <student name="Mark" /> </class> <class teacher="Smith" grade="4"> <student name="Baker" /> <student name="Smith" /> </class> </root>' SELECT @x
If I wanted to delete the class nodes which contain a student node with a name of “Miller”, there are a couple of ways to do it; the first method involves two passes:
SET @X.modify('delete /root/class//.[@name = "Miller"]/../*') SET @X.modify('delete /root/class[not (node())]') SELECT @x
In this case, we walk the axis and find a node test of class (/root/class); we then apply a predicate to look for an attribute of name with a value of Miller ([@name=”Miller”]) in any node below the node of class (//.). We then walk back up a node (/..), and delete all subnodes (/*).
That leaves us with an XML document that has three nodes for class, one of which is empty (the first one). We then have to do a second pass through the XML document to delete any class node that does not have nodes below it (/root/class[not (node())]).
The second method accomplishes the same thing in a single pass:
SET @x.modify('delete /root/class[student/@name="Miller"]') SELECT @x
In this case, walk the axis to class (/root/class), and then apply a predicate that looks for a node of student with an attribute of name with a value of Miller ([student/@name=”Miller”); the difference in this syntax is that the pointer for the context of the delete statement is left at the specific class as opposed to stepping down a node, and then back up.
Quick blog post; got asked today by a developer friend of mine about how to delete nodes in an XML fragment using the .modify() method. After some head-scratching and some fumbling around (its been a few months since I’ve done any work with XML), we came up with a version of the following script:
DECLARE @X XML = '<root> <class teacher="Smith" grade="5"> <student name="Ainsworth" /> <student name="Miller" /> </class> <class teacher="Jones" grade="5"> <student name="Davis" /> <student name="Mark" /> </class> <class teacher="Smith" grade="4"> <student name="Baker" /> <student name="Smith" /> </class> </root>' SELECT @x --delete the classes that belong to teacher Smith SET @X.modify('delete /root/class/.[@teacher="Smith"]') SELECT @X
Now, let me try to explain it:
So a few weeks ago, I mentioned that I was starting to diversify my data interests in hopes of steering my career path a bit; I’ve built a home brewed server, and downloaded a copy of the Hortonworks Sandbox for Hadoop. I’ve started working through a few tutorials, and thought I would share my experiences so far.
I don’t have a lot of free cash to setup a super-duper learning environment, but I wanted to do something on-premise. I know that Microsoft has HDInsight, the cloud-based version of Hortonworks, but I’m trying to understand the administrative side of Hadoop as well as the general interface. I opted to upgrade my old fileserver to a newer rig; costs ran about $600 for the following:
ASUS|M5A97 R2.0 970 AM3+ Motherboard
AMD|8-CORE FX-8350 4.0G 8M CPU
8Gx4|GSKILL F3-1600C9Q-32GSR Memory
DVD BURN SAMSUNG | SH-224DB/BEBE DVD Burner
I already had a case, power supply, and a couple of SATA drives (sadly, my IDE’s no longer work; also the reason for purchasing a DVD burner). I also had a licensed copy of Windows 7 64 bit, as well as a few development copies for Microsoft applications from a few years ago (oh, how I wish I was an MVP….).
As a sidebar, I will NEVER purchase computer equipment from Newegg again; their customer service was horrible. A few pins were bent on the CPU, and it took nearly 30 days to get a replacement, and most of that time was spent with little or no notification.
I downloaded and installed the Hortonworks Sandbox using the VirtualBox version. Of course, I had to reinstall after a few tutorials because I had skipped a few steps; after going back and following the instructions, everything is just peachy. One of the nice benefits of the Virtualbox setup is that once I fire up the Hortonworks VM on my server, I can use a web browser on my laptop pointed to the server’s IP address with the appropriate port added (e.g., xxx.xxx.xxx.xxx:8888), and bam, I’m up and running.
First, I have to say, I really like the way the Sandbox is organized; it’s basically two frames: the tutorials on the left, and the actual interface into a working version of Hadoop on the right. It makes it very easy to go through the steps of the tutorial.
The Sandbox has lots of links and video clips to help augment the experience, but it’s pretty easy to get up and running on Hadoop; after only a half-hour or so of clicking through the first couple of tutorials, I got some of the basics down for understanding what Hadoop is (and is not); below is a summary of my initial thoughts (WARNING: these may change as I learn more).
Recently, there’s been a couple of great posts about the Death of the Database Administrator, including a response by Steve Jones and a several reactions by the staff of SQL Server Pro; the central premise behind the supposed demise revolves around this one major thought:
The evil cloud has reduced the need for internal systems infrastructure, including database administration. It’s a storm of needs for faster development (agility) and the rise of hosted services; who needs a database server, when you can rent space on Azure? Please note that I’m not specifically anti-cloud, but I’m casting it as the villain when careers are on the line.
Furthermore, in shops where the cloud is banned (e.g., financial services), developers are using tools like Entity Framework to write SQL for them. Tuning SQL thus becomes an application change as opposed to a stored procedure change; DBA’s who do performance tuning have to focus on index maintenance and hardware acquisition. Code tuning is now part of the development domain, and the career of the pure SQL developer is gasping in comparison.
Like all great controversial statements, there’s an element of truth; the cloud, agile approaches, and new technologies are reducing the need for traditional database administrators, but I think we’re a long way away from pulling the plug. However, I will say that over the next decade, these trends will probably continue to grow, eating away at the availability of jobs that do strict database administration (and the SQL developer will probably expire altogether). But not yet.
What this does mean is that if you are intending to be employed 10 years from now, and you’re a database administrator, you’ve got two choices to make today:
So go deep or go broad, but go. I wouldn’t start quaking in my boots just yet about the demise of your career, but change is coming; those who adapt, survive.
For me? I’m going broad. I’ve built a home-brewed server, and downloaded a copy of the HortonWorks Hadoop Sandbox. Stay tuned for my adventures with Hadoop.