Skip to content

restore-a-database-and-bind-it-to-a-resource-pool.md

Latest commit

 

History

History
73 lines (54 loc) · 4.21 KB

restore-a-database-and-bind-it-to-a-resource-pool.md

File metadata and controls

73 lines (54 loc) · 4.21 KB
title Restore a Database and Bind it to a Resource Pool
description Learn about restoring a database with memory-optimized tables in SQL Server. Follow best practices by binding the database to a named resource pool.
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice in-memory-oltp
ms.topic how-to

Restore a Database and Bind it to a Resource Pool

[!INCLUDE SQL Server] Even though you have enough memory to restore a database with memory-optimized tables, you want to follow best practices and bind the database to a named resource pool. Since the database must exist before you can bind it to the pool restoring your database is a multi-step process. This topic walks you through that process.

Restoring a database with memory-optimized tables

The following steps fully restore the database IMOLTP_DB and bind it to the Pool_IMOLTP.

  1. Restore with NORECOVERY

  2. Create the resource pool

  3. Bind the database and resource pool

  4. Restore with RECOVERY

  5. Monitor the resource pool performance

Restore with NORECOVERY

When you restore a database, NORECOVERY causes the database to be created and the disk image restored without consuming memory.

RESTORE DATABASE IMOLTP_DB   
   FROM DISK = 'C:\IMOLTP_test\IMOLTP_DB.bak'  
   WITH NORECOVERY

Create the resource pool

The following [!INCLUDEtsql] creates a resource pool named Pool_IMOLTP with 50% of memory available for its use. After the pool is created, the Resource Governor is reconfigured to include Pool_IMOLTP.

CREATE RESOURCE POOL Pool_IMOLTP WITH (MAX_MEMORY_PERCENT = 50);  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

Bind the database and resource pool

Use the system function sp_xtp_bind_db_resource_pool to bind the database to the resource pool. The function takes two parameters: the database name followed by the resource pool name.

The following [!INCLUDEtsql] defines a binding of the database IMOLTP_DB to the resource pool Pool_IMOLTP. The binding does not become effective until you complete the next step.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO

Restore with RECOVERY

When you restore the database with recovery the database is brought online and all the data restored.

RESTORE DATABASE IMOLTP_DB   
   WITH RECOVERY

Monitor the resource pool performance

Once the database is bound to the named resource pool and restored with recovery, monitor the [!INCLUDEssNoVersion], Resource Pool Stats Object. For more information see SQL Server, Resource Pool Stats Object.

See Also

Bind a Database with Memory-Optimized Tables to a Resource Pool
sys.sp_xtp_bind_db_resource_pool (Transact-SQL)
SQL Server, Resource Pool Stats Object
sys.dm_resource_governor_resource_pools