Thursday, May 27, 2010

Querying Active Directory via a Linked Server using ADSI: SQL Server 2008 R2

How to quickly set up a linked server for the purposes of querying Active directory:

sp_configure 'show advanced options', 1
reconfigure
GO

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO

sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADsDSOObject', 'adsdatasource'
GO 

Next check the properties of the newly created Linked Server. By default, the new Linked Server Definition will connect to Active Directory using the security context of the current user.

image

If you are using SQL Authentication, it may be necessary to hard-code a suitable account here that has access to the AD.

Here is a sample query to select some users from the AD. Be sure to fill in your domain name in the LDAP section of the query string.

SELECT TOP 50
 samAccountName AS AccountName,
 displayName AS DisplayName
FROM OPENQUERY(ADSI, 
    'SELECT samAccountName, 
    displayName 
    FROM ''LDAP://DC=au,DC=xyz,DC=local'' 
    WHERE objectClass=''user''') 

Wednesday, May 26, 2010

Installing SQL 2008 R2 – in just 25 Screenshots

I’ve been playing with SQL 2008 R2 for a while now, and just recently I installed the RTM version for a customer. The following screenshots give an indication of what is involved. There are a few more steps required than for SQL 2005 or 2008, but the process is very intuitive. Kudos to the guys who wrote the installer!

Note that this was for a dev environment, so some shortcuts were taken! (File placement, service accounts, etc….)

1

2

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Thursday, April 8, 2010

How Many Databases Can I Mirror?

There is a new article on SQLCAT which answers a commonly asked question about database mirroring. How many databases can I mirror from a single instance? The answer is, for 64 bit systems there is no limit. It all comes down to system resources. For 32 bit systems there is a recommendation of 10 databases, however this is only a rule of thumb.

Mirroring a Large Number of Databases in a Single SQL Server Instance

Friday, March 26, 2010

Multicore Awesomeness

I am currently working with a customer who is looking at deploying a bunch of HP Proliant DL785 G6 servers. These mega machines can hold up to 512 GB RAM, and support 8 AMD Opteron Processors, which have 6 cores each. That’s 48 processing cores in total. Staggering when I think back to my old 386 in the garage….

Tuesday, March 9, 2010

The Data Deluge

The Economist recently ran an excellent 14 page special report on the exponential growth of data. The far-reaching effects of data availability are fascinating – for example, when statistics on restaurants violating health regulations were published online, the problem largely resolved itself. Restaurants were forced to get their act together, or go out of business! Data is definitely the currency of the modern era.

The description of how Google uses feedback from users to refine its systems is also fascinating. Take for instance the spell checker – which is now accurate in over 50 languages, based on people clicking those “did you mean….” links. Compare that to Microsoft spending millions of dollars to build the conventional MS Word spellcheck, based on a complex set of rules. Sometimes an approximation based on statistics gets you close enough to the truth!

Tuesday, March 2, 2010

How to Change to Collation of a Database

Today I had the unpleasant task of changing the collation on several databases in a SQL 2005 environment. This essentially involves copying the data out, changing the collation of the underlying tables and then putting the data back again. Messy!

The ALTER DATABASE … COLLATE command gets you part way there. New objects will be created using the new collation, but what about all those object which were already present? Sadly they are not affected.

Luckily for me, Igor Kovalenko made this awesome script a few years back. It essentially moves your data out, changes the collation of the individual tables, and puts your data back where you expect it to be.

I then used RedGate SQLDataCompare to ensure that the data was consistent with a backup taken earlier on.

The only problem I hit with the script was with a VARCHAR(MAX) column. Changing the datatype to VARCHAR(8000) did the trick.

Monday, March 1, 2010

Server Name Mismatch

When I was configuring replication at a customer site today, I came across a problem where the internal name of a SQL Server didn’t match the machine name. I replicated (ha!) the problem on my laptop:

image

The message reads: “SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address or any other alternate name are not supported.”

To fix this problem, check the values of:

select serverproperty ('servername')

and

select @@SERVERNAME

As indicated in the error message, you will most likely find that they return different values. This is enough to confuse your Configure Distribution Wizard. I then used:

sp_dropserverincorrect_server_name’

sp_addservercorrect_server_name’, local

And restarted the SQL service for good measure. More info on these procs is available in books online.

In my case, the problem was caused by the SQL Server having been built from a dodgy machine image, and the windows server having been subsequently renamed. Something to be aware of when renaming a server!

Friday, February 26, 2010

Hello Cloud – Getting The Environment Ready

First, you need to download the Windows Azure Tools For Microsoft Visual Studio 2010 1.1 (February 2010). This comes down as the aptly named VSCloudService.exe. You might encounter the following error:

image

There are a few pre-requisites to install first. Click here to view the detailed readme file. Once this is complete, the installation proceeds…

image image

image

image

Launch Visual Studio 2010 RC1

image

File->New Project

Under Visual C#, go to Cloud. The option to create a Windows Azure Cloud Service is now enabled.

image

Playing with Windows Azure

I have been following SQL Azure pretty closely over the last few months. This week at Melbourne SQL Server Users Group, we had a presentation from David Lemphers from the Windows Azure team. To be honest I didn’t know a lot about Windows Azure prior to this session.

The architecture of Windows Azure is really neat – the ability to provision additional instances of your application, the separation of web and worker roles. The use of simple objects that we are all familiar with: blobs, queues and tables for storage. It all makes a lot of sense.

In my next few posts, I am going to document my experience as I attempt to write “Hello Cloud”. For this purpose I have downloaded Visual Studio 2010 RC1. My internet cap is really hurting right now.

image

Xobni – Neat add-in for Outlook 2007

At the moment I am testing out a little application called Xobni. It integrates with Outlook 2007 and does all sorts of useful things, like making it easy to find that attachment that some customer emailed you last week. Instead of trawling with the built in Outlook search, it presents a tidy context-sensitive list for you to click through. It also collects and displays interesting statistics on who emails you and when.

http://www.xobni.com/

A quiet day today…

mail traffic

Testing out Live Writer

Today I downloaded Windows Live Writer – the installation and integration with blogspot.com was pretty slick. Hopefully this will allow me to breathe some life back into my blog!