Skip to main content


Showing posts from April, 2010

When was SQL Server last restarted?

How do you tell when your SQL instance was lasted restarted?I was reading a post by Joe Webb (Blog|Twitter) about a query he uses to to gather information about his clients SQL Server instance including how he find out when an instance of SQL Server was last restarted.You can find Joe’s script and his latest post here.He basically queries the sys.dm_exec_sessions to find the login time of the first session.As Joe mentions in his post there a number of ways to get this information. I use a slightly different method where I query sys.databases to get the create time of tempdb…Tempdb db is recreated each time SQL Server is restarted so the created date time of TEMPDB will also give you the date and time of the last restart of the instance. My query looks like this:SELECT create_date
FROM sys.databases
WHERE database_id = 2 --TEMPDB's database_id

which produces the following result:

SQL Server Connection Strings

The purpose of this short post is to demonstrate a simple method of generating an accurate SQL Server connection string for your SQL Server servers and databases.  This tip was first showed to me by a friend of mine, Paul Blackwell, a while back and I wanted to share it with you here. As pre-requisite to this tip you need to be using a windows based operating system. In order to build our SQL Server connection string we need to clear all open windows and have the desktop in front of us… so click the <show desktop> button <Right click> on the desk top Select <New> <Text Document>Call it <sqlconn> Open the new text document called <sqlconn>on your desktop When the document is open select <File><Save As> Keep the file name the same but change the <save as type> to all files. After the file name add the extension .udl like in the screen shot below and click <Save>There will now be a second item on the desktop called <sqlconn&g…

SQL Server Replication Overview

For my next series of blog posts I intend to document and write about SQL Server replication. This is a pretty large topic and I make no claims to an expert in replication but over the last couple years I have been working with replication more and more and I think it is time I wrote some blog posts on the topic to document and reinforce my understanding of the subject and  hopefully learn a lot along the way.
SQL Server replication is a technology  that can be used to  distribute and update data from one database to another and also synchronising the databases to maintain consistency.
SQL Server offers the following types of Replication:Snapshot Replication - a snapshot of the database is taken at a point in time and the whole data set it rather than individual transactions is applied to the subscriber. This is not a continuous process so the subscribing database always has some latency when compared to the publishing database.Transactional Replication - Transactional replication allo…

Microsoft Teleconference – Ted Kummert (Senior VP) and Tom Casey (General Manager of BI)

Microsoft  held a big teleconference today, which I unfortunately got called away from a minutes into it. The call was basically the launch of SQL Server R2. There are numerous posts from floating around from some high profile community members:Brent Ozar’s post is hereMr Denny’s Post is hereFrom what I can gather there is an evaluation copy available to download today, Technet and MSDN subscribers get it on 3rd May and Volume License customers get it later on the 13th may. Check out the above posts for more detailed, probably more accurate info though, I did miss most of it after all.

Virtualisation – An Introduction

For the last two years or so, in one capacity or another I have been running SQL Server in virtual land and I wanted to start writing some blog posts on the subject .I thought I would start providing  a definition of virtualisation and then I thought better of it and I decided to leave it for the virtualisation experts, Scott Lowe in his book Mastering VMware VSphere 4 (you can find his blog here) provides a great definition of virtualisation:“Virtualisation is the abstraction of one computing resource from another computing resource...when most information technology professionals think of Virtualisation , they think of hardware Virtualisation : abstracting the operating system from the underlying hardware upon which it runs and thus enabling multiple operating systems to run simultaneously on the same physical server.”So the crux of virtualisation is  thus…You have one ( more likely many) relatively powerful server that act as the physical hosts with a hypervisor technology installe…

Third Party Backup Tools – Redgate SQL Backup

To continue my little blog series on SQL Server backups, any series would not be complete without a look at some of the third party products that exist to supplement a backup plan with additional functionality not always offered in the native SQL Server backup.
My favourite tool and the backup product I have used the most over the years is Redgate’s SQL backup, there are other tools out there such as Hyperbac (Actually this has just been acquired by Redgate) and Litespeed, but over the years, for one reason or another I have used Redgate and have been very pleased with it.
Reasons for using Third Party Tools“SQL ships with its own backup utility!” I hear you cry. “Why would you spend some extra $ on a third party tool when out of the box SQL Server can do this stuff for you?” Well I have a couple of reasons:
Compression – The main reason I started using Redgate backup way back on SQL Server 2000 was due to its ability to compress the backups it took. This remained true for SQL Server 2…

T-SQL Tuesday #005 – Offloading Reporting (#TSQL2sDay)

Aaron Nelson (Blog|@SQLvariant) is hosting this months #TSQL2sDay with the theme of reporting. My post looks at some of things I have done in the past to offload reporting to another server to reduce the overhead of running reports against production OLTP databases.
A few years ago, back when I was implementing a HA solution in SQL Server 2000 utilising clustering to prevent against node failure  and Log shipping to maintain an off-site, second copy of the database. The organisation wanted to implement a reporting and BI solution so that management information could be made available on demand for selected reports and also to load a data warehouse. Even though we copied the logs over to the standby server they were only restored once a day, so it was possible for the logged shipped DB to be a day behind. If we ever needed to recover the db in the event of a failover at most one days worth of logs would need to be applied before the database could be used and the day's latency was …

db_executor role

The process of creating a db_executor might not be granular enough for some but there is a nice simple way of creating a database role in a SQL Server database (called db_executor)  and granting the db_executor role  execute permissions on all stored procedures, then when you want to give someone execute permissions to all stored procedures you simply add them to the db_executor role. As I have already said this may not be for everyone but I have used several times for various clients.

I will use the Adventure database as example here:

First we will create a role in the database called db_executor:
USE [AdventureWorks] GOCREATEROLE [db_executor] AUTHORIZATION [dbo] GO

The next step is to grant the db_executor role execute permissions, which can be done as follows:

--GRANTExecute Permission on the roleGRANTEXECUTETO [db_executor] GO

We will next create a login and user that can be added to the db_executor role in Adventureworks to pick up the permissions:

Create the login:

USE [master] GOCR…

Q1 - review

I didn’t post my professional goals for the year on my blog at the start of this year, my blog was new and I wasn’t sure that it was appropriate. Instead I made a written note in my paper based diary – I still keep one of those, not for managing my appointments or scheduling my time, I have an iPhone for that and it handles it just fine. I find it good for scribbling notes and ideas down when I don’t have my laptop or computer at hand, and if you do it properly it keeps a note of the date you wrote the notes too. Having read everybody else’s goals though,  I will definitely publish next years goals here at the end of this year.
One of the initial goals was to publish on my blog regularly…when I wrote that down and reviewed it I saw that this was not really a measurable target and I needed to put some numbers next to it to be able to measure it successfully. So I thought about and I aimed to post at least once preferably twice a week, every week. This would mean that I would have betw…

Uninstall 64 bit and reinstall 32 bit SQL Server

For reason's beyond the scope of this post I needed to uninstall a 64 bit version of SQL server standard edition (Including Integration Services ) and re-install the 32 bit SQL Server standard edition. The uninstall seemed to complete successfully so I went on my merry way and I attempted the re-install of the 32 bit version: The database engine installed perfectly well but when the installation got to Integration services, it  failed with the following error: Failed to install and configure assemblies E:\Program Files (x86)\Microsoft SQL Server (x86)\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: One or more of the components being installed are already registered as 64 bit components in the target application. You must install the 32 bit versions of the components being installed in a different COM+ application, or delete the existing 64 bit versions of the components bei…

Differential Backups

My last few posts have been backup orientated, I have a post on Full Backup, a post on Transaction Log backups and a note on how to manage backup files. The next in my little series on  backups is a short post on differential backups in SQL Server.
What is a Differential BackupA differential backup in SQL Server is a backup of all changes made to the database since the last full backup was taken.
How would you use a Differential Backup?If you have large database that changes by a small amount each day, then to narrow you backup windows and possibly your restore time you make use of differential backups for example if you currently take a full daily database backup of your 250GB database you could possibly reduce the backup window for this database during week by switching the full daily backup to a full weekly backup and taking a differential backup each day in place of the full backup
Or if point in time recovery is important to you, it is for many people, and you take a high frequency…

Powershell – Useful Resources

One of my aims at the start of the year was to improve my Powershell and scripting skills. I run a few Google searches looking for resources and had plenty of results returned, but finding the ‘wheat from the chaff’ proved difficult. So I turned to twitter and asked the SQL community using the #SQLHELP hash tag. As an aside if you have a SQL problem and need an instance response and you can formulate a question in less than 140 characters add #sqlhelp to the end of your post and you’ll get a whole bunch of help…I digress. I got a whole heap of replies and I thought I would formulate the URLs in a txt document for future reference, then I realised I should post them on my blog so everyone can use them and here they are:Useful Powershell URL's…