SQL Server
SQL Server
Wow. I didn’t see this coming, but Sun Microsystems has acquired MySQL. That blog post pretty much says it, about trying to figure out what this means.
I think overall it is a good thing, particularly since the open source version of MySQL is more likely to flourish under Sun than other potential buyers, notably Microsoft. But I wonder what it means for enterprise users?
Gut reaction: seems like a good fit. Best of luck to both companies, their employees, and users!
Kudos to Patricia Baer for tipping me off before I heard about it from the regular sources!
The September 2007 update for SQL Server 2005’s Books Online (BOL) is finally available for download. The update has been available online since, well, September but this time it took the UE group forever to make the download available. Because of “technical issues,” whatever they might be.
Anyway, go grab it*. An updated BOL is one of your more valuable SQL Server resources.
* No guarantees the link will work forever, or even for very long. But Google always knows.
Update: BOL is now part of Microsof Update! How long has that been? Forever, and I just noticed it? Either way, cool!
Microsoft has just announced that PDC (Professional Developers Conference) will be back 27–30 October 2008 in Los Angeles. That’s good.
But it will be interesting to see how they uniquely position PDC versus TechEd*, now that TechEd has been separated into separate developer and IT weeks in Orlando.
* Interesting link for TechEd: http://www.microsoft.com/events/teched2007/default.mspx. The link says 2007, but at the moment it it is about TechEd 2008.
I attended a lot of sessions at DevTeach in Vancouver last week. It’s one of my favorite conferences because it is relatively small and therefore intimate, yet it attracts some amazing speakers, including some fine speakers from Microsoft.
Unlike some conferences, all speakers deliver their sessions from their own laptops instead of from a desktop machine provided by the conference. Not surprisingly, most speakers are running Vista since it is Microsoft’s latest and greatest client OS.
But it struck me at how many problems speakers were having with Vista. I’m not sure I attended a single session where some problem didn’t arise (but maybe I’ve blanked the good experiences out of my memory). One speaker couldn’t get the projector to work with Vista without technical help from the A/V support staff, then the magnifier was so small as to be worthless, making it hard to see important stuff. Many machines were slowed to a crawl, presumably from the combined performance-sucking power from PowerPoint and Vista’s Aero interface, not to mention Visual Studio or SQL Server Management Studio.
The carnage was impressive. It seems that Vista pushes the limits of laptop technology. Apparently the OS demands not only a new laptop but a top of the line version of the laptop with all the processor and memory you can throw at it.
And me? No problems at all. I had one issue with Diskkeeper kicking in during a session but I shut that off and problem was over. PowerPoint, Visual Studio, and whatever else I had to run, no problem.
I’m running Windows XP. I think I’ll stick with it for a while longer, maybe even after I replace my three-year-old laptop. Sorry, Microsoft!
DevTeach is one of the very fun conferences I speak at regularly, based in Canada. It is small, intimate, attracks some great speakers, and has support from Microsoft. There has long been plenty of good reasons to attend, as indicated by its motto, training you just can’t get any other way.
But now there is another reason: Beer! I’m blown away, but during the afternoon break they set out beer. And none of the 3% stuff either, this is 5%.
So it should be an interesting afternoon of sessions. Thank heavens I did my only session today right after lunch and before the alcohol flowed! Beer and SQL Server encryption just don’t mix.
Now, if I only liked beer…. Oh well.
At SQL Server Magazine Connections at DevConnections in Las Vegas last week, I had the opportunity to geek out on SQL Server security for a full day at the end of the conference. Attendance was great and I hope that everyone left with a solid foundation of understanding of SQL Server security.
One question that came up was about the Surface Area Configuration utility that ships with SQL Server 2005. The question was whether SAC is cluster-aware. At the time I was showing the command-line version of the tool, but it really applies to both that and the GUI tool. Showing my developer bias, I didn’t know the answer but promised that I’d find out.
The answer is that yes, it is cluster aware, at least to the extent that you can configure remote computers with it. In the GUI version of the tool, use the Change Computer link on the opening page:
In the command line version of the tool, use the -S switch to specify the name of the remote computer. If you leave off this switch, SAC connects to the local computer.
If anyone has specific experiences using SAC in a cluster environment, I’d love to hear about them!
By the way, if you’re not familiar with the command line version, it is a handy way to export and import settings, making it relatively painless to configure multiple SQL Server instances the same way. See the sac Utility entry in BOL for more information.
At SQL PASS this year, I did a session about Common Table Expressions in SQL Server 2005. I had endeavored to come up with an extremely simple recursive CTE, but wasn’t creative enough at the time.
But I revisited the problem in preparation for an updated CTE session at DevConnections this week, and came up with this:
WITH
SimpleCTE
(Number
) AS
(
SELECT 1
UNION ALL
SELECT * FROM SimpleCTE
WHERE 0
=1
)
SELECT * FROM SimpleCTE
No guarantees that I couldn’t get it even simpler, but this is probably what I’ll show this week. There may be another option for the SELECT * part of the CTE, but I’m not seeing it right now.
BUT! Obviously, this is the result of a thought experiment, and is not practical for anything else I can think of. Don’t take this as any kind of recommended practice!’
I just found out that I’ll be doing a full day, post-conference session on SQL Server 2005 security at SQL Server Magazine Connections at DevConnections in Las Vegas this November.
I’m excited beyond words! I’ve wanted to do this for a long time, and we’re going to geek out on keeping data safe from villans.
Here’s the draft description:
There are few corporate assets as valuable in the information age as data. Enterprises spend billions to collect and generate it, slice and dice it in every conceivable way to mine marketplace intelligence from it, and replicate and back it up using elaborate, redundant schemes. Yet it is all too common to slack on security. Sure, SQL Server 2005 is designed to be "secure by default," but once you add databases and start letting users and their applications access the server you have already poked holes in the security. SQL Server comes with plenty of features that let you secure data, but it can be hard to get a handle on the right ones to use in your environment. During this day of security, we'll explore myriad security features in SQL Server 2005, including granular permissions and how to design an effective authorization system, owners and schemas, and how they can help secure a database, the security issues and dangers with running SQL-CLR code, how to run T-SQL code in different security contexts, the comprehensive encryption features that can protect data, creating and enforcing password policies, how SQL Server protects catalog views and secures metadata, protecting against SQL injection attacks on the server, and more. You'll see lots of code and get lots of practical ideas for how to secure your database. Prerequisites: You'll need to have a good understanding of the basic database features and functions of SQL Server for this workshop, and it helps to have butt heads with SQL Server a time or two trying to get something to work without completely disabling security.
I’ll post more later as I develop the outline and contents.
I’m in Montreal this week for DevTeach, a user group-oriented developer and SQL conference that is one of my two favorite conferences. Montreal is a great city (particularly for the stomach!) and they do a great job with the conference.
Yesterday were the pre-cons, covering .NET 3.0, VB.NET, and SQL. I sat in on part of Kevin McNeish’s .NET 3.0 session and learned lots of great information about Windows Workflow. It’s great how that technology has matured into something amazingly powerful.
The Tuesday morning keynote has started. Jean-Rene has announced that DevTeach is hitting the road. The next stop will be Vancouver 26-30 November, and will include both DevTeach and SQLTeach. I hope to be there; that should be a great time to be in Vancouver!
The main keynote event is Pablo Casto, Microsoft’s ADO.NET wunderkind. Julie Lerman’s excitement about the keynote was justified (causing her to miss the Vermont .NET User Group meeting for the first time ever to get here in time). Pablo talked about the current thinking at Microsoft about the Microsoft Data Platform. ON the surface, this is the same sort of thing they’ve been talking about since the introduction of ADO, but baby, it’s come a long way since then!
The Microsoft Data Platform:
- Provides a uniform way of describing business data
- Gives each app and appropriate view of the data
- Enables the creation of services, from reporting to synchronization to integration
- Applications and services use the same data model
A key part of the platform is the Entity Framework and the Entity Data Model, which is coming of age. I’m blown away by the possibilities of what this all means for application infrastructure. But what I didn’t know until today—apparently it was announced a couple of weeks ago—is that all this will not ship with Orcas. Instead pieces and parts will RTM at various times in 2008. That sucks, but so be it.
George Dougherty emailed me with an interesting problem that he and Tony
Sengphirom discovered.
“A cursory google search didn't find anything, but we came across a pretty
big gotcha this morning with our production database server. We're running SQL
2000 on Windows 2003 R2 with SP2. I haven't tested against our test 2005 box but
it may affect 2005 as well. With SP2 applied if you install SNMP on 2k3, the
install restarts MSDTC, SQL Agent and the SQL server itself without asking for
any user input. Downgrade or do this on a box without SP2 and SNMP goes on
without any restarts. Needless to say it was a big surprise this morning when
one of our network admins installed on our production box as the company got in
and started their day.”
I haven’t verified their findings (too busy getting ready for
DevConnections!), but wanted to get their report out.
Google has launched a Code Search tool, designed to make it easier to find snippets of code. The service looks interesting. I’m working on a project with the installation APIs in .NET 2.0, and tried to find an example of using the ManagedInstallerClass (despite the fact that the documentation says that it’s for internal use only). This was my search:
lang:"c#" ManagedInstallerClass
If found two hits, one of which showed me an interesting way to use it.
I tried to find the same thing in VB, but initially couldn’t find the magic way to indicate the language. lang:VB? lang:“VB.NET”? lang:“Visual Basic”? Another variation? Fortunately, the Advanced Search link provides a combo box with a complete list of languages. “Basic” it is. No samples for ManagedInstallerClass though, alas.
There’s a good spread of languages, certainly all the ones I’m interested in, including SQL.
There are lots of flexible options for searching, including regex. Options include searching by language as I did above, specific files or directories, specific packages, and code license types.
The only thing I don’t like so far is that there doesn’t seem to be an easy way to get context information of the page where the code is coming from. Most of the code I looked at was in some kind of archive file, with no link to the Web page that might give information about the code. Often you can figure it out from the link, but not always.
Give it a try. It seems to be a nice, targeged way to find useful code. The FAQ ‘splains it all. It’s still a Google Labs project, so I assume that means it will evolve quickly.
It has been a great summer that I’ve mostly stayed home, but it’s time to hit the road. Next week I’ll be visiting three INETA user groups to talk about security. At the Inland Empire .NET User’s Group in southern California and Little Rock .NET User Group in Arkansas I’ll be talking about SQL Server 2005 security from a developer’s perspective. At the Dallas .NET User Group in Texas I’ll be talking about least privilege development and showing off Aaron Margosis’ LUA Buglight and other tools. In Dallas, Ron Jacobs from Microsoft’s Patterns & Practices Group will be speaking as well during an extended meeting.
If you’ve in any of these areas those evenings, come join us! Details are on the groups’ Web sites.
It was a sad occasion, but Melinda and Bill Gates were here in Fairbanks, Alaska today. I couldn’t mention it before, but they came up for the memorial service this afternoon for Susan Butcher at the Davis Concert Hall at the University of Alaska Fairbanks.
The Gates have been friends with Susan and David for years, ever since the Gates came up for a dog mushing trip as part of their honeymoon. I was really impressed with Melinda, who gave one of several short reflections about her friendship with Susan and David. She told some nice, warm stories, and is an excellent speaker. Melinda apparently spent a lot of time with Susan when she was being treated at a hospital in Seattle, apparently more than anyone except for Susan’s husband David.
It was an impressive memorial, with a variety of dignitaries, including Alaskan politicians, George Lucas (another friend of the family), and many from the Fairbanks and mushing communities. It is amazing how many lives Susan touched, right through her losing battle with leukemia. It’s nice that people came from so far to pay their respects.
Executive Summary: Despite hours of trying, I have had no success installing SQL Server 2005 SP1. Herein lies my sordid tale, and my conclusion that I’ll work on this no longer. Perhaps this post can save someone time trying all the things I did.
I have been trying off and on for about three weeks to install SP1 for SQL Server 2005. I think I wisely decided not to install it when it came out, because that was a couple of days before I headed to DevConnections in Nice, France earlier this month to do five SQL Server 2005 sessions. It’s never good to change configurations so soon before standing in front of people to show them stuff. I downloaded it though, and after I finished my last session I fired up the installation program.
Alas, it was not to install. For the record, I have attempted to install it on an IBM Thinkpad T40 laptop running the Developer’s Edition of SQL Server 2005, lots of memory, running as mere user. The default instance is SQL Server 2000 and I’m updating a named instance of SQL Server 2005.
I will say that I really like the design of the installation package. It makes the options easy, provides plenty of status information (maybe even a bit too much), and let’s you know the outcome. But with all that, I still don’t have it installed after many hours of trying and many attempts.
The first indication of a problem is when it tries to update Database Services. This dialog popped up:
The first couple of times this happened I went and shut down all the services and hit Try Again. That ended up making no difference, so all the subsequent times I just hit Continue, willing to do a reboot as a small cost of getting this thing installed.
Setup then continued on its merry way. A few moments later, though, I was greeted by this unhappy dialog:
Clicking the link did little to enlighten me:
Clicking the View link just lists a gajillion log files. I helpfully click Send Error Report so that Microsoft can share my pain, and installation continues:
Eventually, what can be installed is, and I get the Installation Complete dialog that betrays the fact that installation is very incomplete:
Sadness. I Googled the error message shown at the bottom of the dialog, “Unable to install Windows Installer MSP file,” which leads me to a KB article, “BUG: Error message when you try to install SQL Server 2005 Service Pack 1: "Unable to install Windows Installer MSP file." Yowsa! Microsoft knows about this and has a fix!
Alas. Despite carefully following the directions—heeding, of course, the mandatory, dire warnings about messing with the registry—this does not change the outcome one whit.
So at this point I have updated all but the Database Services and have thrown hours of time that might otherwise been billable down the rabbit hole, never to be seen again. I will make nor further attempt until Microsoft re-releases this service pack. I’m not going to touch a server with SP1 until I’m a lot more confident in the package.
Security Note: I realize that the world isn’t yet attuned to running as a least privileged user, and I also realize that this is server software which has special security needs. So I tried it with RunAs, MakeMeAdmin, and finally—gasp!—logged in using a real Administrator account. The installation wouldn’t run with RunAs, and otherwise there was no difference in the outcome for any of the options. So this isn’t a least privileged user account issue.
Wow. Here’s a report that Microsoft is dropping PowerPoint from Office.
What a great way to make the world a better place!
I’m taking the year off from speaking at most conferences, but I just couldn’t pass up the chance to head to the French Riviera and SQL Connections there this April. As usual, the all conferences—SQL, ASP.NET, Visual Studio, and Exchange Connections—have an incredible group of speakers.
I’ll be doing these sessions at SQL Connections, all covering SQL Server 2005:
Defence in Depth: Encrypting Data
Despite all of the security features built into past versions of SQL Server, sometimes they just aren't enough. Far too often creative attackers are able to get around normal protections even when the database is isolated from the Internet. SQL Server 2005 now natively supports data encryption on the server using a variety of algorithms and techniques. Best of all, it can manage the keys for you, removing the biggest problem and hassle of using encryption. This session will explore the encryption options in SQL Server, how they protect against specific types of attacks, encryption support in objects and T-SQL, common pitfalls that cause encryption to only provide an illusion of security, and whether encryption is the right tool to use.
Querying and Updating the XML Data Type
With XML as a native data type in SQL Server 2005, you are now faced with two query engines and syntaxes, good old SQL and the new kid, XQuery. It can be daunting to create queries that mix the two engines and it can be hazardous to your performance. This session will explore the XML-specific T-SQL functions you can use with the XML type, how to mix and match the syntaxes effectively, and how to build common queries that extract and modify data at a minimum of two levels: relational data and embedded XML.
Security in the CLR World Inside SQL Server
One of the major benefits of writing .NET code to run in the Common Language Runtime in any environment is its code access security (CAS). CAS provides a code-based - rather than user-based - authorization scheme to prevent various kinds of luring and other code attacks. But how does that security scheme coexist with SQL Server 2005's own, greatly enhanced security features? By default your .NET code is reasonably secure, but it is all too easy for the two security schemes to butt heads and cause you grief. During this session we'll briefly look at the concept behind CAS, then explore how to make it work for you instead of against you as you take advantage of these advanced programming features in SQL Server.
SQL Server 2005 Security for Developers
An increasingly hostile, connected world requires bold new initiatives to protect your data stores. SQL Server 2005 is the first major release of SQL Server since Microsoft began its Trustworthy Computing. As a server product and the foundation for business critical applications, SQL Server has a whole new security infrastructure that will affect how you develop applications for it and maintain it. This session will look at what's new, what's easier, what's harder, and why you'll need to have a better understanding of these features than ever before. Developers can no longer afford to be ignorant of security, and this new version of SQL Server changes how you handle threats in fundamental ways.
XML Schemas with the XML Data Type
XML Schema has become the dominate data schema description format, implemented throughout .NET, deeply integrated with SQL Server, and rapidly becoming the standard way to describe the structure of XML data. This session will cover everything you need to know about XML Schema and its use in SQL Server 2005 to provide typed XML data, and why you should. You'll learn the basics of XML Schema, ways to use it effectively in data apps--why you'd want to, when you have to--and how to go beyond the basics. Learn how to rely on the tools but know how to refine schemas to the benefit of your apps.
See you there!
I always forget the details of how to do this and have to research it again. So here for both your and my benefit is how you can use the SQL Server COALESCE function to retrieve text from multiple records and concatenate it together.
The need I had today was to pass a string like '7,2,8' to a stored procedure and get back a string like this: ‘Alaska Natives, Art Education, Art History’. You can probably guess that the numbers are primary keys into a lookup table with areas of interest for grant funding. The numbers come from reading the value of a collection of checkboxes on an ASP Web form (yes, ASP classic, sigh).
I could have done three (or however many) calls to the database, but that is bad practice. So I used the COALESCE function like this (this is my test code before I turned it into a stored procedure:
DECLARE
@criteria NVARCHAR(1000)
DECLARE @aoilist NVARCHAR(100)
SET @aoilist = '7,2,8' -- test data; string can be any number of indices
SET
@aoilist = ',' + @aoilist + ',' -- put delimiters at start and end
SELECT
@criteria = COALESCE(@criteria + ', ' + aoi.ncv_DecAreaOfIntr_Desc, aoi.ncv_DecAreaOfIntr_Desc)
FROM tblFunder_DecAreasOfInterest_dfai aoi
WHERE CHARINDEX(',' + CAST(int_Recordid AS nvarchar) + ',', @aoilist) > 0
PRINT
@criteria
According to BOL, the COALESCE function “Returns the first nonnull expression among its arguments.” That description doesn’t begin to hint at this trick, though!
Most commonly the WHERE clause uses an EXISTS clause with a subquery to return data from another table. But in this case another table wasn’t involved, and I just used the @aoilist value to provide the test.
I originally learned of this trick a few years back in the ASP.NET forums, on which I’m a moderator. It has simplified my coding a lot since then!
I received an email from James Johnson, leader of the Inland Empire .NET Users Group, centered in Redlands, California but serving the entire Inland Empire (as one would surmise from the name, heh). I’ve had the pleasure of visiting the group a couple of times because my folks used to live about 30 miles east of there. And last summer I visited as an INETA speaker.
When I was there it was a small group of fun people who are passionate about development. I love that kind of energy!
James writes that the group, in his words, has been reborn, relaunched, re-opened, re-whatevered. When they lost their meeting room, they decided it was time to invigorate the group.
And boy, did they! Last Tuesday was their INETA Sponsored Community Launch Event and grand Re-Opening, with 73 attendees and a ton of goodies to raffle off. A four hour event that was full of energy and enthusiasm. One of the best things they are doing, besides the new Web site and setting up online forums, is to set up study groups. I so wish that I had opportunities like that here in Fairbanks! I guess I’ll just have to get down there again and join in!
Well done, James! Best of fortune with the new group!
I’m pleased to announce that my first video training CD package is available, Exploring SQL Server 2005 from AppDev (Application Developers Training Company).
Despite years of doing training and speaking at conferences, this was the first time that I’ve shot videos. It was quite a different experience to do a lively presentation while speaking only to a microphone or to a camera for the head shots. The days spend recording were pretty stressful