Migrating data between SQL Server 2008 and Cloud SQL for SQL Server using backup files

In this tutorial, you migrate data from SQL Server 2008 to Cloud SQL for SQL Server 2017 Enterprise. The tutorial shows you how to export the data from SQL Server 2008, import the data to Cloud SQL for SQL Server 2017, and validate that the data was successfully imported.

If you want to migrate data from SQL Server 2017, see Migrating data between SQL Server 2017 and Cloud SQL for SQL Server using backup files.

This tutorial is useful if you are a sysadmin, developer, engineer, database admin, or DevOps engineer who wants to migrate data from SQL Server 2008 to Cloud SQL for SQL Server 2017 or who wants to upgrade from SQL Server 2008 to SQL Server 2017.

The tutorial assumes that you have a license for SQL Server 2008 and that you're familiar with the following:

Objectives

  • Create a Cloud SQL for SQL Server instance.
  • Create a Cloud Storage bucket.
  • Back up your database.
  • Import the database into Cloud SQL for SQL Server.
  • Validate the imported data.

Costs

This tutorial uses the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. For more information, see Cleaning up.

Before you begin

  1. In the Cloud Console, on the project selector page, select or create a Google Cloud project.

    GO TO THE PROJECT SELECTOR PAGE

  2. Make sure that billing is enabled for your Cloud project. Learn how to confirm billing is enabled for your project.

  3. Enable the Cloud Storage and Cloud SQL APIs.

    ENABLE THE APIS

  4. Install and initialize the Cloud SDK on the server that's running SQL Server 2008.

  5. In the Cloud Console, go to Cloud Shell.

    GO TO Cloud Shell

    At the bottom of the Cloud Console, a Cloud Shell session opens and displays a command-line prompt. Cloud Shell is a shell environment with the Cloud SDK already installed, including the gcloud command-line tool, and with values already set for your current project. It can take a few seconds for the session to initialize.

Creating the Cloud SQL instance and Cloud Storage bucket

  1. In Cloud Shell, create the Cloud SQL for SQL Server 2017 Enterprise instance that you later migrate the database to:

    gcloud beta sql instances create target  \
        --database-version=SQLSERVER_2017_ENTERPRISE \
        --cpu=2 \
        --memory=5GB \
        --root-password=sqlserver12@ \
        --zone=us-central1-f
    

    It can take a few minutes to create the instance. The default root username is sqlserver with a default password of sqlserver12@. For this tutorial, you create the instance in the us-central1-f zone. For more information about zones, see Cloud locations.

  2. Create a Cloud Storage bucket to store the backup file before the data is imported into Cloud SQL:

    gsutil mb -b off -l US "gs://bucket-name"
    

    Replace bucket-name with a unique name for the Cloud Storage bucket.

Backing up your database

In this section, you connect to your Windows server running SQL Server 2008, create a backup of your database, and upload the backup database to Cloud Storage.

  1. Connect to your Windows server running SQL Server 2008 by using RDP. It can be the primary or a replica server.

  2. Launch the PowerShell command-line tool.

  3. In the terminal where you installed the Cloud SDK, create a backup folder:

    mkdir c:\backup
    
  4. Create a backup of the database in the backup folder:

    osql -E -Q "BACKUP DATABASE db-name TO DISK='c:\backup\db-name.bak'"
    

    Replace db-name with the name of the database that you want to migrate to Cloud SQL.

  5. Copy the backup file to the Cloud Storage bucket:

    $PROJECT_ID = (gcloud sql instances describe target --format='value(project)' )
    gsutil cp -n c:\backup\db-name.bak gs://bucket-name
    

Importing the backup file to Cloud SQL

  1. In Cloud Shell, retrieve the service account that's associated with the Cloud SQL instance and save it in a variable:

    SVC_EMAIL_ADDRESS=$(gcloud sql instances describe target \
        --format='value(serviceAccountEmailAddress)')
    echo $SVC_EMAIL_ADDRESS
    

    Google Cloud creates a service account when you created the Cloud SQL instance. You use the service account to give the Cloud SQL instance permission to access the resources it needs.

  2. Add the service account to the Cloud Storage bucket's access-control list (ACL) with writer permissions:

    gsutil acl ch -u ${SVC_EMAIL_ADDRESS}:W gs://bucket-name
    
  3. Add the service account to the import file with reader permissions:

    gsutil acl ch -u $SVC_EMAIL_ADDRESS:R gs://bucket-name/db-name.bak
    
  4. Import the backup file to the Cloud SQL database:

    gcloud beta sql import bak target \
        gs://bucket-namedb-name.bak \
        --database db-name
    

Validating the data import

In this section, you check that the data was successfully imported.

  1. In Cloud Shell, install the SQL Server toolkit:

    sudo apt-get install -y mssql-tools
    

    If you accept the license terms, enter yes when you're prompted.

  2. To securely access your Cloud SQL instance, download the Cloud SQL proxy:

    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
    
  3. Start the Cloud SQL proxy:

    CONNECTION_NAME=$(gcloud sql instances describe target --format='value(connectionName)')
    ./cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:1433 &
    
  4. Run a query to validate the data in one or more of the tables:

    /opt/mssql-tools/bin/sqlcmd -U sqlserver -S 127.0.0.1 -Q "query-string"
    

    Replace query-string with the SQL query that you want to run.

    When you're prompted, enter the sqlserver12@ password.

Cleaning up

The easiest way to eliminate billing is to delete the Cloud project you created for the tutorial.

Delete the project

  1. In the Cloud Console, go to the Manage resources page.

    Go to the Manage resources page

  2. In the project list, select the project that you want to delete and then click Delete .
  3. In the dialog, type the project ID and then click Shut down to delete the project.

What's next