Archive
Migrate your SQL Server database to Azure by using SysTools “SQL to Azure Migration” Tool
Microsoft SQL Server is the most popular database management system, which provides flexibility to the database administrator to manage their database. It is a full-featured database and designed for use in corporate applications. But, many times, many users need to migrate their SQL Server database to the Azure SQL Database. Because Azure is an intelligent and fully managed relational cloud database that offers the broadest SQL Server engine compatibility.
However, at the time of SQL database to Azure migration, there are chances that we can miss some important steps or information such as trustworthy property, dependent Jobs, Linked Server, Logins etc. The main headache of any conversion is figuring out how to get the information from database and import into the server. Therefore, users need to use a professional tool to transfer SQL Server Database to Azure SQL Database.
One of the best third-party solutions I came up is SysTools SQL to Azure Migration Tool, which allows users to move all the SQL database objects like Tables, Triggers, Stored Procedures etc. to Azure SQL Database. Apart from this, there are many other features of this tool that are discussed below:
Key Features of SQL Server to Azure Migration Tool
1. Easily Transfer SQL Database to Azure
With the help of this tool, users can perform direct migration from SQL to Azure SQL database. For this, you need to provide all the credentials like server name, database name, username and password etc. After that, one can easily transfer SQL Server database to Azure.
2. Option to Add MDF/NDF Files
The tool provides an option to add MDF & associated NDF files to the software. You can also migrate all the database objects like Tables, Triggers, and Stored Procedures etc. from SQL to Azure.
3. Migrate Corrupted Database
The best part of this utility is that it can convert the corrupted database as well as the healthy file from local SQL server to Microsoft Azure SQL Database. Before migration, it scans the corrupted database files and repair them using either quick or advanced scan options.
4. Quick and Advance Scan Option
While working with this tool, the user can choose the required scan mode depending on the level of corruption out of the following modes:
– Quick Scan: This mode scans minor corrupted SQL files
– Advance Scan: This mode scan major corrupted SQL files
5. Auto Detect SQL Server File Versions
The SQL to Azure migration tool has an option to detect the versions of added MDF or NDF files automatically. For this, you just need to click on the checkbox next to this option.
6. Export Schema Option
The tool provides a feature to save the SQL database schema into Azure in any of the following ways:
– With the Only Schema: It migrates only schema of tables, views etc.
– With Schema & Date: It allows users to transfer both schema and data of all database objects.
Free and Licensed versions of SysTools SQL Server to Azure Migration Tool
The tool can be availed in the following two versions:
1. Demo Version: Users can freely download the trial version of software from the SysTools official website. It is available to understand the working of the software in a much better way.
2. Licensed Version: The licensed version of the tool can migrate SQL server database to Azure from MDF or NDF files. Also, it allows you to transfer the schema and schema & data.
System Requirements
The licensed version of SQL to Azure migration tool has been tested by the SQL experts. It will evaluate the performance of the software in terms of quality, reliability, security etc.
However, the testing has been performed in below-mentioned environment:
– Operating System: compatible with Windows 10 and all below versions
– Processor: Intel Pentium 1 GHz processor or any equivalent processor
– RAM: Around 2 GB of RAM is necessary
– Hard Disk: At least 100 MB space is required for installation
Advantages:
– It can also migrate corrupted or inaccessible SQL database to the Azure database.
– The software can preview all database components before migrating to Azure.
– The SQL Server to Azure migration tool has a simple and user-friendly interface.
Disadvantages:
– It is required to have database created on Azure SQL Server Database before migration.
– The Demo version only exports 25 records of each table.
Observational Verdict
After considering all the features of SQL Server to Azure Migration tool, I can say that it is a reliable and effective tool for DBAs and Developers to transfer SQL data to Azure without causing any data loss.
Azure SQL Data Sync – keep your data in sync between Azure Hybrid environment SQL Server and SQL DB
SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.
Internal Mechanism & Performance impact:
Data Sync uses insert, update, and delete triggers to track changes. It creates side tables in the user database for change tracking. These change tracking activities have an impact on your database workload. Assess your service tier and upgrade if needed.
Since Data Sync is trigger-based, transactional consistency is not guaranteed. Microsoft guarantees that all changes are made eventually, and that Data Sync does not cause data loss.
Limitations & Requirements:
1. Each table must have a primary key
2. Snapshot isolation must be enabled
3. A table cannot have an identity column that is not the primary key
4. The names of objects (databases, tables, and columns) cannot contain the printable characters period (.), left square bracket ([), or right square bracket (]).
5. Unsupported data types
a. FileStream
b. SQL/CLR UDT
c. XMLSchemaCollection (XML supported)
d. Cursor, Timestamp, Hierarchyid
6. Azure Active Directory authentication is not supported.
| Maximum number of sync groups any database can belong to | 5 |
| Maximum number of endpoints in a single sync group | 30 |
| Maximum number of on-premises endpoints in a single sync group | 5 |
| Database, table, schema, and column names | 50 characters per name |
| Tables in a sync group | 500 |
| Columns in a table in a sync group | 1000 |
| Data row size on a table | 24 Mb |
| Minimum sync interval | 5 Minutes |
Configurations:
1. Frequency can be set in seconds, minutes, hours and days (min 5 minutes)
2. You can choose and select desired tables and columns to sync
3. For on premises database you must configure a local DMG agent
Setup Azure SQL Data Sync:
Check here for Step-by-Step tutorial.
1. Create a Hub database on SQL DB (Hub & Spoke topology)
a. Hub database: must be Azure SQL DB
b. Spoke/Member database: rest of databases are either Azure SQL DB, on SQL Server instance
2. Create Sync group (On the Hub Database create a “New Sync Group”)
a. Sync Schema: which data is being synchronized
a. Sync metadata database: must be an Azure SQL DB
b. Sync Interval: frequency
c. Conflict Resolution Policy: (Hub wins or Member wins)
3. Add Sync Members (Spokes, can be either SQL DB or SQL instance)
a. Sync Agent Gateway (for on-prem): download and install on on-premise server
b. Sync Direction: bi-directional, or one direction
4. Configure Sync group
a. Select the Tables/Columns which you want to sync.
Powershell script to:
– Sync SQL DB & on-prem SQL Server instance
– Sync between multiple SQL Databases
Best Practices: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-best-practices-data-sync
Monitor: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-monitor-oms
Troubleshoot: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-troubleshoot-data-sync
Recommendations:
Data Sync is not appropriate for the following scenarios:
1. Disaster Recovery
2. Read Scale
3. ETL (OLTP to OLAP)
4. Migration from on-premises SQL Server to Azure SQL Database
Further reading: https://blogs.msdn.microsoft.com/igorpag/2017/07/06/azure-sql-data-sync-test-drive-and-first-impressions/
Create a new Azure SQL Database (on PaaS) step by step
Azure SQL Database is a Microsoft’s cloud based service on Microsoft on premise version of SQL Server relational database. It is a fully-managed relational cloud database service built for developers.
–> Today we will see how you can create a new SQL Database on Microsoft Azure:
1. Please make sure you have a Azure Account and Subscription.
2. Go to Microsoft Azure portal:
– In the menu, click New
– Select the Databases in the Azure Marketplace blade
– Click SQL Database
3. In the SQL Database blade enter the following settings:
– Name: MyFirstAzureSQLDB
– Subscription: Select your Azure Subscription
– Resource Group: Select the Resource Group, or create new
– Select source: We will choose here “Sample (AdventureWorksLT)”
– Server: Create a new logical server with following settings:
– Server name: Provide a globally unique name
– Server admin login: Provide a valid user name of your choice
– Password: Provide a valid password
– Location: Select a valid location
– Allow azure services to access server: Select the check box
– Want to use SQL elastic pool? Select “Not now”
– Pricing tier: As you click on this option you will be taken to the Configure Performance blade, select Basic for now, but you can choose higher tiers as per your workloads and needs.
– Collation: Leave it as default, SQL_Latin1_General_CP1_CI_AS
– Pin to dashboard: Select the check box
4. Finally click the Create button on the main blade. Provisioning a new SQL Database takes few minutes and you can see the deployment progress at the Top-Right corner of your portal. And once the deployment is done you will see following message at the same place:
5. Now as you had selected to pin the new Database, so you should be able to see it in your dashboard like this:
6. Click on the shortcut and you will be taken to its Settings and Properties, where you can check resources, various options, tweak settings and do some admin related stuff.
7. The portal also provides a basic Query Editor to execute SQL Queries, check data in tables, and explore the database, check some DB objects, etc. After selecting the Query Editor you can click on Login button at the top. Provide the User Name and Password you applied while creating the Database.
8. Try explore the Sample Database, it gives limited feature to check Tables, Views and SPs. You can try writing a SQL Query and Run against the tables/views, or execute the SPs. The results will be shown in pane below.
9. You can also connect to this Azure SQL Database from SSMS installed in you PC or a remote server, which I’ll show in my next [blog post].
–> Terms used above:
1. Resource Group: is a collection of resources that share the same life cycle, permissions, and policies.
2. Server: or Logical Server acts as a central administrative point for multiple databases, including elastic pools logins, firewall rules, auditing rules, threat detection policies, and failover groups.
3. DTU (or Database Transaction Units): Microsoft guarantees a certain level of resources for that database (independent of any other database in the Azure cloud) and providing a predictable level of performance. This amount of resources is calculated as a number of Database Transaction Units or DTUs, and is a blended measure of CPU, memory, I/O (data and transaction log I/O).
4. Elastic Pools: provide a simple and cost effective solution for managing the performance of multiple databases within a fixed budget. An elastic pool provides compute (eDTUs) and storage resources that are shared between all the databases it contains. Databases within a pool only use the resources they need, when they need them, within configurable limits. The price of a pool is based only on the amount of resources configured and is independent of the number of databases it contains.
New Sample database “WideWorldImporters” for SQL Server 2016 and Azure SQL Database
So now as SQL Server 2016 is released (on 1-June-2016) and is in market for few days, so Microsoft team has released a new Sample Database “WideWorldImporters” specially for learning and working with new features of SQL Server 2016 and Azure SQL Database.
Till now AdventureWorks was quiet popular Sample Database since SQL Server 2005 to SQL Server 2014, and still will be. And prior to this we had Northwind and Pubs sample databases to work with SQL Server 2000 version.
–> WideWorldImporters:
You can download both the OLTP and OLAP (DW/BI) databases from this GitHub link.
1. WideWorldImporters (OLTP): contains sample tables for OnLine Transaction Processing (OLTP) workloads, as well as Real-time Operation Analytics.
2. WideWorldImportersDW (OLAP, DW/BI): contains sample tables for OnLine Analytical Processing (OLAP) workloads, in Dimensional Model, like Fact and Dimension tables.
3. For Azure SQL Database: you can download the bacpac for both the editions OLTP/OLAP.
–> These Sample Databases are designed in such a way that these can be used to check and evaluate the new Features of SQL Server 2016, like:
1. Temporal Database and Tables
2. Native JSON support
3. ColumnStore Index
4. In-Memory OLTP
5. Row Level Security, Dynamic Data Masking and Always Encrypted
6. Partitioning
7. Query Store
8. Polybase
–> The MSDN Documentation of these sample databases provides you more information on:
1. Installation and Configuration
2. The Database Catalog
3. Use of SQL Server features and capabilities (mentioned in above points)
4. Some Sample Queries (zip file)
–> After Downloading and Restoring the sample tables looks like this in Object Explorer:
Just Rs. 32,900 !!!
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 