Friday, November 11, 2016

How to deploy your SQL Server database to a remote server

CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.

  1. First time deployment
    1. Backup / restore method
      1. Backup the database on the source
      2. Copy the backup file to the target machine
      3. Restore the database on the target
      4. Create logins and set permissions as needed
    2. Compare and Synchronize method
      1. Create database on the target machine (blank)
      2. Use xSQL Schema Compare to compare and synchronize the database schemas of the source and the target. 
      3. Use xSQL Data Compare to populate the remote database with whatever data you might have on the source that you want to publish (lookup tables etc.)
  2. Database exists in the target server
    1. Compare and Synchronize method
      1. Use xSQL Schema Compare to compare and synchronize the database schemas of the source and the target. 
      2. Use xSQL Data Compare to push any data you need from the source to the target. Caution: be careful not to affect any data that exists on the target already.
CASE 2: You can not directly access the target server but you have a way to deploy SQL scripts on that server. As is indeed the case in most scenarios you also should have a way to get a backup of your database from that remote host. In this case follow those simple steps:

  1. Restore the remote database on your local environment
  2. Use xSQL Schema Compare to compare your source database with the restored database. Generate the schema synchronization script and save it.
  3. Use xSQL Data Compare to compare your source database with the restored database. Carefully make your selections to ensure you push only the data you want to push from the source to the target. Generate the data synchronization script and save it. 
  4. Deploy your schema synchronization script to the target machine. 
  5. Deploy your data synchronization script to the target machine.

Both xSQL Schema Compare and xSQL Data Compare are completely free for SQL Server Express with no restrictions or limitations. Furthermore, for other editions of SQL Server the tools are free if the database has under a certain number of objects in it (current limitations are listed here).

0 comments:

Post a Comment