Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Step-by-step guide

 

  1. Set up TDE

  2. Install TDE client

  3. Create a smartpoint to the database location on disk

    1.  The SQL Smartpoint must allow these apps encrypt/decrypt permission. Other applications may be necessary depending on the environment.

      1. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlsrvr.exe
      2. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlagent.exe
      3. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\ReportingServicesService.exe
      4. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\msmdsrv.exe
      5. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\AccessToSql.exe
      6. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe
      7. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\SQLMaint.exe
  4. Expand
    titleMove a MS SQL database to a new location

    To move a SQL server database, the database must be detached, then the files must be moved to a new location and then the database can be attached again.

    Change the file locations with an ALTER DATABASE command:

    Code Block
    languagesql
    USE master; --do this all from the master
    ALTER DATABASE foo
    MODIFY FILE (name='DB_Data1'
                 ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

    Note: Changes to the path do take effect immediately, but will be applied the next time the database starts.

    Take the database offline

    Using WITH ROLLBACK IMMEDIATE will disconnect all users and roll back all currently open transactions:

    Code Block
    languagesql
    ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;


    Copy the files to the new location

    Copy the files to the new location using your

    Code Block
    COPY C:\sqlfiles\DB_Data1.mdf X:\NewDBFile\DB_Data1.mdf

    Note: Do not use any “Move” functions to copy the database files to their new location or transparent disk encryption will not be applied.


    Bring the database online

    Code Block
    languagesql
    ALTER DATABASE foo SET ONLINE;

 

Info

Content by Label
showLabelsfalse
max5
spacesTDE
showSpacefalse
sortmodified
reversetrue
typepage
cqllabel in ("sql","tde") and type = "page" and space = "TDE"
labelstde sql

Page properties
hiddentrue
Related issues