SQL Server 2008 R2 Build List

http://www.sqlservercentral.com/articles/SQL+Server+2008+R2/70092/

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.

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''') 

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….)

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

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….

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!

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.