Creating SQL Server Instances

This guide shows you how to create SQL Server instances on Google Compute Engine. For more general information about Windows Server instances and Windows applications that you can run on Compute Engine, see Windows on Compute Engine.

To learn how to create and configure a performance-optimized SQL Server instance, see Creating a High-Performance SQL Server Instance. Optionally, you can use HammerDB to run Load Testing on SQL Server. Note: Windows Server and SQL Server images are premium resources that incur additional fees to use.

Before you begin

• If you want to use the command-line examples in this guide:
  1. Install the gcloud command-line tool.
  2. Set a default region and zone.
• If you want to use the API examples in this guide, set up API access.

Creating a SQL Server instance

To create a SQL Server instance, specify the image family for the specific version of SQL Server that you need. For a list of the SQL Server image families, see the list of public images.

For optimal performance, SQL Server instances require machine types with more virtual CPUs and larger amounts of memory than most Linux instances. Google recommends that you use machine types with at least 2 vCPUs and at least 4 GB of system memory when you run SQL Server instances. You can use custom machine types to configure SQL Server instances to match the performance requirements for your workload. Additionally, you can use larger persistent disks and faster SSD persistent disks to improve the performance of your applications.

Additionally, you must set specific firewall rules to allow SQL Server traffic on the VPC network or subnet that your instance is a part of. For more information see Best Practices for SQL Server.

Console

To create a SQL Server instance:

For images with SQL Server, you must select SQL Server images from the Applications tab.

1. In the Cloud Platform Console, go to the VM Instances page.

   Go to the VM Instances page

2. Click the Create instance button.
3. In the Boot disk section, click Change to begin configuring your boot disk.
4. In the Application images tab, choose a SQL Server image.
5. Click Select.
6. Click the Create button to create the instance.

After you create the instance, create a firewall rule to allow access to SQL Server on your instance. The default SQL Server port is 1433.

1. In the Cloud Platform Console, go to the Firewall Rules page. In the Cloud Platform Console, go to the Firewall Rules page. Go to the Firewall Rules page
2. At the top of the page, click Create firewall rule to start creating a firewall rule.
3. Specify the details for this firewall rule.
   • In the Name field, specify a name for the firewall rule.
   • In the VPC network field, select the VPC network where your SQL Server instance is located.
   • In Source filter field, select the range of IPs that you want to allow access on this port. For this example, you can select Allow from any source.
   • In the Allowed protocols and ports section, enter the port that SQL Server uses. For this example, specify tcp:1433;, which is the default port.
4. Click Create to create this firewall rule and allow access to your SQL Server instance over port 1433.

If you need to add additional firewall rules to your instance, see the firewall rules documentation.

gcloud

Use the compute images list command to see a list of available SQL Server images:

gcloud compute images list --project windows-sql-cloud --no-standard-images

Use the compute instances create command to create a new instance and specify the image family for one of the Windows Server or SQL Server public images.

gcloud compute instances create [INSTANCE_NAME] \
    --image-project windows-sql-cloud \
    --image-family [IMAGE_FAMILY] \
    --machine-type [MACHINE_TYPE] \
    --boot-disk-size [BOOT_DISK_SIZE] \
    --boot-disk-type [BOOT_DISK_TYPE]

where:

• [INSTANCE_NAME] is the name for the new instance.
• [IMAGE_FAMILY] is one of the public image families for Windows Server or SQL Server images.
• [MACHINE_TYPE] is one of the available machine types.
• [BOOT_DISK_SIZE] is the size of the boot disk in GB. Larger persistent disks have higher throughput.
• [BOOT_DISK_TYPE] is the type of the boot disk for your instance. Specify pd-ssd to use a faster SSD persistent disk or pd-standard to use a standard HDD persistent disk.

After you create the instance, create a firewall rule to allow access to SQL Server on your instance. The default SQL Server port is 1433.

gcloud compute firewall-rules create sql-server-1433 \
--description "Allow SQL Server access from all sources on port 1433." \
--allow tcp:1433 --network [NETWORK]

where [NETWORK] is the name of the VPC network where your instance is located.

If you need to add additional firewall rules to your instance, see the firewall rules documentation.

API

To create an instance with the API, include the initializeParams property in your instance creation request and specify a Windows image.

 POST https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/[ZONE]/instances

 instance = {
   "name": "[INSTANCE_NAME]",
   "machineType": "zones/[ZONE]/machineTypes/[MACHINE_TYPE]",
   "disks": [{
       "boot": "true",
       "type": "PERSISTENT",
       "initializeParams": {
          "diskName": "[DISK_NAME]",
          "sourceImage": "projects/windows-sql-cloud/global/images/family/[IMAGE_FAMILY]",
          "diskSizeGb": "[BOOT_DISK_SIZE]",
          "diskType": "[BOOT_DISK_TYPE]",
        }
     }],
   "networkInterfaces": [{
     "accessConfigs": [{
       "type": "ONE_TO_ONE_NAT",
       "name": "External NAT"
      }],
     "network": "global/networks/default"
   }],
   "serviceAccounts": [{
        "email": DEFAULT_SERVICE_EMAIL,
        "scopes": DEFAULT_SCOPES
   }]
 }

where:

• [PROJECT_ID] is the ID for your project.
• [ZONE] is the zone for this instance.
• [INSTANCE_NAME] is the name for the new instance.
• [IMAGE_FAMILY] is one of the public image families for Windows Server or SQL Server images.
• [IMAGE_PROJECT] is either windows-cloud for Windows Server images or windows-sql-cloud for Windows Server images with SQL Server preinstalled.
• [MACHINE_TYPE] is one of the available machine types.
• [BOOT_DISK_SIZE] is the size of the boot disk in GB. Larger persistent disks have higher throughput.
• [BOOT_DISK_TYPE] is the type of the boot disk for your instance. Specify pd-ssd to use a faster SSD persistent disk or pd-standard to use a standard HDD persistent disk.

After you create the instance, create a firewall rule to allow access to SQL Server on your instance. The default SQL Server port is 1433.

POST https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/global/firewalls

{
 "name": "sql-server-1433",
 "network": "global/networks/[NETWORK]",
 "allowed": [
  {
   "IPProtocol": "tcp",
   "ports": [
    "1433"
   ]
  }
 ]
}

where:

• [PROJECT_ID] is the ID for your project.
• [NETWORK] is the name of the VPC network where your instance is located.

After you create your SQL Server instance, set the initial password for the instance so that you can connect to the instance through RDP. Then, use the SQL Server Management Studio to manage databases on your SQL Server instance.

Additionally, you can enhance the SQL Server installation with one or more of the following items:

• Install SQL Server Data Tools on your SQL Server instance so that you can create and manage your SQL Server databases using Visual Studio. Connect to the instance and then download and install the SQL Server Data Tools from the Microsoft website.
• SQL Server images include several default components. Optionally, you can add features to your SQL Server installation. SQL Server images always include the setup executable at c:\setup.exe.
• If you need to install SQL Server to a secondary persistent disk, add a secondary persistent disk to the instance. Then, connect to the instance and run the c:\setup.exe installer and select a new installation path on your secondary disk.
• If you require additional storage space on your boot disk or on any secondary storage disks, resize the persistent disks even while your instances is running.

Managing SQL Server databases

Use the SQL Server Management Studio to configure SQL Server databases. You can download and install the SQL Server Management Studio on your local workstation, and use it to connect to the database engine on your instance remotely.

If you cannot install the Management Studio on your local workstation, connect to the instance through RDP and run the Management Studio on the instance itself. SQL Server 2012 and SQL Server 2014 both include the SQL Server Management Studio by default. For SQL Server 2016, you must download the SQL Server Management Studio from the Microsoft website and install it on the instance.

By default, SQL Server uses Windows Authentication mode to control remote access to SQL Server itself. If you need to use SQL Server Authentication mode, change the authentication mode.

What's next

• Verify that your instance has successfully started.
• Create and attach a secondary storage disk to your instance to store your data separately from the boot disk.
• Learn best practices for SQL Server on Cloud Platform.
• Create and configure a High-Performance SQL Server Instance.
• Use HammerDB to run Load Testing on SQL Server.
• Use SQL Server with .NET applications.