Skip to main content

Posts

Showing posts from 2011

Happy New Year

Image: ponsuwan / FreeDigitalPhotos.netAs 2011 draws to a close and as people look forward to evening of partying and celebrating I’d just like to wish everyone a very happy, healthy and prosperous New Year.

Contracting is business not employment: Read the small print

This post is about a contracting in UK. Some of terms mentioned here will not mean a great deal to people who do not understand the mechanics of contracting or the UK tax systems.
I recently got sent an email by a friend and fellow contractor that contained a link to the following news article:
http://news.efinancialcareers.com/72048/the-outrageous-email-allegedly-sent-by-bank-of-america-merrill-lynch-to-its-contractors-in-london/
Feel free to go away and have a read of the article but the gist is this. A well known global financial institution has decided that its is possibly paying over the odds for its contractors and as such it is enforcing a 10% rate cut across the board in an effort to cut costs. It doesn’t want to lose the contractors but it does want to reduce the amount it pays for them and hence reduce the cost of the contractors.
The feeling of the comments left on the post seem to imply that people are outraged by such a move. The survey results seem to suggest opinion is spl…

Execute Permissions

Written by Ian TreasureGethyn posted on the use of execute permissions a while ago (db_executor at http://www.gethynellis.com/2010/04/dbexecutor-role.html). Briefly, this approach uses a role – db_Executor – to which users are given membership. The execute permission is granted to the role.

I am using this approach to control access to stored procedures. Some developers started to complain that they could not execute their stored procedures. The first thing that I did was to check if their user was a member of the db_Executor role. It was, which was a surprise. I then started to check that the role had executor permissions. This was not so easy to do.

After a little digging, I used sp_helpprotect as follows.

In SQL Server Management studio, I ran:

sp_helprotect 'execute'



This returns the following:

Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 291
There are no matching rows on which to report.

OK – now I know that the problem is because the database role does not have ex…

Contracting, SQL Server and gethynellis.com

A while back I started posting some contractor related articles.The articles were mainly for the those people that wanted to start their own business and work for themselves as a contractor. I work in the IT sector through my own company with several clients both based in the UK and across the pond in the US. I have been running my company and working for myself for almost five years now and I even through one of the most difficult economic times  in modern history I have thoroughly enjoyed it and I wouldn’t change  anything that has happened thus far.  A while back I started a new section of gethynellis.com that I intended to have a focus on contracting and also some career based themes. At the time I thought it would useful to split up the SQL Server content that usually gets published on gethynellis.com from the contractor/career type posts. This week for a number of reasons I have decided that was not such a good idea.My reasons for splitting at the time were simple, the two  peop…

Destroying a Windows Cluster

I haven’t posted for a while but thought I’d dust off the blog and get a post out. I have a pipeline of blogs from David and Ian waiting to be scheduled and they should be out before Christmas.Warning – Do not do this on your production boxesI have been doing some work, first with CTP3 and now RC0 and Availability Groups. For those of you that don’t know you need a windows cluster configured to use AlwaysOn Availability Groups. I wanted to remove the cluster from my virtual nodes…so I could rebuild it. That’s a long story. I thought, before I started that removing the cluster would be difficult. As it turns out it is very simple in Windows 2008R2.To destroy you cluster from Failover Cluster Manager right-click the name of your Windows cluster, scroll down to other tasks and select <Destroy Cluster> You get a warning which, if you accept then goes away and removes the cluster from all nodes in your cluster…Its gone!As I said don’t do this on your production boxes but if you ever …

SQLBits Friday and Saturday

By David PostlethwaiteThe last two days of SQL Bits provide opportunities to hear short 50 minutes presentations from experts
on a diverse range of SQL related subjects.Friday I stuck to a DBA theme.
I’ve heard Simon Sabin talk on performance tuning at the last SQL bits but there’s no harm hearing it
again there’s always something you missed last time. He certainly hates scalar functions.
David Morrison went through query plan operators which again although you’ve heard them all before,
you pick up something new.
Both covered similar ground and the overall view is that there are many ways to write a query to
achieve the same output, exactly what code you finally use will depend on many factors like the amount
of data, the cardinality of the data ,the available indexes, new indexes . You’ve just got to keep looking
at the query plan, understand it and change the query until you get acceptable results.
And finally Klaus Aschenbrenner on SQL 2008 troubleshooting looking at all those DMVs and how t…

SQL Bits Training Day

By David PostlethwaiteJust spent a day with Allen White, a SQL MVP from Ohio, USA learning about automating and managing SQL Server using PowerShellPowerShell is ubiquitous in Windows now and can be used to view and manage any part of the Windows systems, it uses the .NET framework which, for me who has written plenty of c#,  made it much easier to understand. One of the things I liked about PowerShell was the way that objects like the registry and SQL Server can be browsed just like a disk drive.The real winner, not just for PowerShell but for .NET as well, is SQL Server Management Objects or SMO, a whole class of functions that can be used to interrogate SQL Server. I hadn’t come across this before but I could see so many ways that I could use it for retrieving information and managing SQL Server. An example was “how would you find all varchar fields in all databases in several instances and change them to varchar(max). A nightmare with t-sql but with SMO quite easy.We also saw exam…

David’s SQLBITS Day 1–The Travel Day

Just had a pleasurable 5 hours watching the Autumn countryside go by on a glorious sunny day (in fact its warmer at the end of September than it was for most of July and August).Where am I heading you ask? Liverpool the home of the Beatles, the Mersey Ferry and Nerris Hughes. I’m here for SQLBits, a 3 day biannual conference held in the UK for everything SQL Server.This is my 2nd time to SQLBits, the last being in March at the Grand Hotel in Brighton.Thursday is a training day where one studies a particular topic in depth. Friday  has a choice of 30 different sessions. Saturday has another 30 sessions but this day is free to attendees.On Thursday I’m looking forward to getting to grips with PowerShell. As well as being a SQL DBA I also write a lot of ASP.NET. I’ve written quite a comprehensive web site to audit and troubleshoot my SQL Server estate but I’ve wanted to learn PowerShell to allow me to run quick checks and automation commands just like I used to do with DOS in days gone b…

Sharon Dooley’s Lightening Talk and the WIT Luncheon

At the end of last week I received an email from my friend and colleague Sharon Dooley telling me her “Lightening Talk” had been accepted and she would be speaking at the Summit in October. Sharon has many years experience in the IT industry, a former SQL Server MVP, currently working as a trainer and course author specializing in SQL Server. She is a volunteer with PASS and is the Virtual Chapter Lead for the DBA virtual chapter. Sharon is also involved in the Women in Technology virtual chapter. A couple of weeks ago Sharon and I were chatting about the PASS Summit and even though she hadn’t submitted a full session she was considering submitting a lightening talk. Sharon doesn’t currently maintain a blog so I said if she did submit a talk and it was accepted I would promote her “Lightening Talk.” Sharon has a really awesome query that returns a user’s permissions; she will be demonstrating this query during her talk. She also promised to share the query with us after the Summit so …

Using PowerShell to Monitor SQL Server

By Ian TreasureOver the past few years, I have used a number of approaches to monitor SQL Server. For example, I have configured jobs to e-mail the DBA team if they fail. I have written programs to check that everything is OK. I have used a number of third-party tools to monitor my instances. Each approach has its own strengths and weaknesses. If the SQL Server Agent is not running, the jobs don’t run and I don’t get any messages telling me that they have failed. To stop this happening, I have used server monitoring tools for example  Big Brother http://www.bb4.org/ which can warn if a service has stopped. This sort of tool is pretty good, but you need to invest time and effort setting it up. The same applies to monitoring tools like Ideas Diagnostic Manager or Red Gates SQL Monitor. Also, these tools can cost a lot of money and can themselves affect performance of the server.

So I want to find a tool to monitor SQL that is independent of SQL Server, free and easy to use. PowerShell fi…

VMFS LUN resizing options in vSphere

IntroductionFirst off, many thanks to Gethyn for the opportunity to post on his excellent blog. I’m Christian Dadswell and I am a Senior Infrastructure Analyst at the Intellectual Property Office. I have 15+ years cross platform IT experience and currently support and maintain, amongst other things, the vSphere infrastructure from SAN (fabric and storage) thru vSphere to the virtual machines. In this article it is my intention to give you some options for resizing a VMFS LUN to allow you to increase the size of a Windows VM volume. It will also cover a scenario whereby a VM has been frozen in operation due to lack of disk space. Previous experience has shown me that VM’s occasionally require more space for whatever reason. This happens more often than not with SQL Server VM’s. Sorry, but you SQL DBA types love to eat SAN space. But seriously if a VM needs more space for its OS or data volumes something will need to be done. To kick things off I will create a couple of scenarios that …

Get your AD information with .NET

.NET has a useful class called system.DirectoryServices to query LDAP such as Active DirectoryHere is some example code to retrieve all the AD information for a user called FullName,On a web site, your Active Directory Name can be found using FullName = HttpContext.Current.Request.ServerVariables["AUTH_USER"];(If you are not using AD then this will return a blank)In this procedure we first write the results to a data table. we can then sort the results before appending them to a stringbuilder which is then return to the web page.using System.DirectoryServices;publicstaticString GetUserInfo(string Fullname)        {DataTable UserInfo = newDataTable();            UserInfo.Columns.Add("Name", typeof(string));            UserInfo.Columns.Add("Info", typeof(string));            System.Text.StringBuilder sb = new System.Text.StringBuilder();//extract the user name from DomainNamestring username = Fullname.Substring(Fullname.IndexOf("\\") + 1);//create…

Another New Contributor–Christian Dadswell

This is just a short post to introduce you all to my good friend Christian Dadswell who has kindly agreed to write guest post for gethynellis.com.Christian has a wealth of experience in IT especially in the field of virtualisation. I had the pleasure of working with Christian on some recent projects to virtualise a SQL Server estateChristian’s post will be focusing on resizing LUNS in vSphere…a subject which we are all likely to have interest in.As a hobby Christian writes and develops mobile apps for the Android market. I’m told that he is actually very good it. His BBC Browse Cast application had in the region of 20,000 downloads the last time I checked with him… It is probably much more by now.Chris’ post will be published in the next couple of weeks

Auditing SQL Logins Addendum

One thing that has come out of auditing successful logins is the number of applications called ".Net SqlClient Data Provider". This is slightly annoying because we don't want to audit a generic name because you won't then pick a rogue application.This can be easily fixed by adding " Application Name=AppName" to the connection string of the .NET application.i.e.connectionString="Data Source=Server1;Initial Catalog=master;Integrated Security=SSPI; Application Name=Application1;" providerName="System.Data.SqlClient

Auditing SQL Logins Part II

Written by David PostlethwaiteIn a previous blog  I talked about how to audit successful logins. So what about failed logins?As mentioned before logon Auditing can be configured under server properties. Here you can audit failed logins..This puts the audit records into the Windows event log but a more useful solution would be to have these failed logins written to a table.It turns out to be quite simple using the master..xp_ReadErrorLog command which reads from the Windows Event log.So first let's create a table to store the resultsCREATETABLE [dbo].[lv_Failed_Logins](      [Failed_Date] [datetime] NULL,      [Failed_Userid] [varchar](50)NULL,      [Failed_Hostname] [varchar](50)NULL,      [Failed_ServerName] [varchar](100)NULL)ON [PRIMARY]In this example we will run a scheduled job every night to write the failed logins into a tableFirst we will cycle the error log using the following command. This makes the current log file an archive file and creates a new empty log.sp_cycle_e…

Putting a Database into Single User Mode

I was recently discussing with a group of budding DBA’s how you go about performing a restore of database. They were trying the restore for themselves  and a few of them got an error about not having exclusive access:Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
This was a test setup and obviously we had other sessions connecting to the databases. these connections were  preventing the restore.If you are restoring a database and you have other sessions connected it can be quite simple to kill those connections in one go  by putting the database into single user mode and using the ‘ROLLBACK IMMEDIATE’ switch. This will rollback any open transactions and allow you to restore the database. I like to wrap this around the restore statementalterdatabase TestDB set single_user withrollbackimmediate

RESTOREDATABASE [TestDB]
FROMDISK = N'C:\Data\SQLBackup\…

Audit SQL Logins

Written by David Postlethwaite There are several ways that you can audit who has logged in to your SQL Server. The easiest way is to turn on logon Auditing under server properties. Here you can audit failed logins, successful logins, or both. This will put the audit records into the Windows event log. The failed logins is pretty useful because there shouldn't be many of them. But the successful logins turns out not to be so helpful because it records every single login which includes the service account. It turns out this logs into the server every few seconds so you end up with so much data in the event log that it's impossible to find anything else, especially the rogue user. The Enterprise version has a built in auditing option which writes to a user defined event log but again this still suffers the same problem in that it audits every login. What would be better is a way of selectively auditing only those logins we want to see (or not recording those we aren't interested …