One of the things on my todo list for this past week was to upgrade a SQL 2005 Standard Instance to Enterprise Edition. I was doing it remote, and started up the install, forgetting – until reminded by the installer – that you have to use SKUUpgrade = 1 when launching setup. To me, that’s just annoying, they couldn’t build it into the installer? So I throw together a one line batch file (just in case), add the parameter, still get the warning. Ah, must be case sensitive. Try again, no warning this time, make it further along and then get an error that BPA failed (I’m guessing BPA = best practice advisor, but all it said was BPA). It runs a bit longer, then returns “UpgradeAdvisor returned –1”.
Figuring out arcane errors is not what I want to do on Sunday morning. Do a quick search, find a couple suggestions, plus Error while running 2005 upgrade advisor and http://support.microsoft.com/kb/916760. Turns out the copying the BPA files from Bin to the main folder fixed the error. How annoying is that?
Near the end is the reminder to install the latest service pack, as installing the upgrade will overwrite the files you already had there. I get that, sort of. I’ve never dug in deep to see, do the DLL’s really differ from Standard to Enterprise, or is it ultimately just a registry key? I wish it was the latter, because the whole idea of what is basically a full install just to move up (or down, in theory) editions seems like a lot of risk and time. It’s also downtime. Not a big deal in this case, but interesting to think that you have to have a fair amount of down time to move up to an edition that will help you solve a problem.
Not very often I run across something with zero matches in a search engine, but had it happen recently. I had set up a simple demo of a sparse column set and happened to look at the execution plan, was interested to see that the Compute Scalar was backed by ConvertSVtoXML. This was on a select from a table with one row, and then, being a little more curious, updated it with an xml fragment I saw a call to ConvertXMLtoResv. I’d venture that those serve to convert back/forth from XML storage.
I’d welcome more info on the functions, but for me the more interesting part was the zero matches!
I think in general the SQL Server DBA’s of the world prefer that object names not have spaces embedded, and probably not be all upper case either. The spaces are a practical consideration, as soon as you use them you’re forced into bracketing the offending the name ([First Name]). There’s an option to auto add them in SSMS, but they’ve always seemed…noisy. I’ve long since stopped using them, and not really thought about it much since then, until recently I was using a third party grid control on a web page and noticed that it automatically changed ‘FirstName’ to ‘First Name’ for the column header. I’d been used to manually doing that and it was a nice labor saving touch.
I try to use column names that would be reasonable for a report, using FirstName instead of FName. Of course to a non-tech person even FirstName looks like a typo, but it’s not horrible. There’s no guaranteed win though, using “Marital Status” on a report instead of “MS” just wastes (sorta) display space when you just want to report the code (M, S, etc) rather than the literal. Using First_Name is a middle of the road approach.
So, for 15 minutes I’m questioning the no spaces rule. Is it really the right thing, or just what’s best/preferred by me at the expense of every developer and report writer in the company?
The alternative I’ve thought of is exposing a variety of “pretty” names for each column. At design time (and later) we could define the underlying column name as we prefer, but also define a super short version as well as a full length normal looking version. For example:
We’ve already got support for column level properties, would just be a matter of building maybe more direct support into the designer. Thinking really big, if we leveraged some of the .Net’ish syntax, we might do this:
select firstname.TrueName, firstname.Short, firstname.Long from table
Of course, just because I can think of an alternative doesn’t mean we need to build it. Definitely this can be done today without any language enhancements, but it would rely on convention and wouldn’t be widely supported across tools/components. Or we could ask for a parser that was smart enough to know when a space was part of the object name and not a delimiter.
Solving a problem that doesn’t exist maybe?
I think I’m content to stick with no spaces for now. Yet I still think that if spaces were painless for us the DBA’s, we’d use (or even mandate) them.
You can read the full text of it in today’s SSC Newsletter, the quick summary is that for many businesses a database is a toaster. They don’t care how it works, they don’t want to do anything to it, they just want it to work. If it breaks they fix it or call someone. Give it a read and see what you think.
I’m slated to write one a month and I haven’t settled on a theme yet, and don’t know if I will. More likely I’ll look for ideas as I’m out with groups and look for things that intrigue me, not much different than how I find blog ideas. It’ll be interesting to see how it goes.
It’s also funny how you can so easily add one more thing to your task list. Writing one editorial a month doesn’t seem like a lot, even writing twelve a year. But at an average of an hour each, that’s 12 hours that’s either added to the stack or taken away from something else. Ideally you know the answer to that before you say yes!
Back in July we had Chad Miller visit oPASS to do a presentation on Powershell. It was well received and kudos to Chad for a nice presentation and taking the time to drive up from Tampa. Going in to the presentation I would say my view of Powershell was interesting, but over hyped, but then again, I’m typically not a bleeding edge adopter of technology either.
Chad made some interesting remarks during the presentation (and apologies if I put words in his mouth by accident):
The first one sounds plausible. The second – I think – is over stated. I’m sure there is a limit, but verbosity of code has never struck me as a major impediment to getting things done. Of course, that assumes more than passing comfort with the syntax. I’m probably also biased, as many C# people consider VB.Net to be verbose, but I find VB.Net to be reasonable. In both cases it’s the difference between code you really write and pre-packaged/reusable code that impacts productivity. On the third one I agree about 50% – PS does make it easy to discover, but so does the Visual Studiio IDE, either via the object browser or via the immediate window. Calling it 50% is probably unfair, because I think easy of discovery is critical to learning any new product. The last point was really great, until we build that shared knowledge it’s hard for anyone to move into deeper areas. Interesting to see how (if) this moves forward in the next couple years.
I’ve done my fair of scripting and batch files over the years, ranging from almost vanilla batch files to more advanced ones using things like PC Magazine Batchman (best link I could find), VBScript, and just a small amount of PS work so far. Scripting is a useful technique, but once I moved into “real” programming environments I found that going back was hard. Even VBScript scripts I’d write in VB6 for the intellisense, debugging, etc, and then as a final step make it a true VBScript file. I’d say in many ways that a very nice IDE trumps all, for learning and pure efficiency. The Powershell IDE seems like a useful step in that direction, but note quite all the way there.
Some of us will need PS at work, others may never need it. As SQL professionals I think we need to know the basics; what is Powershell, how to load and run a script, the basics of variables and comments. After that you make the decision about where to spend your very limited professional development time – do you learn more PS, Analysis Services, or even something else? No wrong answer.
For those that really enjoy Powershell, I’d suggest taking one more step and trying a “real” language, something that has a first class IDE, supports all the concepts that go into programs today. A good way to do this is with SMO (SQL Management Objects) to build a first class UI around some solution you need. You can call SMO from PS of course, but if you want something really sophisticated, use Visual Studio or similar. Make sure you learn how to use source control either way, and to put good comments in – because even something as small as a batch file can end up being a key part of the enterprise strategy.
It proved to me (again) that I’ll always enjoy hearing about a topic from someone who is both passionate and knowledgeable, someone that has put some effort into making it do tricks.
In the next week or so I’ll be upgrading the SQL 2005 server that has the SQLSaturday database and I’m looking forward to giving the filestream a workout. For those of you new to the issue for years we’ve had the choice of either storing files in the file system and a pointer (filename) in the database, or storing the actual file in the database. The generally accepted decision point was to store in the db if under 1 meg, otherwise in the filesystem. Of course, it’s all one or the the other per column, so you have to decide in the beginning. It’s always been hotly debated, but I’ve been in favor of storing in the db because:
But I also get that that doesn’t fit for all scenarios. Filestreams are new in SQL 2008 and are an attribute of varbinary(max) columns that let you store the file in the file system, but it looks like it’s stored in the table – an interesting hybrid. Right now we store the files associated with each SQLSaturday presentation as a single zip file in the db, so this will be a good exercise in migrating them out to disk as I convert that to filestream. Migration sucks no matter which way, but it strikes me as less painful for those that went with in the database rather than pointers – when I’m doing all the queries will still work and no changes required to procedures or code. I’ll have to write some code to test the streaming option to see if that would also be change free, but at rough glance it seemed like it was more than that. Good to experiment and learn some lessons on a real project, though admittedly a small one!
I suspect I’m not alone in that I’ve had SQL 2000 running for a long time with no problems and no real compelling reason to upgrade. It’s not that I’m opposed to the latest and greatest, but it was easier at the time to just add a SQL 2005 instance for times when we needed a new feature than to worry with upgrading stuff that was working quite well.
It’s been on my list for a while – years you could say – and recently I had some unexpected free time, so decided to invest that time in cleaning up a server and removing the final SQL 2000 from the business. I could probably have done it more easily by just upgrading the instance, but instead I took the time to migrate it to an existing SQL 2005 instance on that machine. That gave me a reason to review all the jobs, clean up some naming disparities, and in general just look things over to make sure we had everything more or less as it should be.
It was also a time to appreciate the power of views and some foresight in using them for a shared database we use for sending email. I wanted to make the change during the week without an outage, so being able to change the views while I moved that db and then alter them again meant that no email was lost and at most it was queued for a couple minutes longer than usual.
What about SQL 2008? Given the clean up just done and the closing down of a few legacy things we no longer need the upgrade to SQL 2008 should be relatively painless, just a matter of finding the time to do it! It’s a Windows 2003 server that has had SQL 2000, now SQL 2005, and soon SQL 2008, so after the upgrade it will be time to think about upgrading the OS. Not a huge priority with me, Win 2003 has been solid and reliable and right now nothing I can think of in Win 2008 that I need. Nice to do, not that important to do.
Given a choice, I’d probably upgrade to new versions as they come out. There’s merit in having all the incremental improvements that come with a new version, and upgrading more frequently forces you to keep things reasonably up to date and upgradeable. Those upgrades come with a cost beyond the license; upgrade time, testing, risk – all good reasons to stay with something that works.
Ran across this in a recent class while covering partitioning. Start by creating a standard partitioning function and the scheme, these intended for demo against the Person.Contact table in Adventureworks:
CREATE PARTITION FUNCTION pfContact (datetime) AS RANGE LEFT FOR VALUES ('1/1/2004'); CREATE PARTITION SCHEME schContact AS PARTITION pfContact TO ([primary], [primary]);
CREATE PARTITION FUNCTION pfContact (datetime) AS RANGE LEFT FOR VALUES ('1/1/2004');
CREATE PARTITION SCHEME schContact AS PARTITION pfContact TO ([primary], [primary]);
Then try creating the new table using the scheme:
CREATE TABLE [#temp]( [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Title] [nvarchar](8) NULL, [FirstName] nvarchar(50) NOT NULL, [MiddleName] nvarchar(50) NULL, [LastName] nvarchar(50) NOT NULL, [Suffix] [nvarchar](10) NULL, [EmailAddress] [nvarchar](50) NULL, [EmailPromotion] [int] NOT NULL , [Phone] nvarchar(20) NULL, [PasswordHash] [varchar](40) NOT NULL, [PasswordSalt] [varchar](10) NOT NULL, [ModifiedDate] [datetime] NOT NULL , ) ON schContact([ModifiedDate])
That yields an error:
Msg 1921, Level 16, State 1, Line 1 Invalid partition scheme 'schContact' specified.
My first thought was that it was that last extra comma, the one after modified date. But, it turns out that it doesn’t work with temp tables. Sort of. I’m not sure how often I’d want to partition a temp table, but it was another one of those interesting times when a student does something different, things break, and you get a less than helpful error message!
Wasn’t too hard to resolve by just making it a ‘real’ table, but my guess is that it breaks because the function and scheme are in the current db, the table would be in TempDB. If you create the objects all in Tempdb it works fine. I'll try to get something nicer written up and sent to Steve soon.
As background, I've never been a fan of multiple instances. It's a useful thing to have available and I use it on a server today, but it's never provided a solid way of isolating resources for each instance.
Next, one of the things I evangelize is that DBA's should make every effort to make a database portable. Portability is important because sometimes we run short of space and/or performance, and the ability to quickly and easily move a db to another server is a nice thing to have. We used to use views to abstract three part and four part lookups, but since SQL 2005 synonyms are a cleaner way to solve that problem. Actually, it only solves part of the problem, since we still have jobs that access the database, external reports, and often linked servers.
We can fix some of that by setting up a DNS alias for each database (thanks for my friend John J for that idea) so that we don't require any app code change if we move a db, and that should handle reports too. Linked servers aren't terribly hard, but what about jobs? Is there an elegant way to handle them besides a good naming convention such as DBName - Job Name, or creating a category that maps to each db, or just using the dbid assigned as the job context?
Instead, I propose a simpler model - every database runs in a separate VM. That forces portability because everything moves as a package. It's a challenge as far as licensing because unless you have the Enterprise Edition you have to buy licenses for each VM, but it might be worth spending more (or getting MS to change the licensing model). Nothing that says you couldn't put more than one database on a VM when it made sense - an example might be a lookup db or an archive db that you really do want in the same place all the time).
Licensing is a negative, I think all the rest is positive if you can live with SQL on a VM. It's a brute force solution, but it also feels mildly elegant. If the licensing question could be fixed would you consider this as a solution?
One of the labs I frequently do with students is to design a database for a training business, something which I know something about and can add some challenges as the lab progresses. Students tend to either under or over normalize, though of course that's all in your point of view!
As we work through it I remind them that the cost of development goes up with each join. Yes, SQL Server is good at joins and most of write them without problem, and yes, a normalized database is a good thing. But. As a total guess, I'd say that each join adds at least 10 hours work to a project. Remember that in most cases joins require another form/page/control/screen to support it administratively (you do build admin tools don't you?) and those take time - time to design, code, build, test, revise, deploy, etc.
So let's say you're in the common case of needing to get something to market very quickly that is as good as possible, but time to market is a key factor. As you think about joins affecting design and build time, you can take two approaches - omit building the form/page that allows you to use it/maintain it, or deliberately denormalize the design in key places to help you gain time.
The key...is the key places.
Here's an example. As we arrive at a close to final design I typically see that most classes add a locationid and a corresponding location table - this is to indicate where each class will be held. In my particular case 90% of classes are held in Orlando and location isn't huge, but that could change, and I wouldn't argue it's bad design. The other thing I see that is most often missed is they treat the student as a login and capture demographic data for the student, but fail to capture what is for me key data - the employer at the time they attended the class.
Which of those is easier to fix later? Converting a location varchar(200) to a lookup table, or amending the design to capture employer per class attendee? In the latter case we may have lost data we can't recover, in the former we may have junked up data ('Orlando', 'Orlando, FL', etc). I'd argue that if you had to take a shortcut, do it on location. At worst we have to massage the data to convert it later, and even for reporting now it's not likely to cause huge problems.
To recap, there are two points here. One is that you have to understand a design with 100 tables is going to cost a lot more to build than one with 10 tables, up to you to move the slider. The other is that a really good architect/DBA can help decide which tables to remove when someone sets the slider to something besides 100 so that you can evolve the design with the least amount of pain. There's probably something to be said for creating the full 100 table design, then from that reducing it to the x table design for speed. Think of it as designing your dream house, then building it with 3 bedrooms instead of 6, you'll add more when time and funds permit.
Minimalist design can easily be confused with laziness, and also sometimes hard to tell the difference between minimalist and downright ugly. Here's a quick and probably imperfect example.
I've been working on a lightweight advertising/invoicing/etc design for one of my projects. One of the things I decided to store in the db was a template for what is basically a report (I'll blog on that soon). It went with a particular part of the project, so I had a table with one row that held the template. No plan for history as part of v1, we'd just change the template and move forward. Now the first part about that is minimalist is my total comfort level with the idea that if I wanted to change things later to track all the version changes it would be easily done, and would have no affect on stuff doing prior to that point.
Based on that, I stubbed in a todo item that we would need a UI to allow making changes to the template. Not a high priority item, but I've learned the hard way that if you build a system,you build the tools needed to manage it or you're doomed to only letting power users make changes.
As things evolved I needed a template for a different part of the system, and it was a very similar implementation. I hate duplication, so I stopped and looked some, and saw that I'd need one more template before I was done for yet another part of the solution. Now I could just create three tables, one for each area and let them store one row, or I could make a minor change to the first part of the solution to reference a shared template table. Which to do and why?
For me, condensing it down to one table meant I needed only one UI implemented instead of 3. Yes, they'd be similar, but each would need it's own set of procs and it's own menu item. Do I save a lot by doing that? Maybe I save an hour. Do I lose anything? As far as I can tell I don't, but it's still one table and I'm still comfortable that if I need to change things, the change is isolated to one area and won't require a lot of pain.
Even here you can see that there are some interesting design decisions. I could have stored the template in the file system and just used notepad to edit it (very minimalist), or I could have built a report in Reporting Services but I would have needed three of them, and probably other options I didn't see. The biggest reason to put it in the db is to provide the ability to edit it via the web, and I prefer not to have apps working in the file system for the web.
Regardless of whether you evolve a design my way or another way, you'll encounter a lot of places where you can add/remove complexity. Adding one more table doesn't seem like a lot. Adding 10? Think about how you'll maintain it, index it, build a UI against it, and only build what you need - without being blind to tomorrow.
Note: I was working on this at the Summit and then it got lost in the draft folder, so unfashionably late here are a few comments about the event.
I flew out from Orlando on Sunday, had a reasonably pleasant flight to Dallas, actually arriving 15 minutes early. So far so good...except, there was still a plane at the gate, so we had to wait. And wait some more. Turns out that the plane at the gate had a mechanical problem, so they had to offload the passengers, and then before moving it, add 2 tons of fuel. Probably more detail than we needed, but interesting. Why would fuel need to be added? I'm assuming for balance, but you'd think that you'd only care about side to side balance, and how does taking passengers off change that? Sorry, I'm always curious!
Onward to Seattle and very nice weather until about an hour out, then it got gray and rainy. Met Steve Jones at the airport, took a town car ($45 if you're wondering, nicer than a taxi at the same price) in to the Sheraton, checked in, and then checked in at the Summit over at the convention center. Normal conference registration process, quick and easy. Then off to get coffee, and started running into people we knew, starting with Rick Heiges, and then Arvin Meyer. Had coffee, then back to the convention center to get ready for the reception, setting up at a table near our MVP Lead Alison Brooks to use our normal networking plan. It worked, but we made the tactical mistake of picking a high top table instead of one of the lower chairs. High table was easier to see and be seen, but we had the only 2 high chairs, so we had a lot of people standing up.
The reception went well, lots of SQL people present, and I'm sure I've missed some, but it included Robert Cain, Jessica Moss, Bill Graziano, Rushabh Mehta, Allen White, Alexander Kuznetsov, Brad McGehee, Paul Nielsen, Pinal Dave, Simon Sabin, Itzik Ben-Gan, Jung Sun Kim, Roy Harvey, Ron Talmadge, Greg Linwood, Peter Ward, Arnie Rowland, Michael Coles, Chuck Heinzelman, Plamen Ratchev, Rodney Landrum. And though not a SQL guy, our Florida developer evangelist Joe Healy. It was just nice to sit and catch up with people I knew and meet people that I knew online but not in person.
On Monday we all rode buses to the MS Campus and headed off to the rooms for presentations by various parts of the SQL dev team. As much as I hate to say it, I can't tell you the session titles or even the presenters! I can tell you it was a nice facility, and that I saw a few nice techniques for providing feedback about what ideas/features were the most interesting. One was a just a simple raise your hand if you (really want this feature, find it interesting, don't care) so they could get a quick count, another was based on having a budget of x points, assign points to various ideas indicating which ones you would fund with your budget.
Monday night we had dinner with all the SQL MVP's present in Bellevue and again, it was a nice time to relax and talk. Not so much networking at this point, more just relaxing and talking about whatever. Repeat for Tuesday. Tuesday night was a social gathering downtown. See Steve's blog for more details on that!
It was a good event, and I suspect a great event in a year just before a new version release. It was definitely worth the time and expense to make the trip the first time just to see it all happen and meet new people, but it is a long trip for me and don't know if I'd want to make it every year. One thing you can easily tell is that MVP's are definitely important to MS. Not just kinda important, they clearly have a special place within and across MS, and MS spends a lot of time and money to facilitate that. Imagine you've built a product and you can find 10 or a 100 people that really love your product, use it every day, and spend time showing how it could be better - wouldn't that be useful? Then institutionalize it so that every product has that kind of support, and you start to see why MS tries so hard to maintain the MVP program.
I'm been in tactical mode lately, building an advertising management system for one of our projects. Actually it's v2, the first one is working and doing what needs to be done (definitely minimalist), but as we started looking at wrapping up the major development we decided to add a few things that had been on the wish list - but with the caveat that the time and resources to make the change were time boxed.
I tend to look at all development work from a data perspective. A lot of that comes from being a DBA of course, but also from being a business owner. I want to be able to answer the questions that matter to me, and in some cases I may not be asking the questions a developer would expect. That DBA experience also leads me toward normalized designs and in truth designs that often more complex than needed. Of course the hard part is knowing that you've exceeded the complexity threshold. The opposite end of the spectrum is the agile design approach which embraces YAGNI (you ain't gonna need it) and focuses on building for today.
Time boxing (or cost boxing) is an easy way to answer the question about too complex, though it separates the zealots from the pragmatics pretty quickly. Once you know the resource constraints, you then build a solution that you know you can get done. Survivorman is an example of this - I remember an episode where he had the choice of building a fire to feel safe, or building a platform so he could sleep off the ground and avoid most of the insects and worse. He could not get both done, he had to pick.
Back to the minimalist design. I had 2 weeks to redesign and fully implement the tables, workflow, invoice generation, and to connect to to a site was already up and running, with the final UI construction to support the design set aside as a later task. Two weeks is not long! As we worked on the design we had to make some hard choices, and the trick - to call it something - is to have a good feel for what is important and what's not, and the harder trick is to know what you can scrimp on now and change later without it causing major reconstruction.
As DBA's we should be masters of abstraction and refactoring. Between synonyms, views, computed columns, and triggers we have ways to put things in one place and make them appear differently or in a different place as needed. Only with a good understanding of the options and the final (someday) goal can you do good minimalist design, and there is definitely a subtle difference between good and not good.
You aren't always going to need it. Think about spending effort on the things that matter and setting yourself up to make changes later that will be easy refactorings.
A mildly humorous title, spawned by a link my friend Jon sent me about calculating Pi using a batch file. Technically interesting, if less than useful. Made me curious what had been done with Pi in SQL, so went looking:
Interesting, but not incredibly useful. The problem solver portion of me can appreciate tackling something like this for the fun of it, but the more pragmatic portion of me says I should spend time on more useful things.
Which isn't to say that I've never spent time on stupid code. At various points I've had code to "net send" a sentence to another machine one word at a time (annoying to be on the other end), an exe that just emailed a mailing list to tell them that the coffee was ready, and perhaps most strange, code to convert an integer to a 12 character base 36 representation (we had an application that used them for primary keys).
Apress sent me a free review copy, so I took a quick look through it at lunch. I've used full text a few times and know the basics, but I'm far from being a power user. The first chapter seemed kind of dense, more about the internal process than I cared about, but the rest of the book looked good. From basic set up to administration to really good stuff like building a Google-ish interface to FTS, all looked good and reasonably easy to understand.
Learned a few things (and probably a lot more that I would learn if I gave it the time it deserved):
I think it's worth having if you're doing full text stuff.
Disclaimer: I know Michael from SQLServerCentral.com and the PASS Summit, so I'm mildly biased.
