June 2011 - Posts

Edinburgh User Group Meeting–Wednesday 29th June
26 June 11 09:01 AM | MartinBell | with no comments

On Wednesday we have two guest speakers at the Edinburgh SQL Server User Group Meeting at the Microsoft Offices at Waverley Gate. Tony Rogerson will be presenting a session on using NoSQL to produce a cheap high performance, highly scalable BI system and Colin Mackay will be presenting on SQL Injection Attacks and how to prevent them.

18:30 - 18:50 - Introduction, Networking and Food

18:50 - 19:40
How to get throughput of over 1GBytes per second for less than 2.5K using commodity kit - Tony Rogerson SQL MVP
In this session Tony will talk about the recent paradigm brought into play through the NoSQL movement for dealing with high performance, high scalability requirements but at a fraction of traditional costs. Tony will demonstrate a box built using commodity kit and will talk about how it may be used to good effect in a Business Intelligence setting. It will be an open discussion.

19:40 - 20:00 – Break

20:00 - 20:50SQL Injection Attacks (and how to prevent them) - Colin Angus Mackay
With recent reports of a man convicted of stealing the details of 130million credit cards by use of SQL Injection Attacks, isn't it time to find out how to defend your systems against them? In this talk Colin Mackay will show you what a SQL Injection Attack is, what they look like, how they work and most importantly how to harden your application and database security in order to defend your systems against them.

Although the technologies used in this talk are SQL Server and the .NET Framework, the general ideas presented apply to any database that uses SQL as a query language, and to any framework that may interact with that database.

Colin Angus Mackay
Colin Angus Mackay is a software developer specialising in Microsoft technologies located in Glasgow, Scotland. A former Microsoft MVP (C#) for four years running, the Treasurer of Scottish Developers, Code Project MVP for five years running, and has co-organised four DDD Scotland events. While not involved in software related pursuits is an amateur photographer (which generally involves wondering why his camera's autofocus mechanism chooses the potted plant off to the side rather than the main subject).

20:50 - 21:00 – Close

Pizza and drinks will be supplied in the breaks, and a feedback prize draw will be held.

Sign up for the event at SQLServerFAQ.com

Filed under:
Finding out how much space is used by filestream files
25 June 11 11:10 PM | MartinBell | with no comments

At the Manchester user group the other night a question from the floor was “How do you determine the space used by the Filestream files” so I thought I would look into it.     

I found the same question posted on Stack Overflow and the following was given as a means to get an approximate answer.

SELECT SUM(DATALENGTH(filestreamcolumn)) FROM filestreamtable;

A follow up reply also points you to the sys.database_files system view to retrieve the size of the filestream filegroup, but it also points out that it does not necessarily give you an accurate value of the total space used and it doesn't tell you the amount of space available on the volume. This seems like an ideal situation to use Powershell as it involves the file system and SQL Server, so I then looked into ways of doing it that way 

I found this article on Allen White’s blog which does most of the SMO I need to carry out the task, but to get to get the directory size I would need to do something like this script which I found on TechNet.
After putting it all together, I came up with this script. Most of my additional code is in the Filestream-Path procedure which uses the WMI to get the volume information, but before that I split the filegroup path into it’s ancestry using the split operator. This will then go through each ancestor from the oldest to the youngest and if volume information is returned then it must be a disk mount point. The last volume found will be the most recent ancestor.

When running this script make sure you will get errors if you don’t have sufficient permissions on the folders.

Filed under: ,

This Blog

SQL Blogs