feedburner
Enter your email address:

Delivered by FeedBurner


Configuring and securing connections to Azure SQL Database

Labels:

With Azure SQL Database, Azure configures the network for you and by default restricts all access using Azure SQL Database firewall rules, as discussed in Chapter 3, “Getting started with an Azure SQL Database.” If a firewall rule does not exist, Azure will reject all connection attempts from IP addresses that have not been whitelisted explicitly.

 Use database-level firewall rules in conjunction with contained users (discussed below) whenever possible to make your database more portable.

 Use server-level firewall rules when you have many databases that have the same access requirements and you don’t want to spend time configuring each database individually.

Additionally, Azure SQL Database requires encrypted connections at all times while data is “in transit” to and from the database. In your application’s connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal). If you do not, the connection will not verify the identity of the server and will be susceptible to “man-in-the-middle” attacks. For the ADO.NET driver, for instance, these connection string parameters are Encrypt=True and TrustServerCertificate=False. For more information, see Azure SQL Database Connection Encryption and Certificate Validation. The code block below shows a sample ADO.NET connection string.

Server=tcp:[your_sql_database_server_name_here].database.windows.net,1433;Database=[your_sql_database_name_here]>;User ID=[your_username_here]@[your_sql_database_server_name_here];Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Unlike SQL Server in an Azure virtual machine, with Azure SQL Database you do not have to secure the operating system itself. However, all Azure subscription administrators have access to the SQL Database instance, and you should limit subscription administrators.

Source of Information : Migrating SQL Server Databases to Azure

Configuring and securing connections to SQL Server in an Azure virtual machine

Labels:

With an Azure virtual machine, you have several options to restrict and secure connections to your SQL Server instance. The virtual network for your Azure virtual machine is a logical isolation of the Azure cloud dedicated to your subscription. You can fully control the IP address blocks, DNS settings, security policies, and route tables within this network, similarly to how you use these mechanisms to control your on-premises network. You can also segment your virtual network into subnets to further control access to the virtual machines on your virtual network that host your SQL Server instances.

In addition, you can connect the virtual network to your on-premises network using one of the connectivity options available for Azure virtual machines. In essence, you can expand your on-premises network to your Azure virtual network, delivering the benefit of enterprise scale that Azure provides. Finally, you can set up and configure an Azure virtual machine as a domain controller and join your SQL Server virtual machine to this Azure domain controller. This Azure domain controller can be federated with Azure Active Directory, be federated with your on-premises Active Directory, or be a controller within your existing on-premises Active Directory. A full discussion of your options and best practices for configuring a secure connection to your on-premises network is beyond the scope of this ebook. If you do so, you can join your virtual machine to your on-premises Active Directory environment and use Windows user accounts for authentication.

In addition to configuring and securing the virtual network to which your Azure virtual network is connected, you should take these security steps:
 Use a unique local administrator account for your virtual machine that does not have a name of Administrator.

 Use complex strong passwords for all of your accounts, Windows and SQL Server. For more information about how to create a strong password, see the Create Strong Passwords article in the Safety and Security Center.

 Enable encrypted connections for your SQL Server instance and configure your SQL Server instance with a signed certificate.

 Use Windows firewall rules to control database engine access.

 If your virtual machines should be accessed only from a specific network, use network security groups (NSGs) to control traffic and restrict access to certain IP addresses or network subnets. An NSG contains access control rules that allow or deny traffic based on traffic direction, protocol, source address and port, and destination address and port.

Source of Information : Migrating SQL Server Databases to Azure

Migrating a compatible SQL Server database to SQL Database

Labels:

To migrate a compatible SQL Server database to Azure SQL Database, Microsoft provides several migration methods for various scenarios. The method you choose depends upon your tolerance for downtime, the size of your SQL Server database, and the speed and quality of your connection to the Microsoft Azure cloud.

If you can afford some downtime or you are performing a test migration of a production database for later migration, consider one of the following three methods:

 SSMS Migration Wizard: For small to medium databases, migrating a compatible SQL Server 2005 or later database is as simple as running the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio.

 Export to BACPAC file and then Import from BACPAC file: If you have connectivity challenges (no connectivity, low bandwidth, or timeout issues) and for medium to large databases, use this manual method. With this method, you export the SQL Server schema and data to a BACPAC file and then import the BACPAC file into SQL Database using either the Deploy Database to Microsoft Azure SQL Database Wizard in SQL Server Management Studio or the SqlPackage command-prompt utility.

 Use BACPAC and BCP together: Use a BACPAC file and BCP for very large databases to achieve greater parallelization for increased performance, albeit with greater complexity. With this method, migrate the schema and the data separately.

  • Export the schema only to a BACPAC file.
  • Import the schema only from the BACPAC file into SQL Database.
  • Use BCP to extract the data into flat files and then parallel load these files into Azure SQL Database.
Source of Information : Migrating SQL Server Databases to Azure

Determining and resolving Azure SQL Database V12 compatibility issues

Labels:

To export to a BACPAC file, the database must be compatible with Azure SQL Database V12. The export will fail if the database is not compatible. With SQL Database V12, there are very few remaining compatibility issues other than server-level and cross-database operations. Databases and applications that rely on partially supported or unsupported functions will need some reengineering to fix these incompatibilities before the SQL Server database can be migrated. Review these partially supported or unsupported functions before migrating the SQL Database.

The tooling to detect and fix these incompatibilities is improving, but it still requires some manual intervention in many cases. The list below describes the tooling as it currently exists, including new tooling coming out in preview mode in May 2016.

 The Microsoft SQL Server 2016 Upgrade Advisor preview (UA): This stand-alone tool that currently is in preview will detect and generate a report of SQL Database V12 incompatibilities. This tool does not yet have all of the most recent compatibility rules (but will soon). If no errors are detected, you can continue and complete the migration to SQL Database. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.
As mentioned previously, UA will be updated in late April or early May 2016 and become Data Migration Assistant (DMA). DMA will contain the most recent compatibility rules and will, over time, enable you to fix incompatibilities directly in the tool. This functionality is still coming online, so stay tuned.

 SQL Server Data Tools for Visual Studio (SSDT): For Azure SQL Database V12, the best tool today is SSDT because it uses the most recent compatibility rules to detect SQL Database V12 incompatibilities. To use this option, download the newest version of SSDT. If incompatibilities are detected, you can fix detected issues directly in SSDT.

 SqlPackage: SqlPackage is a command-prompt utility that tests for and, if found, generates a report containing detected compatibility issues. If you use this tool, make sure you use the most recent version (follow the link provided) to use the most recent compatibility rules. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.

 The Export Data-tier Application Wizard in SQL Server Management Studio: This wizard will detect and report errors to the screen. If no errors are detected, you can continue and complete the migration to SQL Database. If errors are detected, you must use another tool to fix any detected compatibility issues. SSDT is the recommended tool.

 SQL Azure Migration Wizard (SAMW): For Azure SQL Database V11, the best tool was SAMW. This is a community-supported CodePlex tool that has not been updated fully for Azure SQL Database V12 and generally is not the best method to use to migrate to Azure SQL Database V12.

Source of Information : Migrating SQL Server Databases to Azure

Migrating using full database and transaction log backups to minimize downtime

Labels:

To minimize downtime involved in a migration using a full database backup, you can use a combination of a full database backup, a differential backup (manual method only), and multiple transaction log backups. By using this method, you will keep downtime to a minimum. This method commonly is used with medium to large databases where minimizing downtime is of paramount importance. To test your database in Azure before migrating a production system, either use the full database backup method or use this method to test and verify the migration steps before the actual production migration.

 Manual method: Back up, copy, and then restore (without recovery) a full database backup, a differential backup (optional), and multiple transaction logs while keeping your production system operational. Continue taking and applying transaction log backups without recovery until you are ready to switch over. Apply the final transaction log backup with recovery when you are ready to switch your clients/applications over to connect to the SQL Server instance in the Azure virtual machine rather than the on-premises database.

 AlwaysOn Availability Group method: If you have an on-premises AlwaysOn Availability Group, you can extend the availability group to SQL Server in your virtual machine. Configure your SQL Server instance in the Azure virtual machine as an AlwaysOn replica, seed the replica with a full database backup, keep the replica current with transaction log backups that are applied automatically, and then fail over to the Azure replica when you are ready to switch over your clients to the SQL Server instance in the Azure virtual machine (and turn off AlwaysOn in the source database).

Source of Information : Migrating SQL Server Databases to Azure

Migrating using full database and transaction log backups to minimize downtime

Labels:

To minimize downtime involved in a migration using a full database backup, you can use a combination of a full database backup, a differential backup (manual method only), and multiple transaction log backups. By using this method, you will keep downtime to a minimum. This method commonly is used with medium to large databases where minimizing downtime is of paramount importance. To test your database in Azure before migrating a production system, either use the full database backup method or use this method to test and verify the migration steps before the actual production migration.

 Manual method: Back up, copy, and then restore (without recovery) a full database backup, a differential backup (optional), and multiple transaction logs while keeping your production system operational. Continue taking and applying transaction log backups without recovery until you are ready to switch over. Apply the final transaction log backup with recovery when you are ready to switch your clients/applications over to connect to the SQL Server instance in the Azure virtual machine rather than the on-premises database.

 AlwaysOn Availability Group method: If you have an on-premises AlwaysOn Availability Group, you can extend the availability group to SQL Server in your virtual machine. Configure your SQL Server instance in the Azure virtual machine as an AlwaysOn replica, seed the replica with a full database backup, keep the replica current with transaction log backups that are applied automatically, and then fail over to the Azure replica when you are ready to switch over your clients to the SQL Server instance in the Azure virtual machine (and turn off AlwaysOn in the source database).

Source of Information : Migrating SQL Server Databases to Azure

Migrating using full database backup for simplicity

Labels:

To use the full database backup method with SQL Server, select one of the following mechanisms and do the following:

 Manually back up your source database and then restore it to your SQL Server instance in your virtual machine using SQL Server Management Studio. You can back it up and restore it using one of these methods:

  • Back up to a local file, copy backup to your virtual machine (or to blob storage), and then restore your backup file into your SQL Server instance in your virtual machine. We will walk through this method at the end of this chapter.
  • Backup to URL and then restore from URL. This method is supported only for SQL Server 2012 SP1 CU2 or newer.


 Use the Deploy Database to a Microsoft Azure VM Wizard in SQL Server 2016 SQL Server Management Studio to back up your source database and then restore it to your SQL Server instance in your virtual machine. This method currently is not recommended because this wizard has not yet been updated to support Azure Resource Manager (ARM)–deployed virtual machines.

 Use the Microsoft SQL Server 2016 Upgrade Advisor to back up your source database and then restore it to your SQL Server instance in your virtual machine. As mentioned previously, the SQL Server 2016 Upgrade Advisor currently is in preview, and a new version called Data Migration Assistant (DMA) will be released in late April or early May 2016. As this tool matures, it will become the preferred method. Until Data Migration Assistant is available, this method is not recommended.

You also could detach your data and log files, copy those files into your virtual machine, and then attach those files. However, this method has no advantage over the database backup and restore method.

Source of Information : Migrating SQL Server Databases to Azure

Migrating a SQL Server user database to a SQL Server instance in an Azure virtual machine

Labels:

When migrating a SQL Server user database to a SQL Server instance in an Azure virtual machine, the underlying mechanism generally uses the SQL Server backup functionality (using transactional replication also is an option, and this option is discussed in its own section later in this chapter). The options that use a variation of the SQL Server backup technology are discussed in this section.

Using database backups, you can:
 Use a full database backup. For simplicity, you can perform the user database migration using a SQL Server full database backup. This method is most appropriate for small databases where the downtime is short; for scenarios in which you can afford the amount of downtime required to back up, copy, and restore the database to the SQL Server instance in the Azure virtual machine; and for scenarios in which you just want to test SQL Server in an Azure virtual machine prior to an actual migration.

 Use a combination of full database and transaction log backups to minimize downtime on your production system.

 Use AlwaysOn Availability Groups and create a replica of your existing on-premises user database on your Azure virtual machine (or replicas of a group of user databases that must be migrated together).

To migrate a SQL Server 2000 database to a supported configuration in an Azure virtual machine, you must first upgrade the database to SQL Server 2005 SP4 or greater. When migrating using these methods, encrypt your backups and use encrypted connections to ensure your data is secure during the migration process.

Source of Information : Migrating SQL Server Databases to Azure

Alltop, all the top stories
BlogMalaysia.com
All Malaysian Bloggers Project
Computer Blogs - BlogCatalog Blog Directory Add to Technorati Favorites
Technorati Profile
Top Computers blogs