Automate Import of Active Directory Records into SQL Server

Previously we detailed how to query Active Directory with the help of a linked server in SQL Server. Now let’s take it a step further by automating the process of retrieving the records from Active Directory and storing them in a SQL Server database using a job schedule.

To automate the import and refresh of Active Directory records into a SQL Server database, do the following:

  • Setup a linked server for connection to Active Directory
  • Create a store procedure to import records from Active Directory
  • Create a SQL Server Agent job to execute the store procedure

1. Setup a linked server for connection to Active Directory

If you have not already setup a linked server in SQL Server to connect to Active Directory, then refer to Query Active Directory in SQL Server with Linked Server for detailed steps.

2. Create a store procedure to import records from Active Directory

Now let’s create a T-SQL store procedure that will import the records from Active Directory. It must do the following:

  • Check to see if the required tables exist and create them if not.
  • Remove all data from the temp AD table if exists before importing
  • Query Active Directory and insert the returned records into a temp table
  • Refresh the current live table with the newly imported data
  • If there are issues, rollback the transaction

To create the store procedure, follow these steps:

  • In Microsoft SQL Server Management Studio, click on the database where the new store procedure will reside, then click New Query
  • In the new query window, enter the T-SQL script found below.
  • Save this new store procedure as usp_<your-store-procedure-name>
  • Confirm that the store procedure was created by:
    • Expanding Programmability > Stored Procedures
-- store procedure t-sql script, save as usp_<your-store-procedure-name>
USE <your-sp-database-name>
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.usp_<your-store-procedure-name>

AS
SET NOCOUNT ON;

IF OBJECT_ID('<your-ad-table-name>temp', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.<your-ad-table-name>temp
  (
    SAMAccountName nvarchar(800) NULL,
    givenName nvarchar(800) NULL,
    sn nvarchar(800) NULL,
    mail nvarchar(800) NULL,
    telephoneNumber nvarchar(800) NULL
  );
END

IF OBJECT_ID('<your-ad-table-name>', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.<your-ad-table-name>
  (
    SAMAccountName nvarchar(800) NULL,
    givenName nvarchar(800) NULL,
    sn nvarchar(800) NULL,
    mail nvarchar(800) NULL,
    telephoneNumber nvarchar(800) NULL
  );
END

BEGIN TRY
  BEGIN TRANSACTION
    IF EXISTS (SELECT SAMAccountName from dbo.<your-ad-table-name>temp)
      DELETE FROM dbo.<your-ad-table-name>temp;

    INSERT INTO dbo.<your-ad-table-name>temp (SAMAccountName,givenName,sn,mail,telephoneNumber)
    SELECT * FROM OpenQuery (
      ADSI,
      'SELECT telephoneNumber,mail,sn,givenName,SAMAccountName
       FROM ''LDAP://your.domain.com/OU=<your-ou>,DC=your,DC=domain,DC=com''
       WHERE objectClass = ''user''
       AND NOT objectClass = ''computer''
      ') AS tblADS
      ORDER BY SAMAccountName

    EXEC sp_rename '<your-ad-table-name>', '<your-ad-table-name>trans'
    EXEC sp_rename '<your-ad-table-name>temp', '<your-ad-table-name>'
    EXEC sp_rename '<your-ad-table-name>trans', '<your-ad-table-name>temp'

  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  ROLLBACK
END CATCH;

  

3. Create a SQL Server Agent job to execute the store procedure

Finally let’s create a SQL Server Agent job to automate the above steps. Here’s what you need to do within Microsoft SQL Server Management Studio:

  • Right click Jobs under SQL Server Agent, and select New Job…
  • Under the General page, enter the following:
    • Name
    • Owner
  • On the Steps page, click New… and enter or select the following:
    • Step name: Import Records
    • Type: Transact-SQL script (TSQL)
    • Database: master
    • Command:
      USE <your-store-procedure-database-name>
      GO
      EXEC dbo.usp_<your-store-procedure-name>
      GO
    • Click OK
  • On the Schedules page, click New… and enter or select the following:
    • Name: <Your schedule name>
    • Frequency: <Your frequency>
    • Daily Frequency : <Your daily frequency>
    • Duration: <Your duration>
    • Click OK
  • Confirm the AD table was populated with records from Active Directory after job has ran successfully as per your schedule.

Advertisements

How to Reorganize and Rebuild Database Indexes Using T-SQL

If you’re working with MS SQL Server 2008 databases, here are some handy tips on how to reorganize and rebuild indexes that are fragmented using T-SQL.

1. Determine Index Fragmentation

Firstly let’s determine the degree of fragmentation. We can either determine the fragmentation for all tables or a specific table in a database.

1a Determine fragmentation for all tables in a database


USE AdventureWorks2012;
GO

SELECT
  OBJECT_NAME(ips.OBJECT_ID)
  ,i.NAME ,ips.index_id ,index_type_desc
  ,avg_fragmentation_in_percent
  ,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

1b Determine fragmentation for a specific table in a database


USE AdventureWorks2012;
GO

SELECT 
  a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
GO

The index’s fragmentation determines whether to use reorganize or rebuild:

  • Between 5% to 30%, use reorganize
  • Greater than 30%, use rebuild

2. Reorganize a Fragmented Index

If the fragmentation is between 5 and 30 percentage, then let’s reorganize the index. We can reorganize all indexes or just a specific index within a table.

2a Reorganize all indexes in a table


-- Reorganize all indexes on the HumanResources.Employee table.
USE AdventureWorks2012;
GO

ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ;
GO

2b Reorganize a single index in a table


-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode
-- index on the HumanResources.Employee table.
USE AdventureWorks2012;
GO

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
ON HumanResources.Employee
REORGANIZE
GO

3 Rebuild a Fragmented Index

If the fragmentation is over 30 percentage, then let’s rebuild the index. We can rebuild all indexes or just a specific index within a table.

3a Rebuild all indexes in a table


USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO

3b Rebuild a single index in a table


USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON dbo.Employee
REBUILD;
GO

4 Potential Issues

A potential issue you may encounter is related to page level locking. This will prevent a reorganize operation on an index if page level locking is disabled. You will see this error:

The index “<index-name>” (partition 1) on table “<table-name>” cannot be reorganized because page level locking is disabled.

To get around the above error, temporary enable page level locking, perform the database index reorganize, and then disable page level locking.


-- Enable row and page level locks
USE [database-name]
GO

ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_PAGE_LOCKS = ON )
ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_ROW_LOCKS = ON )

--Disable row and page level locks
USE [database-name]
GO

ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_PAGE_LOCKS = OFF )
ALTER INDEX [index-name] ON [schema-name].[table-name] SET ( ALLOW_ROW_LOCKS = OFF )

If you want to check the index setting for a particular table (i.e. to see whether page level lock is enabled or disabled), use this query:


SELECT
  Indx.name, Indx.type_desc, Indx.is_disabled,
  Indx.allow_page_locks,Indx .allow_row_locks
FROM sys.indexes Indx
LEFT OUTER JOIN sys.objects OBJ ON OBJ.object_id=Indx.Object_id
WHERE OBJ.name ='Product'

Quick Tip on using ColdFusion cfquery in cfscript

We’re all familiar with using cfquery to query databases in ColdFusion. But what is its equivalent in cfscript? Here are a few query examples to illustrate. For each example we will show the cfquery and its equivalent in cfscript. For these examples we will be performing the CRUD operations on the clients database.

1. Create (i.e. INSERT into the database)

Let’s start with the cfquery tag version:

<cfquery name="qInsert" datasource="clientsDS">
  INSERT INTO clients
  (
    title,
    firstname,
    lastname,
    email,
    phone
  )
  VALUES
  (
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  )
</cfquery>

Here’s the cfscript equivalent:

<cfscript>
  qService = new query();
  qService.setDatasource("clientsDS");
  qService.setName("qInsert");
  qService.addParam(name="title", value="#trim(form.title)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="firstname", value="#trim(form.firstname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="lastname", value="#trim(form.lastname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar");
  qService.setSql("
    INSERT INTO clients
    (title, firstname, lastname, email, phone)
    VALUES
    (:title, :firstname, :lastname, :email, :phone)
  ");
   qService.execute();
</cfscript>

2 Read (i.e. SELECT from the database)

Let’s start with the cfquery tag version:

<cfquery name="qGet" datasource="clientsDS">
  SELECT 
    title, firstname, lastname, email, phone
  FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

  qService = new query(); 
  qService.setDatasource("clientsDS"); 
  qService.setName("qGet"); 
  qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
  qService.setSql("
   SELECT title, firstname, lastname, email, phone
   FROM clients
   WHERE clientID = :clientID
  ");
  results = qService.execute().getResult();

3 Update (i.e. Update the database)

Let’s start with the cfquery tag version:

<cfquery name="qUpdate" datasource="clientsDS">
  UPDATE clients
  SET
    email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qUpdate");
 qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
 qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar")
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
  UPDATE clients
  SET
   email = :email, 
   phone = :phone
  WHERE clientID = :clientID
 ");
 qService.execute();

4 Delete (i.e. DELETE from the database)

Let’s start with the cfquery tag version:

<cfquery name="qDelete" datasource="clients">
  DELETE FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qDelete");
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
   DELETE FROM clients
   WHERE clientID = :clientID
 ");
 qService.execute();

Generate T-SQL for restoring multiple databases

Let’s say we are migrating the database server to a new host. After SQL Server has been successfully installed on the new host, we need to migrate the databases. Let’s say we need to restore well over 100 databases. A better way to do this is to generate the T-SQL restore scripts from the backup files, including the transaction logs.

The following assumptions are made when creating the scripts:

  • Full backup of all user databases daily
  • Transaction log backup every 12 hours at a specified hour
  • A subfolder is created for each database to store the backup files (.bak and .trn)
  • File extensions are:
    • .bak – full backup
    • .trn – transaction log

Here’s the script that will generate the required T-SQL script to restore all the user databases from the full backup and transaction log files. Plesae note that this script is based on someone else’s creation. I can’t remember who it was. But full credit to him. I have adapted his script for my purposes.

USE Master;
GO
-- turn off (x row(s) affected) messages
SET NOCOUNT ON;

DECLARE @BasePath varchar(1000);
DECLARE @Path varchar(1000);
DECLARE @FullPath varchar(2000);
DECLARE @Id int;
DECLARE @todayDate varchar(20);
DECLARE @yesterdayDate varchar(20);
DECLARE @fName varchar (500);
DECLARE @fExt varchar(10);
DECLARE @dbName varchar(200);
DECLARE @cmd varchar(500);

-- set backup file's date in the format yyyy_mm_dd. we restore yesterday's backups
SET @yesterdayDate = replace(convert(NVARCHAR, dateadd(day,-1, getdate()), 111), '/', '_');

--This is the backup parent folder. Can use drive-folder or UNC notation
SET @BasePath = '<enter-your-full-db-backup-path-here>';

--Create a temp table to hold the results. remove the table if exists
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
   ,fullpath varchar(2000)
   ,fileName nvarchar(512)
   ,fileExt varchar(10)
   ,dbName varchar (200)
   ,depth int
   ,isfile bit
);

--Create a clustered index to keep everything in order.
ALTER TABLE #DirectoryTree
   ADD CONSTRAINT PK_DirectoryTree PRIMARY KEY CLUSTERED (id);

--Populate the table using the initial base path.
INSERT #DirectoryTree (fileName,depth,isfile)
EXEC master.sys.xp_dirtree @BasePath,1,1;

UPDATE #DirectoryTree SET fullpath = @BasePath;

--Loop through the table as long as there are still folders to process.
WHILE EXISTS (SELECT id FROM #DirectoryTree WHERE isfile = 0)
BEGIN
   --Select the first row that is a folder.
   SELECT TOP (1)
      @Id = id
      ,@FullPath = fullpath
      ,@Path = @BasePath + '\' + fileName FROM #DirectoryTree WHERE isfile = 0;

   IF @FullPath = @Path
   BEGIN
      --Do this section if the we are still in the same folder.
      INSERT #DirectoryTree (fileName,depth,isfile)
      EXEC master.sys.xp_dirtree @Path,1,1;

      UPDATE #DirectoryTree
         SET fullpath = @Path,
         fileExt = RIGHT(fileName,3)
      WHERE fullpath IS NULL;

      --Delete the processed folder.
      DELETE FROM #DirectoryTree WHERE id = @Id;
   END
   ELSE
   BEGIN
      --Do this section if we need to jump down into another subfolder.
      SET @BasePath = @FullPath;

      --Select the first row that is a folder.
      SELECT TOP (1)
         @Id = id
         ,@FullPath = fullpath
         ,@Path = @BasePath + '\' + fileName
         ,@dbName = fileName
      FROM #DirectoryTree WHERE isfile = 0;

      INSERT #DirectoryTree (fileName,depth,isfile)
      EXEC master.sys.xp_dirtree @Path,1,1;

      UPDATE #DirectoryTree
         SET fullpath = @Path,
         fileExt = RIGHT(fileName,3),
         dbName = @dbName
      WHERE fullpath IS NULL;

      --Delete the processed folder.
      DELETE FROM #DirectoryTree WHERE id = @Id;
   END
END

-- create cursor to loop through the backup files
DECLARE backupFiles CURSOR FOR
SELECT fileName,fileExt,fullpath,dbName
FROM #DirectoryTree
WHERE fileName LIKE '%' + @yesterdayDate + '%'

OPEN backupFiles

-- Loop through all the database backup files (.bak and .trn) from the basepath
FETCH NEXT FROM backupFiles INTO @fName, @fExt, @fullpath, @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = '';
   IF (@fExt = 'bak')
   BEGIN
      IF (@dbName != 'model' and @dbName != 'master' and @dbName != 'msdb')
      BEGIN
         SET @cmd = 'RESTORE DATABASE [' + @dbName + ']' + CHAR(13)+CHAR(10) + ' FROM DISK = ''' + @fullpath + '\' + @fName + '''' + CHAR(13)+CHAR(10) + ' WITH NORECOVERY;';
      END
   END
   ELSE IF (@fExt = 'trn')
   BEGIN
      IF (@dbName != 'model')
      BEGIN
         SET @cmd = 'RESTORE LOG [' + @dbName + ']' + CHAR(13)+CHAR(10) + ' FROM DISK = ''' + @fullpath + '\' + @fName + '''' + CHAR(13)+CHAR(10) + ' WITH RECOVERY;';
      END
   END
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @fName, @fExt, @fullpath, @dbName
END

CLOSE backupFiles
DEALLOCATE backupFiles

--Clean-up temporary table.
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;
GO

Note:

If databases already exist on the new host (i.e. restoring databases on subsequent run, then use the WITH REPLACE option in the restore script for the .bak files.

Click on the Messages tab to see the generated script. Copy and paste the script into a new screen to execute and restore the databases on the new host.

Here’s a sample of what the generated scripts looks like: