Azure for storage of backups is great its cheap and you can easily store them compressed and encrypted. If bandwidth isn’t an issue away you go.
Microsoft introduced Backup to URL for SQL 2014 and back ported it to SQL 2012 SP1 CU2. This is great as you can just run your backup and they get uploaded direct to azure.
If however you are using SQL 2012 prior to SP1 CU2 or a version of SQL prior to SQL 2012 then you aren’t lost.
Microsoft have developed a tool (Microsoft® SQL Server® Backup to Microsoft Azure®Tool) that works for all versions of SQL Server.
It installs a filter driver (like many of the backup compression vendors do) that intercepts requests for backup files and streams them to Azure Blog storage.
Whilst it does what it does its not exactly intuitive. You install it and configure it and then think what next. Thats because the UI is just for configuring the folders that the tool will “filter” on and redirect requests to azure. Once you’ve done that you then run a normal backup or restore, well sort of. The tool doesn’t give you an explanation and so I’ve provided you one here which should explain all you need.
The crux of how this works is the creation of two files, a meta data file and a contents file. You need the meta data file to be able to do the restore. This file is titchy and points to the main data file
"Trailer": "QZ0jQWlF4ymCLWn5OZJDoPRsWVQ0RPA1h2FywEvjwyX3n. . . Vh4OI2nizLgPC//OhR1IXmXhUFC0yIpuP1bDs=",
Backup to azure
- Configure a folder in this tool that SQL has access to be monitored by SQLBackup to Azure
- Now go in to SQL Server and run a backup as normal to the folder and it will be backed up to Azure
SQL will create 2 files,
- The first file with the name you specified. This contains the meta data of where the file will be backed up to in azure (but not with any credentials)
- The scond file contains the data and will have the same name with a suffix to match the type of backup,
- non compressed and non encrypted = .data
- compressed and non encrypted = .gz
- compressed and encrypted = .enc
Restore a backup to another machine
1. Configure a folder in this tool with the correct blog storage account and credentials (and password if using encrypted backups)
2. The meta data file
Download the file with the original name (i.e. no 1 from above) from blob storage to the folder you have configured in step 1.
3. Run your restore specifying the path to the file in the folder in step 2.
SQLBackupToAzure will intercept the request and stream the data from azure to SQL Server.
I’m very excited by to see the pubic preview of the Azure Key Vault service.
A blog has been started to cover the technical aspects and can be found here http://blogs.technet.com/b/kv/
One of the only things I’ve been speaking about in conferences recently has been compliance and encryption. https://www.sqlbits.com/Sessions/Event12/Achieving_PCI_with_SQL_Server-Dont_be_the_next_Sony
The most complex part of the the demos I do in that session is the key management story. Key management in SQL is possible but its complicated because keys are managed in the same store as the data and you have the challenge that a sysadmin has access to everything in SQL Server including the keys. That means duality is very difficult.
I’m working on a demo for how to use the Azure Key Vault instead of the using SQL Servers key management so keep posted.
If you are interested in trying it your self you can look at the technet documentation “Extensible Key Management Using Azure Key Vault (SQL Server)”
Was today directed to a nice little site giving the differences between different database systems. Its a nice succinct table of features
There is however an issue having yes and no answers to many of the features. A lot of them are grey, i.e. in SQL server you don’t have before triggers but you have instead of triggers.
The problem with this is also it gets out of date quickly. Although the fact that SQL Server has had user defined types based on CLR for almost a decade suggests the writer isn’t a SQL Server person.
It also believes that you can’t return the results of an OUTPUT clause as a result set.
So as with much content on the web, take it as guidance but if you are really looking for a feature then go check the relevant forums/official documentation.
There are always those problems that you hit and you feel like you are hitting your head against a wall. This was one of those situations.
Running builds in TFS and we were wanting to update the build version and checkin the result.
Issuing two simple commands to checkout and checkin the file were failing with
“Unable to determine the workspace. You may be able to correct this by running 'tf workspaces /collection:TeamProjectCollectionUrl'.”
Now I know that TFS doesn’t work well if the workspaces are screwed and so started looking into the problem.
Firstly we had just upgraded to TFS 2013 and a new TFS server. This has all sorts of problems with workspaces and solution files as the source control settings are cached and also in the solution and project files. Agghhhh where’s git when you want it.
The caching is easily solved by removing the cached files this can be done by removing
Where <version> is the version of TFS you are using TFS 2013 has a version of 5.0.
Note: you won’t be able to remove the folder if VS is running (on a dev machine) or the Team Foundation Server Build Service Host is running (on a build machine)
I tried this and still no luck.
After some bing juice, I came across some comments in http://blogs.msdn.com/b/granth/archive/2008/07/22/tf-exe-unable-to-determine-the-workspace.aspx that alluded to the PATH pointing to the wrong version of TF.exe
I changed my script to point to the explicit TF.exe and hey presto it worked.
On examining the PATH environment variable I saw that indeed it was pointing to the previous version of TFS tools.
“C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE”
Not sure why the install of TFS 2013 didn’t change the path, probably some back compatibility stuff. Suffice to say, what out if you have multiple versions of VS and TFS installed on your build and dev machines.
Are you trying to build a SQL Server database project and getting
The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
We had this recently when trying to build one SSDT solution but not when building another.
Checking the build agent the error was correct that file didn’t exist. But later versions did.
A nice feature of TFS is that on clicking the error in build it takes you to the exact point in the sqlproj file that the error relates to.
< Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
You will see that the path is made up of a number of components. Importantly $(VisualStudioVersion) defines the folder it looks for the targets file in.
Where is this property set?
We did some digging and found that the solution files differed.
The one that worked looked like this
Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio 2012
and the one that failed looked like this,
Microsoft Visual Studio Solution File, Format Version 11.00
# Visual Studio 2010
As I#m sure you’ve done we put 2 and 2 together and figured that $(VisualStudioVersion) was being set by VS based on the solution file version.
We changed the solution file that wasn’t working to point to Visual Studio 2012 and hey presto it all worked.
This means that if you have multiple versions of Visual Studio installed then you could end up with odd behaviour with automated builds.
There are a few things that are bad IMHO
1. there is no way in the IDE (that I can find) to see that the solution is targeted to Visual Studio 2010 and not Visual Studio 2012
2. no way to change it without editing the solution
3. the fact this property is in the solution and not the project.
Options you have as described in the blog post
1. Put <VisualStudioVersion>11.0</VisualStudioVersion> in a property group in your msbuild file
2. Put <VisualStudioVersion>11.0</VisualStudioVersion> in a property group in your sqlproj file
3. Pass as a property when calling msbuild i.e. /p:VisualStudioVersion=11.0
4. Create a new solution with a header matches the version of VS you want to use
5. Change the solution so the header matches the version of VS you want to use
Are you looking to go to SQLBits on the cheap. If so then we have arranged some great value accommodation.
£39 per night includes a full english breakfast at the Priorslee Rooms which is a 5 minutes from the venue.
For more details on accommodation options available visit the SQLBits accommodation page
If you’ve been reading the wires about SQL Azure you will have seen a change to the tiers that are being offered. Thsi is all so that you can get a more predictable performance level from the platform and also to enable Azure to manage the resources.
You now have 3 categories basic, standard and premium, and within those you have some other levels. This enables you to start your service at a low level and then as required request more capacity (through the portal or API).
For details on the performance aspects of the levels you can read here
Something to note is that the switch between levels may not be instant. This depends on what level you are moving from and to and the capacity available on the physical servers your DBs are currently residing on.
“An SLO change for a database often involves data movement and therefore many hours may elapse before the change request completes and the associated billing change becomes effective. Data movement occurs for changes when upgrading downgrading a database and may also occur when changing performance level of the database.”
If you’re data has to be moved then for a 150GB it could take 2 days to make the transition, 17 Hours for a 50GB DB, the rule is as follows
3 * (5 minutes + database size / 150 MB/minute)
For the exact details go to http://msdn.microsoft.com/library/azure/dn369872.aspx
As a note I’ve created a test DB as a standard edition. I was able to increase and decrease the level instantly. I was also able to increase the the database to a premium database instantly. This would have been due to there being capacity on the servers that the database resides. You cannot rely on this behaviour, it may or may not be instant.
It was sad to hear today about computermanuals.co.uk closing down after a period of administration.
Whilst I do love books, the access to technical information, of high quality on the internet and accessible on your PC does mean the printed technical book does look to be going the way of the dinosaur.
The silver lining is that you can get some books really cheap in their closing down sale
Our next SQLBits
event in Telford in July has topped the number of registrations for the Friday and we are still over 2 months to go. This is going to be our biggest regional SQLBits ever by a long way both in terms of attendees and sessions.
I’m currently in the planning for the Friday evening party and it will be a special event.
If you care about data and work with SQL Server the SQLBits XII is an event that you do not want to miss.
Its not too late to register but I’ve been told that accommodation is in short supply so make sure you make your booking soon.
My career started at a company where we hardly had email, the network was a 10base2 affair with cables running all around the office. You used floppy disks and the thought of a GB of data was absurd. You had to look after every byte and only keep what you really needed.
Whilst the cost of the spinning disks gradually falls the cost and size of flash storage continues to plummet.
The new Crucial SSD is £380 for 1TB
I can now keep 128GB of data on a SD card the size of my finger. It only costs $50 a month to store 1TB of data in Azure ($61.29 on Amazon S3 Europe)
This brings long with it a whole host of problems.
Its too cheap
Whilst before you had to manage your mailbox, your desktop, your database because you ran out of space.
Now its cheaper to keep data than it is to get rid of it,
Keeping data around leads to many problems, and considering the cost of the media is only a small part of the cost of maintaining the data
1. You have to manage it, i.e. Backups
2. You have to secure it.
3. The more you have of it the more risk there is of loosing some of it.
4. When in a database having more data affects your query performance.
5. Can your employees find the data they need, is it a game of find the needle in the hay stack,
6. Most data protection acts state you should only keep data for as long as you need it.
7. You are also liable to give customers copies of the data you have on them. Do you know all the data you have on someone. Even those notes the sales person wrote about someone in a onenote notebook.
Its just like the blob
The reason that this is a problem for many organisations is that its never something that is on the list of priority things when a company starts or a project starts. Its never a problem as there is always enough space when things start. However as time goes by the problem gets bigger and bigger until it becomes a problem.
When it becomes a problem its then such a big thing to deal with, to do the work, change attitudes, implement policy, that no one really has the appetite.
What are you doing about it?
Do you think about what you are storing and decide if you really need to store it?
Do you have a data retention policy?
Do you have a data deletion policy?
What about a data archive policy?
Are you actively reviewing what data you are holding?
Do your IT guys really push back when a team says we need 1TB of storage for project X?
Do your developers have data retention in their definition of done?
Deal with it sooner rather than later or it will be just too big to digest.
More Posts « Previous page
- Next page »