I've finally gotten around to SQL 2008 RC0 after having been wrapped up in quite a few things -- first thing I noticed was an odd behavior in the designer I had not seen in prior versions.
After creating a table, I decided to add a new column and change the order of two existing columns and received this odd message:
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
Luckily I was not the first to notice, and Brian Knight has provided some nice screenshots of how to resolve the error "Saving table changes is not permitted".
Tags: sql 2008,
sms
Categories: SQL Server
Right after I published my post Analysis Services and Storage Options, I noticed one of my bookmarks that might be interesting to those of you out there looking at more frequently updated Analysis Services scenarios.
It is a whitepaper by Paul Sanders published in April of 2005 titled Real-time Business Intelligence Using Analysis Services.
For anyone considering low latency BI, particularly pay attention to proactive caching in Analysis Services 2005 and 2008. It is a partition specific feature and can really come in handy given a well laid out partition scheme for your cube.
Tags: analysis services,
performance
Categories: Business Intelligence
I received an e-mail from a friend at one of our clients asking about storage options for their Analysis Services 2005 cube and what was the best for their scenario. Apparently he had just been to a presentation and he walked away confused over the argument for/against the ROLAP storage method and where it should be used.
His confusion lay in the fact that the speaker kept referring to the advantages of ROLAP for real-time data updates while preserving aggregations. The presentation had gone on in length about caching models and user response time in a manufacturing plant scenario; plus a few other inconsistencies in the speaker's presentation, my friend walked away befuddled over this issue.
I first pointed him to Microsoft's definitions for SQL 2005 and 2008 on the storage settings in order for him to have a solid definition reference– they are not different, but best to keep both bookmarks in case anything does change for 2008, and then we spoke over why he was looking at this type of implementation – and we walked right into one of my major pet peeves in presentations.
The speaker for this incident was giving his presentation to a very specific audience: my friend, his colleagues and a few business analysts, who all work at the same company – a bank. The speaker had gone on about the advantages of ROLAP using both the manufacturing plant scenario and then retail sales. While you can make a case for the retail sales portion of the demo, he had already lost a lot of the audience by starting with manufacturing, relating it to a distribution chain for the retail outlets and back again.
I asked my friend why the interest in real time reporting and got the answer I have frequently heard: "Our exe read this article and thought we needed to be more dynamic, so asked us to look into this." Scott's writing a new Dilbert on this one right now. After we both had a chuckle, we walked through his scenario – a basic fact emerged that my friend would take back to his manager: they have no real time feeds for their reports currently, nor will they in the near/far future.
It made an easy argument back to the execs on why they could not implement– but also forced a decision point to them how they wanted to manage their business process. Without going into too much detail, one of the central roadblocks in my friend's scenario had to do with critical information for their analysis that they received from their 3rd party vendors and service providers. Without working with these two groups to resolve more timely data, they would not be able to approach even near real time for a while.
Now, at least, my friend is working on answering the question of: 'is this even a necessary situation, or can they achieve the needed analysis for the business with the current state?'
As you may have noticed, I have neither disdained ROLAP, nor sung its praises. I prefer to look at this in terms of situations and what is the appropriate solution for a given opportunity. Microsoft put this storage option in for a good reason in Analysis Services 2005 and has preserved it in 2008 as they obviously see a need for it to be there.
As with any implementation though, good planning against a clearly laid out set of goals is always the best way to start.
Tags: analysis services,
storage subsystem
Categories: Business Intelligence
I experienced firsthand this past week that some backup strategies that are supposed to be simple are not always so. D'oh!
I volunteer to be a guinea pig for our I/T guys, mostly because I like to play with the cool new toys; the toy this time: Server 2008 x64 on my laptop. I have been itching to play with Hyper V for multiple reasons, but mostly because I want to have 64 bit development images for SQL 2005 and 2008 (Virtualization team blog).
Before imaging my machine from Vista x64 to Server 2008, I had taken a Norton Ghost full backup, converted that backup to a virtual disk, and also separately taken a backup of my files.
You would think reasonable safe right? A VHD, a Ghost full drive backup and Norton file backup – only problem with my grand scheme was due to my haste in moving to my new OS: I had backed all up to the same drive!
Luckily it was not a full drive failure (and as one buddy pointed out with the WD Passport drives, he frequently re-formats it to avoid corruption), but it did keep me from doing a nice and easy full restore of my image to Hyper V. Countless hours later……………….
I am back up and loving running my "old" Vista 64 machine as a Hyper V machine -- definitely a lot faster than VM Workstation and loads faster than Virtual Server 2005.
Evaluation VHDs are available for download from Microsoft here. The second link on the page is for a 32-bit version of Server 2008 to run under Virtual Server 2005.
Tags: server 2008,
virtualization
Categories: General
.NET makes it incredibly simple to build a site, and for new programmers, it can sometimes provide an unwarranted warm feeling that they are coding to an enterprise standard just by implementing certain aspects of the Enterprise Library, or properly tier-ing their application.
However, all is not well with the web world, as most of well know; many, many sites are exposed to basic SQL injection attacks due to failure in following some basic development principles.
For anyone wanting to read the basics, the Security Development Lifecycle team blog has posted an introduction to software architecture that reduces your attack surface area. Highly recommended for anyone new to web programming and using a database to serve their website; also a good refresher for old hands as well.
In addition, Microsoft has posted a source code analyzer for anyone wanting to run their web application through the ringer. Incredibly useful and highly recommended for anyone needing to learn more about web application security.
Tags: security
Categories: Security |
SQL Server
By no means the complete list of SQL Blogs out there --however if you are looking for additional resources for SQL Server, this page inside the SQL Server Developer Center is the place to go.
One nice bit is that all of the MVP’s for SQL Server thathave blogs are listed here as well as the Microsoft Team blogs (looks like I’ll need to modify my Live.comsearch macro to search all of this content!).
Tags: blogs,
sql server
Categories: SQL Server
I love tools that make my job easier; I particularly love when those tools are free.
One fantastic example is ClearTrace. Though it is simple in design, its purpose and use are clear: analyzing trace output for SQL Server 2005 performance issues. I have used this application numerous times and in many different scenarios.
There have been two scenarios in particular where ClearTrace came to the stage in spades. The first I actually posted about a few months back: a colleague and I were troubleshooting SharePoint 2007 performance issues. I took a trace of the database, ran ClearTrace against it, and we came up with a solid starting point for our investigation into the inner workings of MOSS…… (enough of that here, I'm not a MOSS expert, so I won't keep going!).
My second, and more recent example, was on a project involving a custom web application. The database hardware and setup our client had was not optimal for the solution we were rolling out, but it is what we had to work with, and what they were willing to install, so we made the best of it.
We were looking to squeeze out as much performance from these machines as possible; the client provided us with some traces from production database where the first wave of new users had hit the machine and loaded them into the ClearTrace application.
I will make a note here that the application architecture and database design is damn solid – we had a good team working on this and the end result was a very well performing application that more than met the business needs.
Still, some of the results were surprising to the development team – in this case mostly due to users and their old habits, but the big bonus that came out of this was two-fold: (1) we tuned procedures and indexes and optimized the disk subsystem based on the results and (2) found out usage patterns in the data layer that had not been predicted.
ClearTrace is a performance related tool. One important note is that this tool does not work with SQL Express as this edition does not install the SMO library (just pick up SQL Developer Edition, cheap and it has all the features!).
Tags: tools,
performance,
auditing
Categories: SQL Server |
Tools
With prices for solid state media dropping seemingly month over month, it will not be long before we start seeing solid state media being used regular SQL Server implementations and not just edge cases or leading adopters.
If you subscribe to SQL Server Magazine, then you can access Levon Peters article in the June 2008 publication.
Texas Memory Systems is a professional provider of solid state storage systems; they have a white paper from September 2007 that is informative and posts solid measurements on the server subsystem in order to explain performance gains -- obviously take with a grain of salt given that they are a vendor, however it is relatively objective.
IBM posted a gloss on reliability and points of failure, but not much more in it other than some high level numbers.
The laptops we use here at Catapult all have EC card slots -- I will pick up a solid state drive to test a database on and post the numbers once I have some test cases built.
If nothing else, running some of my virtual machines off of this drive will definitely speed the machine up, however I expect SQL's performance to have an amazing increase.
Tags: storage subsystem,
hardware
Categories: SQL Server