Posts Tagged ‘sql’

Enable Backup Compression as Default in SQL Server 2008 R2

Of all the features added to SQL Server over the past few years I was particularly pleased with the addition of backup compression, there have been third-party tools around for a while that have done this (such as Quest’s excellent Litespeed) but third-party tools always add an extra layer of admin and maintenance that as a DBA I could do without. In SQL Server 2008 it was made available for Enterprise licences only but with the launch of SQL Server 2008 R2 backup compression is now available with a Standard license, though in both cases the feature is turned off as a default – so how do you use it?

Well, if you’re making a backup from Management Studio you can specify manually on the Options page of the “Back Up Database” page (right-click on DB >> Tasks >> Backup), like so…

Alternatively, if you’re one of the “Real DBAs don’t use GUIs” crowd you can add the “WITH COMPRESSION” option to the BACKUP command..

BACKUP DATABASE reportserver TO disk = ‘E:\ReportServer.bak’
WITH COMPRESSION

Or, conversely you can specify not to use compression…

BACKUP DATABASE reportserver TO disk = ‘E:\ReportServer_no_compression.bak’
WITH NO_COMPRESSION

But what if you want to use compression but don’t want to have to think about it every time – or more importantly, don’t want your users to have to think about it? To enable backup compression as a server-wide default you will need to use sp_configure…

USE MASTER;
GO
EXEC sp_configure ‘backup compression default’,’1′;
RECONFIGURE WITH OVERRIDE;

Now all you need to do is tell your storage admin that he/she owes you a drink!

BusObj + MySQL – SQLBindParameter State 07001

I recently encountered a strange problem in Business Objects whilst trying to create a derived table against a MySQL database and whilst the query was a complex one it executed without fault in the MySQL Query Browser so I knew that wasn’t the probem.  The error message I encountered was “SQLBindParameter not used for all parameters”…

I tried the same query as a Freehand SQL query in DeskI and received the same error, after banging my head against the problem for about 15 minutes I decided to take my favourite solution – I went for lunch!  On return the solution seemed blindingly obvious, at the very top of my query I’d included a comment but in the comment I’d included a question mark – as soon as I removed the “?” the query ran without a hitch.  I’m not quite sure of the reason behind this but I suspect that one of the Business Objects, ODBC or MySQL layers treats the “?” as a reserved character to indicate a parameterised query – if anyone has the answer i’d be interested to know.

My Favourite SQL Server Features

SQL Server MVP Ken Simmons is offering the chance to win one of three MSDN subscriptions on his blog, I thought I’d join in by writing a post describing my favourite features of SQL Server.

The ‘big ticket’ item for me is SQL Server Integration Services. For those of you stumbling across this post from non-SQL backgrounds SSIS is Microsoft’s ETL tool and has been bundled with the database engine since SQL Server 2005. Essentially SSIS allows you to extract data from (or deliver data to) a range of ODBC, OLE DB, ADO.NET or flat file data sources including Oracle, DB2, MySQL, Excel, CSV files and of course – Microsoft SQL Server. Along the way you can make use of a range of tasks and components that allow you to transform the data and perform operations such as looping, FTP, Email, Lookup Values, Data Mining, Aggregation, Process Dimensions and many many more .  There are alternatives out there but many are prohibitively expensive for the small-medium enterprise (e.g. Informatica, BODI) – that SSIS comes bundled with SQL Server still astonishes me to this day.

As for my favourite ‘little’ features I’d have include:

Linked Servers, a few people give it a bad rap since they can be misused but I’ve made great use of Linked Servers to simply data assurance processes and join data in queries running across both MySQL and Sybase data sources in a single statement.

Copy and Paste Coloured SQL, I’m not sure if it’s just because I’m used to Management Studio but I often struggle to read SQL if it’s not coloured-in. Colouring SQL improves readability and since SQL Server 2005 I’ve enjoyed the ability to copy and paste directly from Management Studio into emails or word documents and retain the coloured keywords, it makes documentation much more interesting.

Common Table Expressions, it took me a while to get into the swing of using CTEs and then for a while I probably over-used them before settling down. They’re often a great way to make statements with nested subqueries much more readable and much more elegant in design but whenever I go back to SQL Server 2000 or MySQL I really miss CTEs.

SQL Server Express, it might be a bit of a cheat to include an edition as a feature but the fact that the core SQL Server database engine is available for free is brilliant and allows a lot more people to try out SQL Server and see the difference between a slick GUI like Management Studio Express vs the MySQL toolset.

Intellisense, I couldn’t resist choosing this as one of the more modern features I love about SQL Server. Once you’ve gotten used to it Intellisense makes trotting out T-SQL statements a breeze and you know what? It just feels cool, albeit in a really geeky sense of the term.

Microsoft’s UK Tech Days: SQL 2008 R2

I enjoy going to SQL Server community events, I usually find they provide a refreshing look at what other people are doing and provide inspiration and ideas of what I could be doing myself.  Vendor-run events are different so I attended Microsoft’s SQL Server 2008 R2 Tech Days event with mixed expectations, not sure if it was going to overly marketing-heavy or whether it really would be worth taking a day out of the office.

Thankfully I was in luck, Microsoft did a great job of treading the line between promotion and information and whilst the intro and the first 5-10 mins of each take were quite marketing oriented the majority of the content was realistic and provided honest demonstrations of the product.  Also throughout the talks presenters were offering to answer questions via SMS or via the Twitter hash-tag #uktechdays, this was a great touch and even though there wasn’t time to answer all of the questions it really added to the interactivity of the event.

First up was Power Pivot, as a product it looks to be immensely powerful and provides lightning fast analytical capabilities though I imagine it needs a decent amount of RAM and an up to date processor to achieve it – the most amazing part is that it’s a free add-in for Excel 2010!  Essentially PowerPivot allows you to extract up to a million records from a database and perform in-memory analysis with that set of data, including combining it with other data sets, combining it with data in your spreadsheet, performing calculations, making summaries, etc.  It’s well worth taking a look at the demos, PowerPivot is a massive leap forward in Excel’s capabilities but to me it seems like a step backwards in terms of the centralised BI ‘single version of the truth’ concept – allowing users to rip a million rows out of the Data Warehouse, mix them up with other data sources and then send them around via email or even publish them via SharePoint.  As it goes the Share Point integration was also pretty remarkable, allowing other users to use published reports not only for viewing but also as a data source on which to build new reports – pretty ground breaking stuff but I’d hate to be the guy debugging a report based on a report based on a report based on… (you get where I’m going).  Overall I’d give PowerPivot a 5* rating for innovation but it seems that Microsoft is using a common tactic from Formula One – trying to get ahead of the competition by taking a contrary strategy, but will it turn out like Jenson Button in Shanghai (he won) or like Lewis Hamilton in Australia (he didn’t)?

After a relatively dry talk on virtualisation and Hyper-V Live Migration (impressive stuff but I’ve seen it before) the next talk was about Report Builder 3 and having never been a user of Reporting Services I thought I was just going to sit through it and twiddle my thumbs – I was wrong.  Having been knocking about in the BI world for about 8 years or so I can really say that this release of Report Builder really cements Microsoft’s position in Business Intelligence.  It’s still not very slick from a usability standpoint but the visualisations they’ve added are stunning and having been a long-time user of Business Objects the talk actually did make me think “how hard would it be to switch?” – since I have a mature installation the answer is very hard but it still made me think.  The most impressive visual elements were the Spark Lines, Data Bars and Indicators but the maps were also pretty good especially given that you can use ESRI shape files.

The next talk was “Maximising your existing hardware CPU, memory and disk” by Ramesh Meyyappan, I’ve seen Ramesh before at SQLBits and he’s always very good, very detailed and straight to the point.  It was a great talk, taking place mainly in Management Studio rather than PowerPoint and if you get the chance to see one of Ramesh’s talkes in the future you should definitely go (but have a cup of coffee first).  Following Ramesh’s rollercoaster of a talk was much more relaxing run-through of Microsoft’s ‘database in the cloud’ offering SQL Azure, a product I find extremely interesting but don’t have an immediate use for though I expect in time as the feature-set converges with SQL Server I will be changing my mind.  Next up was StreamInsight, R2′s Complex Event Processing (CEP) solution for analysing large data streams (10k rows/sec+) on the fly without touching the relational engine – it looks interesting but I don’t have those sorts of requirements at the moment so I don’t have much of a reaction.  The day was rounded off by a presentation by Andrew Fryer about Master Data Services, a difficult topic to present in a jazzy way but it looks very interesting and if it will integrate with the spaghetti-junction of systems floating around in most organisations it could do a lot to help us keep our data warehouses in line with corporate naming conventions, it sounds like a lot of fuss over a little issue but if you’ve ever actually tried to solve the problem yourself in a company with more than a couple of source systems you’ll understand how hard it can be.

All in all a good day, I’ll give a shout out to the staff at Jumbucks in Shepherd’s Bush where I had breakfast and bought a bagful of Australian confectionary and to the Vegetarian Chinese buffet over the road for providing me with much needed sustenance.

Learn Microsoft SQL Server for Free

I had a conversation with a colleague recently about how to start teaching yourself SQL Server  skills outside of the workplace, he’d read an article on SQL Server Central that recommended buying the Developer Edition of Microsoft SQL Server which I think is a great idea but if you’re really just starting out with SQL Server why not do it for free?

Luckily, there are a number of ways that Microsoft helps us to do this…

Microsoft SQL Server Express 2008 is a cut-down (but not time-limited) version of the full SQL Server 2008 product restricted to 1 CPU, 1GB RAM and one single 4GB database.  The express edition contains the core database engine as well as the excellent Management Studio (IDE) and if you download the “Runtime with Advanced Services” version you also get Reporting Services, enabling you to build, deploy and run a small-scale BI solution.  Please note that the express edition does not come with SQL Server Agent (scheduling) or Integration Services (ETL) but you do get the Import/Export wizard to help move data around – check out the full comparison for more detail.

Alternatively, if you want to run with the big dogs you could try SQL Server 2008 Evaluation Edition which contains all the features of the Enterprise Edition but has a time limit of 180 days.

If you’re just starting out and want to get the hang of basic data manipulation, writing queries, creating tables, etc. then you’re probably better off with the Express version.   It’s smaller and won’t use up as much space/RAM on your PC and you won’t have to worry about the time running out – there’s also a chance that you’d be overwhelmed by all of the other features and products that are bundled with the Evaluation version and in the beginning it’s always better to keep things simple. If you’re already an intermediate-advanced user of SQL Server or another database platform, or you already know that you’re interested in SSIS or Analysis Services then you might as well get hold of the evaluation version but don’t forget that it’s time limited!

Useful SQL Colouring Tool

I guess it depends on your background but if you’re anything like me you’ll prefer to have your SQL coloured and formatted, I began writing SQL in Microsoft SQL Server’s Query Analyser which colours comments green, keywords blue, aggregate functions pink and string values in red – and it’s something I (almost) can’t live without.

It’s become a fairly common scheme for viewing SQL and years of being used to colours mean that reading too much monochrome SQL drives me crazy so when I started this blog I was a concerned about how off-putting it would be if I posted reams of plain old SQL, luckily a bit of Googling led to me finding Instant SQL Formatter.

Instant SQL Formatter allows you to format and colour a SQL statement and then provides you with the HTML that you can paste right into your blog, there are also lots of options to tweak the formatting based on how you’d like to see the SQL laid out.  Here’s some example SQL I wrote using the tool…

– TESTING COLOURED SQL QUERY
SELECT Species,
COUNT(DISTINCT AnimalID) AS NumberOfAnimals
FROM tblAnimal
WHERE Species IN (‘Bonobo’,‘Vervet’,‘Chimpanzee’)
AND IsHealthy = 1
GROUP BY Species
HAVING COUNT (DISTINCT AnimalID) > 1