Here’s another SQL Server 2008 blogcast on manageability, this time on using Policy Management, to implement best practice in SQL Server:
My first ever blog post was about the features of SQL Server 2000 that won’t work in SQL Server 2008, and as I have mentioned before, SQL Server 2000 will come out of Mainstream support today! What this means is that SQL Server 2000 will still be the subject of security fixes, and you can still get paid support for it, but other fixes will only be available if you take out extended support, which you have to pay for.
I know of several large organisations who will take out extended support and have planned for this. However if you weren’t aware of this then all I can suggest is that you look to migrate as soon as possible and in the meantime consider taking out extended support in the meantime if your database is mission critical.
The key resources for upgrade have been the subject of several of my recent posts but too summarise:
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.
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.
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
I remember the old batman shows had a “don’t try this at home” disclaimer after every show, and this came to mind as I started playing with Windows Server 2008, Hyper-V and SQL Server 2008 on my new shuttle PC (4 cores, 8gb RAM, 1TB on three disks)
Here’s what I’ve done
Installed Windows Server 2008 enterprise (you’ll see why in a minute) and added the Hyper-V role and then rebooted.
Added four virtual machines (hence the need for enterprise as it supports four vm’s):
With this rig you can now play with disaster recovery and high availability in SQL Server and work out what’s best for you. For example log ship from the cluster, log ship to the cluster form the mirror, snapshot, and replicate yourself silly.The cost is £1600 for the shuttle and £300 odd (depending on the offer you get) for a Tech|Net subscription so you have the software you need and the knowledge base to use it properly.
I also have to confess I built mine with a lot of help from Daniel Sol (a SQL Server support supremo) so that I can show all of this off at TechEd Barcelona on Monday. If you want to find out more about this and you are going then come along to DAT302, and if you’re not Daniel and I will be showing you how when I get back via a series of blogcasts.
Finally the disclaimers..
Notification services is one of the deprecated features in SQL Server 2008, essentially it is supported for now but will not be in the next release of SQL Server. If you are using notification services currently and you are looking to upgrade to SQL Server 2008 then you can get it from here.
But why deprecate the feature? Basically not many people were using and it wasn’t seen as being easy to use or flexible enough. But what are your migration options if you are using it?
That’s going to depend on what you are using it for:
Having collected the information you want to track, the challenge then is to get this information back to you when things go wrong. This could simply be a case of making use of an agent job to do a test to see if there’s a problem, and then to conditionally running a reporting services report of the problem(s), or a send mail procedure to do it that way. DDL triggers might also be an option, so there are lots of options but no obvious single thing to take your existing setup and migrate it to any of the above I’m afraid.
So I would be interested in the comments this generates, and as ever if you have some ideas or an issue with this then Microsoft Connect is the forum for that.
SQL Server 2008 will have a mechanism to encrypt the database at rest, with little overhead, and you can try this in CTP5. While it is on the instance where it was encrypted it can be accessed by any application with out modification. However if it is detached/attached or restored to another instance then the key used to encrypt it will need to be available on the new instance.
How does that affect replication?
Well you don't have to encrypt the replicated database, and that maybe OK if you are only replicating a non-sensitive part of the data. The replication agent will decrypt and encrypt data on the way in and on the way out between the two. However the recommendation is to encrypt the databases involved with a Master Key.
Finally you should be aware that the data going in and out of a database that has been encrypted is not itself encrypted, that is left to the transport layer so if that's important to you then you will need to apply any of the common approaches for securing traffic i.e. VPN, SSL and IPSec.
For further reading check MSDN here
One of the biggest headaches in SQL Server 2005 is trying to manage resources where there are competing workloads on a shared server. A typical approach is to use instances, but this completely hides memory and CPU from other instances which are then not available to an instance under stress whether the other instances are using them or not.
The resource governor in SQL Server 2008 fixes that and I have a short blogcast here on TechNet Edge to demo how this works...
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
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.
Metadata is “data about data” and in the BI world this means two things:
There are a number of placeholder in all parts of SQL Server to support this, from extended properties in the database engine , actions in analysis services can take you to a website describing each calculation, and integration services extensive logging capabilities.
To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:
This is all designed to work on SQL Server 2005 and should be fine with SQL Server 2008. My only word of caution is that report models are not being developed further and that the whitepaper only refers to Report Builder v1.
Anyway it’s a useful (and free) set of tools and you have the source code to hack it around to get it do what you need. e.g. compliance requirements (Sarbannes Oxley, Data Protection, Basel II etc.) or simply to make your BI project more accessible to your users and easier to maintain.
For those that couldn’t justify going to to TechEd in LA, you may not be aware that the next version of SQL Server has been announced. Following the Windows Server practice of major minor releases then new baby is SQL Server 2008r2.
Why another release so soon? Maybe this video will help..
How does the new release help? here’s a quick rundown of what’s coming in r2..
If you are interested in this you can register for the beta here.
It’s a sad fact that anti virus software is an essential part of using a computer in toady’s connected world, and many people baulk at paying extra for this , although it has never been that expensive. Microsoft have released a beta of what was Morro, now known as Microsoft Security Essentials (MSE). It’s in beta now but this program is only open to the first 750,0000 registrations.
It has a lot in common with Forefront Client Security except that as a free consumer orientated product, it is missing all the manageability of it’s licensed sibling;
This common lineage means it is fit for purpose; the anti-spyware/anti-virus capabilities in Forefront Client Security are good enough to protect 50,000+ of Microsoft’s own PC’s and laptops including mine.
Of course it would be a simple matter to include all of this in the upcoming Windows 7 operating system, but you can imagine that the anti-virus industry and the EU would take a dim view of this so this will be a separate download when it is released.
Note 24/6/09 I have noticed that the site to register is already closed, and I think it may have just been for the US, so sorry about that. When I know more I'll put up another post
It always nice to see historic aircraft , and vintage cars out and about rather than stuck in museums, the noise and even the smell of castor oil add to this nostalgia. However keeping them going requires a lot of effort and keeping them current with modern rules means that a Fokker triplane will need a proper seat harness, radio etc. and my mates Porsche 356 now runs on unleaded fuel.
Keeping software current means patching and possibly bolting on add-ons which can affect performance and make management more of an issue and I would argue that you don’t get the same feeling of pride and a job well done from looking after old software. Getting hold of the bits in bot scenarios can be tricky as manufacturers cease production. With old cars and planes this can lead to small engineering firms recreating new parts and at the extreme complete replicas. However I can’t see many people writing their own hot fixes and service patches!
I mention all of this because SQL Server 2005 is now coming to the end of its life. The key event is the 2nd anniversary of the release of its successor SQL Server 2008 and this occurs on 12th April 2011 and the implications of this are:
Full details of the support arrangements for SQL Server are here (you’ll need to click on the SQL Server 2005 tab)
Full details of the support arrangements for SQL Server are here (you’ll need to click on the SQL Server 2005 tab)
What you decide to do about this is of course up to you. However while I can see the fun in maintaining and restoring an old car or plane I can’t see the justification for running databases on SQL Server 2005 unless:
You will at this point tell me you don’t have software assurance and you can’t justify the upgrade. However there is so much extra stuff in SQL Server 2008 R2 that you can just turn on without upgrading:
The upgrade from SQL Server 2005 to 2008 should be a straightforward process but it is still important to run the application/database through the upgrade advisor and there are a couple of other useful links here..
• TechNet Upgrading to SQL Server 2008 R2
• SQL Server 2008 R2 Upgrade Advisor
• SQL Server 2008 R2 Upgrade Guide
As ever I am interested in your upgrade stories and why you feel you can’t upgrade so ping me I have polo shirts with SQL Server 2008 R2 on even if you can’ put it on your server yet
Given the almost mandatory drive to server virtualisation in data centres, what do you need to worry about and what’s in SQL Server to help you?
The biggest worry is probably trying to manage everything, as typically many organisations simply create a virtual machine for every physical server they have and them lump them onto a fewer number of newer more powerful servers. so you still have as many instances to look after as before.
SQL Server 2008 introduced 3 new tools to help manage multiple instances of SQL Server:
Central management Servers allow you to register servers and collect them into groups to use with T-SQL command and the other tools below:
Policy Based Management (PBM) allows you to define a facet of SQL Server you want to manage, establish what condition you want it in, and then create a policy to encapsulate this and whether you or not you want to simply report on how things are now or to enforce that policy on the target servers (note not all conditions and facets can be enforced). For example you could write a policy that check to see that all of your databases were backed up over the last week, or setup a policy to only allow stored procedures to be created if they have a usp_ prefix to annoy your developers. a final thing to note is that the best practice analyser in SQL Server 2008 is simply a set of polices which you can install, and a final final note is that
Powershell. Powershell is built into Windows Server , Windows , exchange and SQL Server 2008 It’s batch files on steroids or more accurately a .net based scripting language. One of my standard demos is to use this to invoke a policy across a group of servers defined in a text file, put the results into another SQL Server table, and have a custom report to show the results. There is a good example of what is possible on Codeplex – the Enterprise Policy Management Framework..
The other good thing about what these tools can do is that you only need one installation of SQL Server 2008 to manage the other earlier version of SQL Server you may have, without having to use any other tools from Microsoft or anyone else.
Both Standard and Enterprise editions of SQL Server come with a lot more than just a database engine, there’s also a suite of BI tools:
which you are also licensed to use on the same server as the database engine. That might be OK for a small business, however many of the BI projects I have worked on have needed individual servers for some of these components, and then things get more complicated.
Essentially if you put a server component of SQL Server (as distinct from the client tools which I’ll come back to) then you must license that server as well. e.g. you might have Server A with the database engine on and all your databases, while Server B might have Reporting and Analysis Services on and both of these must have an appropriate license of SQL Server.
Which edition is up to you, so your server running database engine might be Enterprise edition, while you might only need Standard edition for your reporting services server.
When it comes to the licensing model you want again each server can be different so Integration Services might only need a 5 CAL license because it isn’t accessed by anyone, but reporting services is opened up to the internet and so must have CPU licensing.
Finally the client tools are not licensed themselves and this includes the client connectivity tools, BI Development Studio, SQL Server Management Studio and the Report Builder, so you can deploy those as needed.
For more information on this complex topic:
This might be a strange topic given that the release of SharePoint 2010 is just around the corner, but I wanted to respond to a couple of questions and debates I have been involved with in my temporary role in the partner team.
I would like to start by going back to the early days of Microsoft BI, analysis services (and OLAP services before it) were just getting established but there was no analysis services client from Microsoft to give users full access to the cubes in analysis services. There were two third party contenders out there Panorama NovaView and ProClarity. These were both web clients and as they evolved they both developed portals form which reports and content could be accessed and changed. They also had security baked into them on top of what was in analysis services itself. ProClarity was acquired by Microsoft and the descendants visualisations in that tool (such as the decomposition tree) are now in SharePoint 2010 enterprise.
Panorama continues to be a Microsoft partner and is a good choice for business who just want a web client to get at their analysis services cubes, or even to get more functionality out of PowerPivot. They also continue to have their own portal or can integrate with SharePoint as desired. There also another 40+ products form partners out there which also provide web access to cubes, and here is a good a list as I’ve found of them
The other key Microsoft BI offering is reporting services which either has its own portal complete with security (Report Manager) or can be integrated into SharePoint.
So you don’t have to use SharePoint to provide Microsoft based BI to your users, but I would submit you are going to need some sort of portal, even if this is just a set of web pages where users can see content they are allowed to see and add more content to it (again subject to security).
However if you want to provide access to reporting and analytics to your users then SharePoint or a similar dedicated portal would be a better option as you can then provide a single point of entry and a single security mechanism to control access to BI.
On the question of cost you could just use SharePoint Foundation (the successor to Windows SharePoint Services), and SQL Server Standard edition, this would give you reporting services integration, and the other key parts of the Microsoft BI stack (analysis services and integration services). However you won’t get the performance point monitoring and analytics (which is in SharePoint enterprise) so the money you save by doing this must be off set against the need to buy a third party tool (like those I have listed above) to replace this functionality. You might not actually save any money and the solution could be more complex, but it might be exactly what your users need. This is a good thing, both for business and for Microsoft:
What I would suggest is to at least have a look at SharePoint 2010 as it’s scarily easy to set up and use even for this old DBA.
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
SELECT * FROM sysdatabases;
on the configuration server (by creating a new query from the configuration server) I would get a message:
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.
Twelve New Servers Coming
The product guys at Microsoft have been busy so there are going to be lots (but not 12) of new servers coming out this year:
So that's 6, 13, 11 or 18 servers, and I will have to defer to my wife (and my accountant) to do the the creative accounting to make 12!
swag to the first person who can work out the four project code-names in the drawing and tell me what products they relate to!
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.
Continuing my series of notes following the TechNet event last Thursday evening, I wanted to explain that in SQL Server 2008 there are two new data types for storing spatial information; geometry and geography. Unfortunately on Thursday I didn't have the final release of CTP5 so my demo didn't have the geography data type and I used latitude and longitude coordinates in a geometry data type with a Spatial Reference ID of 4326 to indicate that they were in fact geographical data.
To explain to those that weren't there...
The Geometry data type supports a flat 2D surface with XY coordinates for points. Points can be on lines, on line string and mark the edges of polygons. There are then methods like STintersects , STarea, STDistance, STTouch which work as you'd expect; the naming convention conforming to standards from the Open Geospatial Consortium (OGC).
The Geography data type uses the same methods but the data type reflects the fact the we live on a curved 2D surface popularly known as the earth. The st* functions are used against both new data types but the answers will be different as a result of the curvature.
One of the odd things about these spatial data types is the need for the aforementioned Spatial Reference IDs (SRID). Both geometry and geography data types have two parts, the coordinates of the object and the SRID indicated in orange below:
DECLARE @g geometry; SET @g = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @g geography; SET @g = geography::STGeomFromText('POINT (3 4)', 4326);
Note that if the SRIDs of two pieces of spatial data don't match then the various spatial methods in SQL server 2008 will return null.
One puzzling thing to leave you with on spatial data is hemispheres:
I am guessing that this is also something to do with the standards?
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:
And features that will be deprecated in future releases SQL server after the 2008 version.
So careful planning is needed if you plan to migrate to SQL server 2008.
Blog posts which just reference whitepapers etc. can annoy some of you so I try and limit these, but if you are one of the many IT managers or DBA’s worried about compliance then please read on.
The IT Compliance Guide is a comprehensive guide to all of those annoying acronyms that seem to get in the way of our work, like SOX, GLBA, HIPAA, EUDPD, PCI DSS, ISO 27002, COBIT 4.1, and AICPA GAPP. Some of these (SOX HIPAAS AICPA) will only apply to those of you who work for an American firm (like me then!), but the EU data protection Directive (EUDPD), and the ISO Code of practice for information security management (ISO27002), are closer to home so this is definitely worth looking at. It also includes an excel workbook to guide you through the processes.
The whole thing has been signed off by auditors Grant Thornton and is free to download.
Transparent data encryption (TDE) in SQL Server 2008 enterprise edition is a great tool for protecting your data ‘at rest’ , but you need to be careful when you use it.
TDE only really comes into play when you move the database to another location (hence the transparent in the name). When you encrypt a database you do so by making a key and using that. If you move the database to another location e.g. with detach-> copy -> attach or backup –> restore then you must have that key at the new location. Bear in mind that a new location could just be the original server which has had to be totally rebuilt as the key won’t be there anymore.
Here’s an example of how to use TDE in a career limiting situation:
DBA: I need to protect my data from unauthorised access
Microsoft chap: Well, you could use TDE this cool new feature in SQL Server 2008
DBA: Thanks Microsoft bloke
DBA: Hey Microsoft chap I used that TDE thing but the server crashed and I can’t get my backup to restore.
Microsoft chap: Ah OK you need to restore the key you used to encrypt the database and her’s some sample code:
-- if there is no master key in the master database, create one (don’t do this if there is already one existing)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 're4lly ReAlLyStr0nG P4ssWOrd'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 're4lly ReAlLyStr0nG P4ssWOrd'
-- restore the certificate including private key
CREATE CERTIFICATE MyDBCert FROM FILE = 'c:\SQLBackup\MyDBCert.cer' WITH PRIVATE KEY ( FILE = 'c:\SQLBackup\MyDBCert.pvk',
CREATE CERTIFICATE MyDBCert FROM FILE = 'c:\SQLBackup\MyDBCert.cer' WITH PRIVATE KEY ( FILE = 'c:\SQLBackup\MyDBCert.pvk',
-- You need the password used to backup the key
DECRYPTION BY PASSWORD = 'P4ssw0rd Us3D 2 BKup Th3 KEy' )
-- now you can restore the database
DBA: Where do I get that private key? on TechNet?
Microsoft chap: I ‘m afraid we can’t really help unless you have the key that you used to originally encrypt the database.
DBA: Surely you have a workaround, backdoor thingy?
Microsoft chap: Afraid not, because that would mean that we could look at the very data you were trying to protect from unauthorised access.
exit ex-DBA …
If you are using TDE the first thin you should do once you have made your key is backup the key like this
BACKUP CERTIFICATE MyDBCert TO FILE = 'c:\sqlbackup\MyDBCert.cer'
WITH PRIVATE KEY
( FILE = 'c:\sqlbackup\MyDBCert.pvk',
ENCRYPTION BY PASSWORD = 'P4ssw0rd Us3D 2 BKup Th3 KEy'
Use the key to encrypt a copy of your database and try to use the key to restore it to another location (which could just be another instance on your test server). Once you have got your head round that then you are ready to try it on the production database.
Remember to keep that key safe, like you would your backups, but NOT in the same location as that will again mean that your use of TDE is pointless as anyone with the key and the data has your data.
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)
Update for Windows Server 2008 x64 Edition (KB950636)
Update for Windows Server 2008 (KB950636)
One of the great things about working in Microsoft is the huge e-mail threads with where we share problems and resolve them, as you get to learn loads of tips and tricks. One of the nightmares of working at Microsoft is the huge e-mail threads with where we share problems and resolve them, as you have to wade though loads of stuff to find the good stuff.
So this is one little problem I came across from Mike McGeehan a Technology Solutions Specialist in New York, which was then fixed by Robert Bruckner in the SQL Server product team.
Top Tip. Mike was able to show us the problem he was having by using adventure works and then enclosing the report files based on that so we can all reproduce the problem.
Mike was trying to create a drill-through report whereby report A has a matrix on it and when you click on a cell it takes you to report B passing parameters on the way so that the right context comes up in report B.
Report A is a modified version of the Company sales report, to include a subtotal in its matrix format:
The report produces a total column as shown below:
When a user clicks on the detail for “Mountain Frames” and “Q2” (the blue circle), a second report is called and results in the following:
This was done by passing the values from the Company Sales report to the subreport:
However, when the user clicks on the subtotal column in the Company Sales report (the red circle), it seems to arbitrarily select “Q3” when passing parameters to the subreport.
Mike’s question was “Is there a way to pass all quarters to the subreport (wildcard?) in order to get all four quarters in the subreport?”
Robert fires up the report on his machine does some digging around and comes back with an answer in 2 hours
Currently, your drillthrough actions on the matrix cells pass a single value for Year, Qtr, etc. First you will need to modify your drillthrough target report so that a NULL value can be passed as Qtr etc. The meaning of passing a NULL value is that you want the data for all quarters – and you will need to write the dataset query accordingly in your drillthrough target report. Then, in the main report, modify the drillthrough action by using the InScope function to determine whether you want to pass a specific value (i.e. detail cell) or a NULL (i.e. subtotal cell). For example (YearGroup is the name of the matrix group that groups by OrderYear, etc.): pOrderYear: =iif(InScope(“YearGroup”), Fields!OrderYear.Value, Nothing) pOrderQtr: =iif(InScope(“QtrGroup”), Fields!OrderQtr.Value, Nothing)
Currently, your drillthrough actions on the matrix cells pass a single value for Year, Qtr, etc.
First you will need to modify your drillthrough target report so that a NULL value can be passed as Qtr etc. The meaning of passing a NULL value is that you want the data for all quarters – and you will need to write the dataset query accordingly in your drillthrough target report.
Then, in the main report, modify the drillthrough action by using the InScope function to determine whether you want to pass a specific value (i.e. detail cell) or a NULL (i.e. subtotal cell).
For example (YearGroup is the name of the matrix group that groups by OrderYear, etc.):
His parting shot is also important -
“BTW, Note that this kind of report layout is simplified in RS 2008 due to tablix – you have separate cell definitions for detail cells and subtotal cells and can then setup the drillthrough actions without the need to use the InScope function.”
I posted this for two reasons:
1. This might be something you are struggling with , but more importantly, 2. If you are stuck don’t struggle on alone, even if you are an ‘expert’ and work for Microsoft you don’t have all the answers, there’s always someone else who might have the answers.
1. This might be something you are struggling with , but more importantly,
2. If you are stuck don’t struggle on alone, even if you are an ‘expert’ and work for Microsoft you don’t have all the answers, there’s always someone else who might have the answers.
Of course not everyone works in a multi-billion dollar software company with technical resources coming out its ears, and this is why I am really keen on the many forums, and community sites out there such as the UK SQL Server community as they can give you the same kind of access to really good advice.