SQL Azure Database provides all the essential database creation, querying, testing and optimization tools from the Azure Cloud Service Platform. However, there are times when you do need to work on database hosted on Azure from your local development server.
So how would you copy and transfer the database from SQL Azure to your locally hosted SQL Server? This article will illustrate a few simple ways to achieve this.
Using SQL Server Integration Services (SSIS) Wizard
SSIS provides very simple means to copy SQL Azure Database to your onsite SQL Server console. Just follow the simple process guided by the wizard in SQL Server Management Studio. But using this service will only import data from the table not column properties, constraints, keys, indices, stored procedures, triggers, security settings, users, logons, etc.
Copying Azure SQL Database manually using SSIS
- First create a new empty database on your local SQL instance in SQL Server Management Studio
- Choose Import Data from menu.
- Write the connection parameters for the source (SQL Azure). Being created for local server, select “.Net Framework Data Provider for SQLServer” as a provider.
- Now choose the newly created empty database as the destination.
Furthermore, you can follow the wizard to select table’s data you want to copy. You can choose to skip any of the tables you don’t need. And this process can also be automated by creating SSIS package and re-executing it whenever an import job is required.
Using a combination of SSIS and DB creation scripts
This process will not only copy data from tables ( as described above) but also will fetch all data and metadata not copied through SSIS.
- First transfer data using SSIS
- Create DB Create script from SQL Azure database (right click on database in SSMS, choose Generate Scripts -> Database Create)
- Re-play this database on your local database.
Use Import/Export service in SQL Azure
By using the Import / Export service in SQL Azure, will transfer data with schema objects to Azure Blob Storage as a BACPAC. You will need an Azure Storage account and do this in Azure web portal. Your data is copied as BACPAC into Azure Blog Storage, just click export button from Azure web portal and select the SQL instance you want to export.
Monitoring SQL Azure with Netreo
Netreo can monitor and automate SQL Azure by querying Dynamic Management Views, visualizing important performance metrics, exposing slowest queries, executing custom SQL scripts, and more.
Netreo keeps constant tabs on your most important asset: the database. It demystifies utilization of SQL Azure performance tiers, database throughput units (DTUs), visualizes important metrics and tracks slowest areas.
- Track CPU, Read/Write utilizations and other key performance indicators
- Know when maximum capacity is being reached
- Be alerted of any outages, blocking queries or system slowdowns
Learn more about Netreo SQL Azure Monitoring and Automation capabilities.
Netreo can help you with all your Azure monitoring needs. Request a Demo!