Skip to content

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

Latest commit

 

History

History
62 lines (49 loc) · 3.04 KB

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

File metadata and controls

62 lines (49 loc) · 3.04 KB
title Restore a Database and Bind it to a Resource Pool | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology in-memory-oltp
ms.topic conceptual
ms.assetid 0d20a569-8a27-409c-bcab-0effefb48013
author CarlRabeler
ms.author carlrab
manager craigg

Restore a Database and Bind it to a Resource Pool

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.

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