Loading Large Dataset using datatables with AJAX

OK, so we manage to retrieve records from Active Directory from SQL Server using Linked Server, and the process is now automated as detailed in a previous article.

Now let’s take that data and display them in a simple listing with page pagination and search/filter capability. To achieve this we will make use of a JavaScript library called datatables. And to ensure great loading time (i.e. no page freeze for large dataset of 5000 or more retrieved records), we will make of the AJAX and deferRenders options in datatables.

Retrieving records from the database

Create a new ColdFusion component called staff.cfc and add the following code for retrieving staff records from a database table. The records from this table were populated from an Active Directory query via SQL Server. A sample partial code snippet to illustrate


<cffunction name="getStaff" access="remote" returnformat="json">
   <cfscript>
      var qStaff = "";
      var sStaff = {"data" : []};
      var jsStaff ="";
      var rowNum = 0;
   </cfscript>

   <cfquery name="qStaff" datasource="<your-datasource-name>">
      SELECT givenName,sn,mail
      FROM staff
   </cfquery>

   <cfscript>
      if (qGet.RecordCount gt 0){
         for (staff in qStaff){
            rowNum++;
            sStaff.data[rowNum] = {};                       
            StructInsert(sStaff.data[rowNum],"givenName",staff.givenName);
            StructInsert(sStaff.data[rowNum],"sn",staff.sn);
            StructInsert(sStaff.data[rowNum],"mail",staff.mail);  
         } //for
         jsStaff = serializeJSON(sStaff);
      }  //if
   </cfscript>

   <cfreturn jsStaff>
</cffunction>

Important notes

  • This function (method) must have this attribute:  returnformat=json
  • Convert the returned query records into a struct in the format required by datatables, i.e. {“data” : []}
  • Finally serialise this struct into JSON before returning it using ColdFusion function serializeJSON

Displaying the records on the front-end

On the front-end, let’s use datatables with its deferRender and AJAX options to load and display the records from the database table. Here’s a partial sample code to illustrate.


<table id="staff" class="table table-striped table-bordered table-hover" width="100%">
   <thead>
      <tr>
         <td>First Name</td>
         <td>Last Name</td>
         <td>Email</td>         
      </tr>
   </thead>
</table>

<script>
   $(document).ready(function() {
      $('#staff').dataTable({
         'deferRender': true,
         'ajax': {
            'url': 'staff.cfc?method=getStaff'
         },
         'columns': [            
            { 'data': 'givenName' },
            { 'data': 'sn' },
            { 'data': 'mail' }
         ]
      }); //dataTable
   });
</script>

Important notes

  • For the table, we only need to define the table header when using the AJAX option
  • We use datables’ columns option to match the table columns to the ajax returned record. Order is important.
  • For the datatables option set deferRender=true to defer rending for large dataset and hence improve loading time
  • Datatables’ ajax option is use to retrieve records from the database
  • The datatables’ twin options of ajax and deferRender is crucial for improving loading time for large data set on the front-end
Advertisements

Resolving Transaction Log for Database SQLMonitorData is Full Error

If you log into Red Gate SQL Monitor and see the following error:

 

Then here’s what you need to do to resolve the issue. First, let see what’s going on with the transaction log by performing this query via SQL Server Management Studio (SSMS):


select log_reuse_wait_desc, * 
from sys.databases 
where name ='SQLMonitorData'

When you run the above query, you see an output like this (in the results pane below):

 

This means that a transaction log backup is required. To back up the transaction log, do the following (while still in SSMS):

  • Create a new maintenance plan for this database to backup the transaction log for database SQLMonitorData
  • In the maintenance plan, select only the transaction log for SQLMonitorData database to backup
  • Execute the maintenance plan to initiate the transaction log backup

That’s all there is to it. After a successful transaction log backup for the SQLMonitorData database, the above error should no longer show up, and you will be able to see the SQL Monitor dashboard.

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:

How to restore the msdb database

To ensure a successful restore of the msdb database, follow these two simple major steps:

  • Make preparations for the restore
  • Restoring the msdb database

Here are some information about the msdb database before we start:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

1 Make preparations for the restore

To prepare for a smooth restore we need carry out these tasks:

  • Get the version of the destination server
  • Get the version of source server on which the backup was created
  • Match the versions for the source and destination servers
  • Ensure exclusive access to the database

1.1 Get the version of the destination server

The versions of SQL Server need to be the same for the source and destination when restoring the msdb or model database. If the versions for the source and destination servers do not match then the restore will fail.

Use one of these methods:

SSMS Object Explorer

Connect to a SQL Server instance through SSMS and find the SQL Server version in object explorer as shown below.

SERVERPROPERTY system function

The SERVERPROPERTY system function can be used for retrieving SQL Server version information to get edition and service pack information.

Using @@Version

The SQL Server version can also be retrieved by using @@Version. It can be used in a simple select statement as shown below

1.2 Get the version of source server on which the backup was created

The best way to do this is to get the information from the backup file itself as shown below using the RESTORE HEADERONLY command.


RESTORE HEADERONLY
FROM DISK = '<backup-location>\<msdb-backup-name>.bak'
GO

1.3 Match the versions for the source and destination servers

In our case we have version 10.50.1617 which matches both the source and destination servers. If this is not the case then apply or remove service packs on the destination server to match the source server. Once both versions are the same then we are ready to go further with the process.

1.4 Ensure exclusive access to the database

Exclusive access for the database is required just like restoring any database, but for the msdb it is slightly more demanding. In the case of msdb we also have to consider the SQL Agent service. If the SQL Server agent service is running then exclusive access can not be achieved.

So for msdb we need to stop the SQL Agent service to make sure we can get exclusive access. This can be done by right clicking on SQL Server Agent and selecting Stop.

2 Restoring the msdb database

Here are the steps for restoring the msdb database:

  • Stop the SQL Server Agent
  • Restore the msdb database
  • Start the SQL Server Agent

2.1 Stop the SQL Server Agent

You can either stop the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Stop

2.2 Restore the msdb database

To restore the msdb database do the following:

  • Launch SQL Server Management Studio (SSMS) or activate SQLCMD from a Command Prompt
  • Issue the following:
    RESTORE DATABASE [msdb]
    FROM
    DISK = '<backup-location>\<msdb-backup-file-name>.bak'
    WITH REPLACE;
    GO
    
    -- for example
    RESTORE DATABASE [msdb]
    FROM
    DISK = 'C:\Programs\SQL Server\MSSQL\Backup\msdb_backup.bak'
    WITH REPLACE;
    GO
    
  • Click Execute (if using SSMS) or hit Enter (if using SQLCMD)

2.3 Start the SQL Server Agent

You can either start the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Start

Congratulations! You should’ve already successfully restored the msdb database.

How to restore the master database

Here are the steps for restoring the SQL Server master database:

  • Set MS SQL Server to Single User Mode
  • Restore master database via SQLCMD
  • Reset MS SQL Server to Multi User Mode

Before we start, here’s some important information about the master database:

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Set MS SQL Server to Single User Mode

To set MS SQL Server to single user mode, do the following:

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Append -m; to the above entry. That is you should now have something like this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

Restore master database via SQLCMD

To restore the master database, do the following:

  • Open a Command Prompt as an administrator from the Programs menu
  • At the prompt, type SQLCMD and press Enter
  • Then type
    RESTORE DATABASE master FROM DISK = ‘backup-path\master.bak’ WITH REPLACE;
  • Prese enter and type: GO

Reset MS SQL Server to Multi User Mode

After you have successfully restore the master database, use these steps to set MS SQL Server back to multi user mode:

  • Return to the SQL Server Configuration Manager dialogue screen that you had open previously
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Remove -m; from the above entry. That is, you should now have something like this now: -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

That’s it! You’ve just successfully restore the master database!

Database msdb cannot be opened and marked as suspect

If you see the following message when trying to open a database in SQL Server Management Studio:

Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error:926)

Then you need to replace these two files from a backup:

  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBData.mdf
  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBLog.ldf

To do so, follow these steps:

  • Stop the MSSQLSERVER service
    • Either open Services
      • Find SQL Server (MSSQLSERVER)
      • Right click and click stop
    • Or use SQL Server Management Studio (SSMS)
      • Right the database icon and select Stop
  • Once MSSQLSERVER service has stopped, copy and replace the following files in the MSSQL\DATA\ folder:
    • MSDBData.mdf
    • MSDBLog.ldf

If the above files are backups of the current machine’s installation, then Error:926 is now fixed, and no further actions are needed.

However if no backup for the current installation was found, and a backup from another computer was used which has a different install path, then the following error will be shown:

Daily Backup – User Databases.Subplan_1′ cannot be run because the SSIS subsystem failed to load.  The job has been suspended.

Running this query:

SELECT * FROM
msdb.dbo.syssubsystems

will show that the path to the subsystem DLL are incorrect (if the current SQL Server installation is on the D drive but current records points to a different drive or location).

To fix the Job has been suspended error (due to incorrect DLL path), do the following:

  • Delete the existing entries:
    DELETE FROM msdb.dbo.syssubsystems
  • Update the table with new rows pointing to the correct location of the DLLs by:
    EXEC msdb.dbo.sp_verify_subsystems 1

How to Retrieve SQL Server Product Keys

This quick tip will come in handy when you need to find out the product keys for an installed SQL Server if that information was not recorded when it was initially installed.

Please note that this PowerShell module and its corresponding Get-SqlServerKeys command only works for MS SQL Server 2005 – 2014. It is currently not compatible with MS SQL Server 2016.

Here are the steps  you need to carry out from a desktop computer running Windows 7 or above to retrieve the SQL Server product keys.

1 Confirm PowerShell version

2 Download and install PowerShell 3.0 if required

If you have an older PowerShell version (e.g. version 1 or 2), then upgrade it to version 3. To do so, you will need to download and install Windows Management Framework 3.0 which also contains PowerShell 3.0 from this website:

https://www.microsoft.com/en-au/download/details.aspx?id=34595

3 Download the PowerShell module for retrieving the product keys

Once you’ve successfully upgraded to PowerShell version 3, please head over to this website to download the PowerShell module for retrieving SQL Server product keys:

https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8/view/Discussions#content

4 Check Execution Policy

Before you can import and execute the PowerShell command to retrieve the SQL Server product keys, you will need to check the desktop computer’s execution policy.

If the execution policy is set to Restricted (which is the default), then temporary set it to Unrestricted. Import the PowerShell module you download in step 3, execute the command to retrieve the SQL Server product keys and then set it back to the default Restricted execution policy, that is:

  PS > Get-ExecutionPolicy
  PS > Set-ExecutionPolicy -ExecutionPolicy Unrestricted
    [Yes] Y

5 Import the PowerShell module for retrieving the product keys

Once the execution policy restriction has been lifted (i.e. temporary set to unrestricted), import the PowerShell module you download in step 3 so that the command Get-SqlServerKeys is available in the current session.

  PS > Import-Module .\Get-SqlServerKeys.psm1
    Run Once [R]

6 Run the Get-SqlServerKeys command to retrieve the product keys

Execute the Get-SqlServerKeys command to retrieve the SQL Server product keys. To retrieve SQL Server product remotely (i.e. those on a remote server), use the Server parameter, i.e.

  PS > Get-SqlServerKeys -Servers <list-of-remote-server-fqdn-or-ip-addresses>

The Servers parameter takes a list of server’s full qualify domain names (or IP addresses). If you’re on the actual server (i.e. not issuing the command from your client desktop computer), then the Servers parameter can be omitted, i.e.

  PS > Get-SqlServerKeys

Here are the commands:

And the returned information:

It will list all the SQL Server instances if there are multiple instances on the server. Please note the above assume you’re running the command from the actual server, and not remotely. If running from a client desktop computer, you will need to add the -Servers parameter and its list of servers names or IP addresses.

7 Set the Execution Policy to Restricted

Once you’re done, please don’t forget to set the execution policy back to the default Restricted.

  PS > Set-ExecutionPolicy -ExecutionPolicy Restricted