I drew the short straw at the 2008 Launch event last week in Birmingham; speak for one hour on upgrading SQL Server. I found this very difficult because on the one hand you can usually just do the upgrade, once you have done all the pre-requisite impact analysis and planning and also there isn't a huge amount to show, before during or after the upgrade.
One point I did want to stress is to get read of all the deadwood and redundant code as part of the exercise. If you are moving from 2000 to 2005 or have done so already you can leave the newly upgrade database 2000 compatibility mode, and there is even a 2000 compatibility mode in SQL Server 2008 (as well as one for 2005). Great! just back up that old 2000 database and restore to 2005/8 and relax. I wouldn't do this myself unless there was a third party application that isn't yet 2005 compliant. Why?
The reason I do like compatibility mode is that while it is on you can then set up profiler to watch for any events that use this feature. This can then be used to track down the stuff you need to change..
And why the picture of 25 tons of rubble? Well I am upgrading my drive so I got rid of all the redundant drives (2 of them each 100mm thick!) before installing drive v3 as the new one would crack if laid on top of previously cracked layers. Like upgrading SQL Server it's boring, but worth it in the end.
There's a lot of buzz around Software plus Services (S+S) from Microsoft and others. It's all very simple as Eileen will tell you - it's when you use a service like GPS that's remote and public and combine it with some software like CoPilot or TomTom on your GPS to find directions.
In a similar vein SQL Server 2008 can store geo-spatial data but that's of no value to a business unless they can see it and the best way to do that is on a map. If you don't have mapping software of your own then you can use a web service like Virtual Earth.
Johannes Kebeck a Microsoft Virtual Earth expert spent a morning in Visual Studio 2008 to produce an application for an insurance company which I have demonstrated in this blogcast:
You would expect there to be a record and hopefully only one record for each sales person in a typical sales system and another record in another table for each of the customers. But what happens when a one of these sales people gets assigned to a different territory or is promoted to be a regional sales manager? The usual answer is that the record is edited to reflect the change.
That's fine until you decide to start to look at trends over time and analyse how each person in the sales team is performing against target . In the example above, all of the sales in the system will appear to have been made by a person in their current role, so when a report is run the answer will be distorted. This is where a separate data warehouse can help, as choices can be made about how to deal with these kind of changes independently of the line of business system. There are three industry standard approaches to the problem of slowly changing dimensions:
The type 2 scenario requires the generation of a new unique key on the dimension as there will be multiple versions of the same source record each with the same source or business key. This new surrogate key is how the dimension is joined to the sales facts made for that version of the sales person as you can see below
For those of you in black and white, Steve's sales in E region are in orange and those when he moved to SW region are in blue. We can sum and group a join of these 2 tables by [Name] to see all Steve's sales or we can do this for [Territory] to get a true picture of sales in either scenario.
Note that the business key doesn't appear in the fact table and that the surrogate key is just an arbitrary number - usually a sequence number incremented as new rows are added to the dimension. It is also good practice to add extra columns such as [Is Current], above to mark which record is the current one. Alternatively [effective from date] and [effective to date] columns can be used to show when the row was in force.
So using type 2 slowly changing dimensions is the most work but the most versatile approach, and the good news is that there is a wizard for this in SQL Server 2005 Integration services (SSIS).
One way to speed up SQL Server 2005 is to upgrade the underlying operating system to Windows 2008. Why? My top three reasons would be:
However be aware you might need some hot fixes so check these articles before you try it as SQL Server 2005 may not install cleanly on the new OS:
Update for Windows Server 2008 for Itanium-based Systems (KB950636)
http://www.microsoft.com/downloads/details.aspx?FamilyID=ad44cb1c-2e36-45b4-bf4c-e0b01b12b233&DisplayLang=en
Update for Windows Server 2008 x64 Edition (KB950636)
http://www.microsoft.com/downloads/details.aspx?FamilyID=a0d43131-bb2c-4f46-b2f0-b90b14865948&DisplayLang=en
Update for Windows Server 2008 (KB950636)
http://www.microsoft.com/downloads/details.aspx?FamilyID=fb0ee17e-96eb-4cbb-ac09-95a4dcf73077&DisplayLang=en
I have seen the filtered indexing in SQL Server 2008 mentioned a couple of times over the last couple of weeks but I only got it properly when I got to try it myself in ctp6.
I have often needed to constrain a column to a only allow unique values but also allow the column to allow nulls and with filtered indexes you can put in a where clause like this ...
create unique index production.nullidx on production.product(code) where production.product(code) is not null
create unique index production.nullidx
on production.product(code)
where production.product(code) is not null
So the filter takes out the nulls. Apparently this is also really useful if you are migrating your database from another provider to SQL Server.
Here's' a practical example of how Reporting Services (SSRS) in SQL Server 2008 has been modified. Cascading prompts in SSRS 2005 re-query if the middle of the three parameters changes. In case you aren't familiar with the term this is where you select a country in a drop-down box and this in turn determines which states/regions appear in the region drop-down box for the user to select (i.e. those in the selected country)
For example using these three cascading prompts in AdventureWorksDW:
When a Country is selected then the State drop-down list is populated which is as expected, but when a state is selected the queries for both Country and City are both re-executed which is not necessary. This situation worsens if you have a separate dataset for the default value for each of these parameters as this will also cause more re-querying and there is no control you can exercise over that.
That's all fixed in SSRS 2008; the engine knows which datasets will be affected by a parameter change and only those will re-execute. Also the results can be shared between the parameter query and the default data set.
The really good bit is that all that you need to do is to move the reports to SSRS2008.
I have seen some strange schemas in my time which look like a good idea on paper but not on disk. A common scenario is the schema that is created by a tool controlled by a user and so we end up with columns like user21 in usertabel7 and so on. Then I got an e-mail about the poor performance of user defined functions (UDFs) following Simon Sabin's talk on the CLR and how good they could be for replacing T-SQL UDF's at the SQL Community event last Thursday .
The database involved in the problem had several tables where generic columns where used for different purposes. Taking customer as an example there was a separate table CustomerStrings that looked like this:
CustomerID is foreign key to the main customer table and CustomerStringTypeID is another foreign key, this time to CustomerStringType which has an ID and a description, with values in the description such as 'Fax', 'Mobile', 'e-mail' etc.
So the CustomerString table would have multiple rows for the same customer for example a row for Fax, mobile, email and so on. The good thing about this is that rows would only be created where a given attribute for a customer was used and so would be efficient on space.
Getting back all the e-mail addresses for a customer wouldn't be too bad either depending on how this was implemented and this is where the developer tried to implement a generic solution with a UDF:
CREATE FUNCTION dbo.GetCustomerString(@customerID int, @customerStringTypeID int) RETURNS varchar(50) AS BEGIN
DECLARE @returnValue varchar(50);
SELECT @returnValue = CustomerStrings.StringValue
FROM CustomerStrings
WHERE CustomerStrings.CustomerID = @customerID
AND CustomerStrings.CustomerStringTypeID = @customerStringTypeID
RETURN @returnValue
END;
The problem arises when this function is used in-line on multiple occasions to get three attributes for every customer:
SELECT
cst.LastName,
dbo.GetCustomerString(Customers.CustomerID, 1) AS Fax,
dbo.GetCustomerString(Customers.CustomerID, 2) AS mobile,
dbo.GetCustomerString(Customers.CustomerID, 3) AS email FROM dbo.Customers cst;
So the UDF gets called three times for every row in the customer table which is always going to be much slower than using a set based operation for example:
cst.LastName
fax.StringValue as Fax
mob.StringValue as Mobile
eml.StringValue as email
FROM dbo.Customers cst
LEFT JOIN dbo.CustomerStrings fax ON cst.CustomerID = fax.CustomerID AND fax.CustomerStringTypeID =1
LEFT JOIN dbo.CustomerStrings mob ON cst.CustomerID = mob.CustomerID AND fax.CustomerStringTypeID =2
LEFT JOIN dbo.CustomerStrings fax ON cst.CustomerID = eml.CustomerID AND eml.CustomerStringTypeID =3;,
However maintaining all the code for the possible combinations of attributes required is a big headache for this company so how can they get the speed of the second query without writing mountains of code. In my opinion the schema is the problem here. I would either create sufficient columns in the customer table for all the attributes required or using another group of tables with one row per customer to store contact details etc. Of course if there loads of columns required (as in SharePoint) many of which are usually empty then it's time to have a look at Sparse columns in SQL Server 2008 and that is what I will be posting about next..
Ever since I have been using SQL Server you have had the ability to manage SQL Server from another machine, so back in the day I used enterprise manager and query analyzer on my PC to do stuff on a server hidden in a basement somewhere.
At some sites an enterprising software compliance guy would quiz me on whether or not it was OK to install the client tools on my machine. I would point out that what matters is the license to connect to the server SO provided that the server I was connected to had a CAL assigned to me or it was CPU licensed then all is well, as the client tools aren’t licensed themselves. That usually sent him away, and I had a smug look on my face until the server team applied a service pack to SQL and then I needed to service pack my PC/laptop to get working properly again.
But is this correct? In a word yes, the EULA you agree to when you download SQL Server has this clause in it:
a. Running Instances of the Additional Software. You may run or otherwise use any number of instances of additional software listed below on any number of devices. You may use additional software only with the server software directly, or indirectly through other additional software. Analysis Services Shared Tools Business Intelligence Development Studio SQL Server 2005 Books Online Connectivity Components Legacy Components Management Tools Notification Services Client Components Reporting Services Report Manager Reporting Services Shared Tools SQL Server 2005 Shared Tools Software Development Kit SQLXML Client Features SQL Server Mobile Server Tools
a. Running Instances of the Additional Software. You may run or otherwise use any number of instances of additional software listed below on any number of devices. You may use additional software only with the server software directly, or indirectly through other additional software.
I get a lot of questions about how filestream works with the other new features in SQL Server, so here’s what I know.
First for those that don’t know, filestream is a new data type in SQL server 2008 that handles large unstructured data files like they are in the database. The clever bit is that it also allows them to be streamed out very quickly using win32 api as though they were files on a file system.
This is done by creating a special filestream filegroup, which actually creates a directory tree for the files to be stored but they now have filenames of guids controlled by SQL server. Filestream thus differs from Varbinary(max) where the data is chopped up into SQL Server 8k pages which then have to be stuck back together for retrieval.
General FAQs:
How do Transactions work? You can actually roll back a transaction where you have attempted to update a filestream object and it will revert to the previous version. This is because the update will copy in the new object and preserve the original until you commit. Needless to say lots of disk space could be needed here for example if your are storing HD videos in your database. How are Backups affected? Backing up and restoring the database is exactly the same. This is important as it allows the structured part of the row to be exactly in step with the filestream object after a restore. Additionally because it is filegroup it can be backed up by itself or excluded form other backups as required. What if I need my Filestream data in a high availability scenario? Mirroring. Most importantly Mirroring isn’t supported (so no filestream filegroup will be allowed on the principal or mirror), so either you continue to use Varbinary(max) in the same way as in SQL Server 2005 for your unstructured files, or you go for… Clustering. This is supported, but the filestream filegroup must be on a shared disk (so maybe you have to rely on your storage to give you high availability here). I would add that 2 node clusters are allowed in standard edition. Log shipping is also supported, but both ends must be running SQL Server 2008 with Filestream turned on.
How do Transactions work? You can actually roll back a transaction where you have attempted to update a filestream object and it will revert to the previous version. This is because the update will copy in the new object and preserve the original until you commit. Needless to say lots of disk space could be needed here for example if your are storing HD videos in your database.
How are Backups affected? Backing up and restoring the database is exactly the same. This is important as it allows the structured part of the row to be exactly in step with the filestream object after a restore. Additionally because it is filegroup it can be backed up by itself or excluded form other backups as required.
What if I need my Filestream data in a high availability scenario?
SQL Server 2008 interoperability with other new features..
What about backup compression? Backup compression doesn’t work with filestream. generally the files will already be compressed (think jpeg, divx etc.) What about Transparent Data Encryption? TDE doesn’t apply to filestream and in any case is only in enterprise edition. If you need to protect the files in a filestream filegroup use the Encrypted Files System (EFS) .btw filestream filegroups can only be created on NTFS volumes. Can I uses Integrated Full Text Search (IFTS) with Filestream? Yes in exactly the same way as you can for normal varbinary (max) data in SQL Server 2005/8. So you must have a unique identifier for each row and a separate column on each row to identify the type of data being stored (pdf, xlxs, docx, html and so on) for the ifilters to work. Which editions of SQL Server does it work with? Filestream applies to SQL Server Express up to Enterprise edition. btw the filestream filegroup does not count as part of the 4Gb limit to the size of an Express database and these special filegroup can be as large as you like.
What about backup compression? Backup compression doesn’t work with filestream. generally the files will already be compressed (think jpeg, divx etc.)
What about Transparent Data Encryption? TDE doesn’t apply to filestream and in any case is only in enterprise edition. If you need to protect the files in a filestream filegroup use the Encrypted Files System (EFS) .btw filestream filegroups can only be created on NTFS volumes.
Can I uses Integrated Full Text Search (IFTS) with Filestream? Yes in exactly the same way as you can for normal varbinary (max) data in SQL Server 2005/8. So you must have a unique identifier for each row and a separate column on each row to identify the type of data being stored (pdf, xlxs, docx, html and so on) for the ifilters to work.
Which editions of SQL Server does it work with? Filestream applies to SQL Server Express up to Enterprise edition. btw the filestream filegroup does not count as part of the 4Gb limit to the size of an Express database and these special filegroup can be as large as you like.
For further reading go to TechNet’s Books on Line for SQL Server 2008
Active Directory Federated Services (ADFS) doesn’t sound like the most exciting topic for a post, but I am going to post on it anyway precisely because it is boring.
First let me log in to windows with a Live ID..
You’ll notice I have used my Microsoft alias to sign in, and I haven’t entered my password yet (my cursor is still in the Windows Live ID dialog box. What password should I enter? Putting my domain password in here might seem like a good choice, or it could be a password I have set up for this site like we all do on some social media sites. None of these is a good story for the user and it would be better not to have to enter a password at all. This is what I get when I tab to the password field..
.. the Live service is asking me to sign in at Microsoft.com which is my domain. I click on this and I get to my SQL Azure account screen.
What you have just seen is ADFS in action. Essentially Live has been told to trust the Microsoft domain to authenticate users rather than have all the Microsoft logins duplicated in Live. Live is still responsible for authorisation i.e. deciding what I can do once I get in , and in this case the only Live service I can use with this account is Azure.
In the real (non-Microsoft world) this is very useful stuff..
Of course this technology isn’t much use unless you can also pass identity information across platforms as well as federating credentials across different active directory domains. ADFS can also be integrated with other federation platforms including IBM Tivoli, Novell Access manager, Sun Open SSO and CA (Site Minder and Federation Manager) using SAML (Security Assertion Mark-up Language). Also Microsoft’s is one of the co founders of OpenID, the organisation that is promoting standards in identity management.
Exactly where is ADFS? The first version of this was simply a role that you configured in Windows Server 2008 /2008 R2, and to store any additional data above and beyond active directory there is a separate data store which is of course SQL Server (Express is fine for this). The latest version that does all the stuff I have described here is a separate download from here along with all the whitepapers step by step guides you’ll need to get started.
To conclude, ADFS is boring for the users because there’s nothing to see, but is a good example of the sort of work that will continue to be done by IT professionals over the next few years as business transitions between traditional on premise solutions and a mix of these plus private / public cloud based services.
A new edition of Windows Server 2008 got launched yesterday Windows Server 2008 Foundation which will only be shipped on a range of appropriate servers. It supports 15 users, 8gb RAM and 1 processor (but no core limit).
I can see that many small business might want to use this as their central server where they may have some of their data in the cloud (the obvious example being e-mail) but want some information such as the company accounts on premise. I can also see Foundation as being a logical home for SQL Express or Workgroup edition, probably bought along with the aforementioned accounts or a small ERP system.
Companies wanting to do a little bit more on premise would probably be better off with Small Business Server (SBS) which has exchange and SQL Server built in as well as a basic set of tools for monitoring the health and security of the server and the PC attached to it.
As a final thought even though April 1st was yesterday, I wonder if this would be a good platform to host LAN parties!
One of the many cool things that's already in Analysis Services in SQL Server 2005 (SSAS) is the concept of many to many dimensions.
Suppose I have 3 bank accounts and one of those I share with my wife, and on another I am co-signatory for a local charity. This means there is a many to many relationship between me and those accounts. In the relational world this is typically dealt with using a bridging table - which in this case would have columns with AccountID (to reference the account table), AccountHolderID (to reference the signatories) and a few other fields such as SignatureImage, etc.
In analysis services many to many dimensions work in a similar fashion, but where in the relational world they would be linked directly to each other, in the multi-dimensional world, the two dimensions in our scenario would be linked by the use of an intermediate fact table, joined to the main fact table. There's an example (as always!) in the Adventure Works sample SSAS solution, where dimSalesReason links to the main fact table (FactInternetSales) via an intermediate fact table:
Many to many dimensions can be useful to improve the performance of distinct counts, and to carry out basic basket analysis (customers who bought X also bought Y). Mark Russo has an excellent white paper on this whole area running to 84 pages of step by step instructions to get you started.
However this technique comes at a price and can cause more performance problems than it solves. A new whitepaper came out late last year and discusses the pros and cons of this approach and how to get the best out of it, careful use of keys and aggregation design.
A lot of the things you need to do with SQL Server may well have been done before even if it’s using the new features in the new version , SQL Server 2008. The challenge is to find this stuff and be able to make use of that earlier effort and a key resource for this is codeplex an open source portal of stuff you can use on the Microsoft platform. I say open source because this stuff is free to use under some recognised license such as , GNU GPL, Microsoft Public License etc.
A good example of the good stuff on here is all the projects that use the new spatial capabilities of SQL Server. There are projects containing the freely to use US census data, map point integration, various type conversions and a start on providing routing functionality. I also found a good example of how to use merge and table value parameters in integration services.
Of course this shouldn’t be one way traffic; a lot of the code here is provided by Microsoft, but MVP’s and partners also contribute, but there are no rules to stop you helping out as well. This might just be to comment and suggest improvements to modifying a project in some way to writing your own.
At the very least it should stop you doing so much wheel reinvention.
Imagine you have a windows 2003/SQL Server 2005 cluster (active-active) and your mission is to both virtualise and maintain high availability using Hyper-V in Windows Server 2008/SQL Server 2008. This may sound odd but I was asked the question at TechEd and I wanted to do some research on it. Fortunately I on Windows 7 training this weekend and have access to 2 of the key players Jeff Woolsey who ‘owns’ Hyper-V development and clustering product manager Dave Dion.
So are your options? and more importantly how do they help?
I would start this by addressing what are you most worried about…
Also what are your goals e.g.
Finally other workloads are also on this setup which will also be virtualised.
The customer who asked me this wanted to cluster the cluster by..
So how does this stack up against our criteria above?
Basically not very good..
Initially this solution looks quite good - if node 1 or node 2 dies then the surviving vm on the surviving node will take ownership of the storage. But this will take time. Currently hyper-V VM’s only support iSCSI (fibre channel is on the way). The two VMs will have different routes to the same shared storage so if that is not accessible then the system will fail. However the killer is that Microsoft do not support SQL Server failover clustering of VMs as per this KB. BTW this limitation applies to all virtualisation technologies, not just Hyper-V.
Is there a better way? For example if you mirrored the critical databases between vmA and vmB then you now have a copy of the database on either shared or separate storage. You could then script a manual failover to the mirror if the node running on the principal moved over to the other node as its storage would now be remote to the VM. This is supported, and mitigates a number of failures including:
Of course this is at the database level, but the principal could be extended for more than one database and other workloads as well.
However in my opinion the best way to consolidate here is to create a physical cluster and consolidate SQL Server workloads on into it. Clustering is a lot easier in Windows Server 2008 and you n o longer need carefully matched hardware. though the surviving nodes in a cluster need to be powerful enough to handle all of the work of the cluster.
Let me know what you think and what your plans are in this space.
The feature of SQL Server 2008 that seems to get the most attention from DBA's is the Resource Governor. It basically does what it says on the tin; for example you may want to reserve a portion of CPU or other resource for a user, process etc.
At the top level Resource Governor has Resource Pools, and there is always default resource pool
Below this you create Workload groups:
CREATE WORKLOAD GROUP groupAdhoc CREATE WORKLOAD GROUP groupReports CREATE WORKLOAD GROUP groupAdmin
These workload groups will be belong to the default resource pool, and for this introduction I will keep it simple, by leaving it like that. It is then a matter of assigning whatever you want to the those groups by using a function like this:
CREATE FUNCTION rgclassifier_v1() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @grp_name AS SYSNAME IF (SUSER_NAME() = 'sa') SET @grp_name = 'groupAdmin' IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%') OR (APP_NAME() LIKE '%QUERY ANALYZER%') SET @grp_name = 'groupAdhoc' IF (APP_NAME() LIKE '%REPORT SERVER%') SET @grp_name = 'groupReports' RETURN @grp_name END
Notice that you can use any rule you like to create an association with a workload group e.g. users or the application.
This function is then needs to be applied to the resource governor like this:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1) You are now ready to configure which workload group gets which resources with this syntax:
ALTER WORKLOAD GROUP groupAdhoc WITH (REQUEST_MAX_CPU_TIME_SEC = 30)
or
WITH (MAX_CPU_PERCENT = 50)
Finally the changes need to be applied to the resource governor process running in memory
ALTER RESOURCE GOVERNOR RECONFIGURE
Changes can be applied to the Resource Governor at will and take effect immediately. The function can be changed to move objects in to different workload groups as required and all of this will affect processes already running.
To try this you will need CTP of SQL server 2008 which is available here.
One of the best parts of my job is helping people, and although I am not a Microsoft support guy, I will respond if I can to requests on my blog , twitter etc. a particularly if it’s from people new to a particular technology. This is partly out of a recognition that we’re all experts in something and even when it comes to the IT department not everyone is deep into SQL Server, System Center or Windows. SQL Server actually presents it’s own problems as it often comes with a business application which hasn’t ben procured through IT, and somewhat naturally when the business asks for help IT will push back. However application like this can be mission critical and in one case (I am not allowed to name names) this resulted in a user running into trouble with a 2TB (yes that is 2 Terrabytes) database filling up his desktop machine.
It was clear from the tone of the e-mail that although this chap is highly technical and has some basic knowledge of IT he hadn’t really done anything with SQL Server before, and was feeling his way. His database turned out to be so large because it was mostly Filestream, a new data type in SQL Server 2008 and later for storing large unstructured data files in such a way as to keep them inside SQL Server’s control while still essentially being separate files capable of being accessed individually through the win32api. So if you looked the disk you would see the files for the database itself, the log file and then a folder with these Filestream files in in a set of SQL Server generated folders and file names i.e. they won’t have the same names as when they were added to the database.
There wasn’t time or space on this machine to backup this database but fortunately the user was using a test database to test how to move it and reuse it elsewhere. I recommended detach –> copy –> attach without realising that Filestream doesn’t like that if you just do it as is, the attach left the database in an unusable state as it was still looking for the Filestream data in its original location. Fortunately running detach form T-SQL:
USE [master] GO ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase' GO
where MyDatabase is the name of my database. If you are new to SQL Server those file will be in something like
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
and that’s part of the problem, a default install of SQL Server will drop these data files on the C Drive along with the SQL Server program and the operating system. Anyway you would then copy the database files to a new larger hard disk and then run this..
USE [master] GO CREATE DATABASE [MyDatabase] ON ( FILENAME = N'D:\DATA\MyDatabase.mdf' ), ( FILENAME = N'D:\\DATA\MyDatabase_log.ldf' ), FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT ( NAME = N'MyDatabase_FSData', FILENAME = N'D:\DATA\FileStreamData' ) FOR ATTACH GO
where D:\data is where you have copied the files to in my case D:\Data
The next problem we hit that his new server came back with an error,
Msg 1813, Level 16, State 2, Line 1 Could not open new database 'MyDatabase'. CREATE DATABASE is aborted. Msg 948, Level 20, State 1, Line 1 The database 'MyDatabase' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MyDatabase'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'MyDatabase' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
This is pretty simple – the new server was running an older version of SQL Server and while you can take on old database detach it copy and attach it to a newer server (which is a supported way of upgrading a database) you can’t go backward to an older version. In this case the original database is SQL Server 2008 R2 and the second server was running SQL Server 2008. Given this is an end user scenario it’s not surprising that he had differing version around, and my final tip is to run this to find out what you’ve got on a server by server basis:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
The version is 10 for SQL Server 2008 , 10.5… for SQL Server 2008 R2.
If you have lots of servers to perform SQL Server discovery on then you’ll be much better off using the free Microsoft Assessment & Planning Toolkit, which will also return operating system and server information plus a host of other information as you need.
So imminent disaster averted, user fairly happy, but all I have done is put some gaffer tape over the problem in reality this data need to be migrated to proper server so that as the database grows it will perform and be available to the whole team and hopefully be properly protected by the IT guys.
I am seeing a resurgence on loud and bold wallpaper in the interior design market, but is there anything out there for the fashion conscious DBA?
How about this…
As you can see it’s a complete diagram of all the system views in SQL Server 2008, available as a pdf here.
I have just arrived on Eileen Brown's team as the Data IT Pro evangelist. During my extensive interview process I was asked by Keith Burns about the common language runtime (CLR) in SQL server 2005 and I replied with a quotation from his blog "that if it can't be done in SQL it isn't worth doing". That would seem to rule out blogging but with the arrival of SQL server 2008 and it's ability to handle the vast amounts of unstructured data we encounter both at home and work this could well turn out to be absolutely true!
The other point about this remark is our digital immortality. At our internal technical conference last week, one of the speakers talked about the first computer he had to use, a secondhand missile guidance system which he had to hook up to a mass spectrometer. After spending many sleepless nights getting the system working he prepared a comprehensive demo for the chemistry lab staff. They weren't impressed at all by his technical wizadry and just wanted to get on and use the mass spectrometer. So he slightly 'modified' the code to make the machine crash randomly and added a lot of extra unnecessary steps to the startup instruction manual than was necessary. This all came back to haunt him when the machine kept hanging as his random number generator turned out not to be so random and he had to use his old time consuming instructions to continually restart the system in order to debug his code.
So not only are your finest moments preserved forever but also our worst mistakes. Which needs me neatly onto one of the darker corners of SQL Server and that is deprecation of features as occasionally we need to tidy up some of the the throwbacks to earlier versions of the product.
There are two parts to this - features that won't be supported in SQL server 2008 as shown below:
Category
Deprecated feature
Replacement
And features that will be deprecated in future releases SQL server after the 2008 version.
http://msdn2.microsoft.com/en-us/library/ms143729.aspx
So careful planning is needed if you plan to migrate to SQL server 2008.
I was at SQL Bits (the SQL) at Aston University on Saturday and as usual if I can't answer a question on the day you will find it here:
http://localhost/reportserver?/AdventureWorksSampleReports/Product+Line+Sales&rs:
Command=Render&rc:Stylesheet=MyStyleSheet.
The details of how to do all of this are in Books on Line (local help reference)
If you didn't make my session there is a new clinic coming out in the next few days on Reporting Services 2008 here. And if you didn't make the event at all it was another resounding success with 325 attendees despite freight on the line between London and Birmingham, so well done to Tony Rogerson, Simon Sabin, Martin Bell, Alan Mitchell and the rest of the team.
Anyway keep an eye out on the UK SQL community site for the next one, and be aware they also run stuff in the evenings, in case you love of SQL Server collides with other lesser interests such as DIY, extreme sports, retail etc.
Excel already has always had excellent support for analysis services, but to get the best form it you need to use a version of office later than than the version of SQL Server you are connecting to. So for analysis services 2000 excel 2003 is OK but if you connect to analysis services 2005 then you are a lot better of with excel 2007 as this version understands the newer features.
One thing you can't do in any version of Excel is write your own analysis services (mdx) formulae. There's no technical reason for this as all excel is doing in getting the data is sending an mdx query in XML/A form. That has been fixed with an OLAP add-in for Excel project that has just been posted on Codeplex. The tool also allows you to see the mdx that's passed to analysis services, and my top tip for this would be to used it to design queries based on cubes for reporting services reports.
One word of caution about keeping analysis services calculations away from the cube is that they can only be seen by going through excel and so I would see this tool being used as a prototyping tool for new calculations, and when these are ready to go in to production they are stored in the cube and available for all to use. Why? One version of the truth - hard to achieve and very easy to break.
I live my little snipping tool to capture bits of screen to share tips and stuff on this blog and in my decks, but since I moved to in Windows 7 it’s gone aaarrghh! Annoyingly all my mates (both of them) have got it on their machines so where did it go?
Apparently it’s part of the tablet specific optional features and I didn’t have this selected. So to fix it (and now I have the tool installed I can show you!) go to programs and features (you can just type that in on the search bar to go straight to it) and from there select turn windows features on or off ..
to get this dialog
..and check the option for Tablet PC Components.
For my blog I also use a lot of the picture editing tools in Windows Live Writer especially crop and border style you can get the latest version of this free blogging tool here.
One of the many interesting new features in Windows Server 2008 is the Read Only Domain Controller (RODC). Simply put , the RODC can only be updated by another Domain Controller i.e. NOT directly. It is designed for the branch office and caches the credentials of the users in that branch and saves users then having to round trip to the main domain controllers back at head office. This really comes into when the link to Head office breaks as the local users can still login using the RODC.
In the same way these branch offices often need to run SQL Server locally, this might then have replication set back to head office to give maximum local performance when the links to head office are slow and/or unreliable.
There is some debate at the moment about the need for SQL Server 2008 will be supported on Windows Server 2008 when it runs the RODC role.
I am using the word supported here in the Microsoft sense of the word i.e. if it’s broken Microsoft will fix it and support you in the process.
In my opinion SQL Server 2008 needs to be supported on an RODC as you really only want one server in the branch. One way to shoehorn all of a branches applications onto physical server would be to use Hyper-V i.e. to create a virtual machine on top of the physical RODC server with all the application stuff. Virtualising SQL Server in a production environment is a frequently debated topic in its own right, but might work in this scenario as SQL Server is unlikely to be maxed out in this scenario.
So if branch offices figure in your SQL server estate, please let me know what you think about any of this.
Among the interesting stuff discussed at last nights SQL community meeting at the Microsoft Campus,was a lively debate on whether to wait for SQL Server 2008 sp1. The traditional view has been to wait until sp1 because that is the stable release. One wit in the audience suggested that SQL Server 2008 was really SQL Server 2005 sp4 and therefore everyone should go right ahead. While SQL Server 2008 is actually a lot more than a service pack, it is not the major rework that SQL Server 2005 was.
David Portas an MVP working for Conchango pointed out how stable it was and he should know he’s been using it for year. He went on to pint out that maybe an sp1 would not be that well tested and would you really deploy that version the day it came out?
My own take on this is that there probably won’t be a service pack for a long time if at all. SQL Server 2008 is very stable and don’t take my word for it because if it wasn’t the stories would be all over the web (the Register, ZDNet, slashdot etc). So your decision on when to upgrade should not be influenced by that and reasons to upgrade should instead depend on what you are doing with SQL Server:
If your interested in SQL Server 2008, you might want to download the product from your TechNet subscription and at least install the client on your local machine so you can see how it looks.
Backing up a cube is not an ideal experience in SQL Server 2005 for two reasons, it’s a manual process to schedule a backup and as the size of the cube grows the backup time increases exponentially i.e double the size of the cube and the backup time and size will increase by a factor of four.
Curiously although there is excellent management tools support for managing SQL Server as I mentioned yesterday, I am not aware of similar support for analysis services, despite the many third party tool vendors who make front ends for it. so here’s a few things to help..
To automate a cube backup use the SQL Server Management Console to generate a script, by selecting the analysis services database to be backed up right click select backup and then click on the script icon as shown below:
The generated script will look like this:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
</Object>
<File>Adventure Works DW 2008.abf</File>
<AllowOverwrite>true</AllowOverwrite>
.. depending on the options you set (like checking allow file overwrite). Anyway copy the script to the clipboard and then create a SQL Agent job to run that script. the job step should then look like this once you have pasted the script.
You will of course want to test the job and verify the cube can be restored. You can get more info on all of this here.
For really large cubes you may need to go for the raw backup solution mentioned in this article, but the other cure for the backup explosion problem is to upgrade Analysis Services 2008 as the size and time taken to do backups is a linear progression i.e. doubling the size of the cube requires double the space and double the time as you’d expect.
Moving a cube from analysis services 2005 to 2008 is about the most painless upgrade you can do and you can do this with a backup/restore or it will just get upgraded if you are doing an in place migration.
Following on from my last post, when DBA’s have to manage multiple SQL Server 2000 instances you would normally see all of them registered on their local desktop in enterprise manager under a variety of groups such as dev, prod, test etc.
With the advent of SQL Server 2005 this practice seems to have died off, perhaps because no one knows that this option exists in Management Studio, or because there’s no advantage in doing so. But that changes in SQL Server 2008, with the introduction of Configuration Servers.
So in this screen shot I have opened up the registered servers by selecting View –> Registered Servers (CTRL+ALT+G):
As you can see there is a node for configuration servers and the first step is to add a server (in the same way as you register a new server). Then create a group underneath it and finally register the servers this configuration server will apply to ( again in the same as normal registered servers are added):
Having done that, you can now apply policies to all of the servers in the group or a T-SQL command by running them from the configuration server.
So in this exercise if I run this
USE master GO SELECT * FROM sysdatabases; GO
on the configuration server (by creating a new query from the configuration server) I would get a message:
DBSERVER\INST03(DBSERVER\Administrator): An error occurred while executing batch. Error message is: The server is not connected.
DBSERVER(DBSERVER\Administrator): (11 row(s) affected)
DBSERVER\prod(DBSERVER\Administrator): (8 row(s) affected)
and all of the databases in each server will be in a grid in the results tab. Note the error above is because dbserver/inst03 is not running (as you can see from the management console screen shot above).
In the same way you can right click on the configuration server and run a policy over all of the servers in the group.
So like lord of the rings you can have one server to control them all.