Design an Azure SQL database and connect with C# and ADO.NET

Design an Azure SQL database and connect with C# and ADO.NET

Contributors
Gene Milener  Carl Rabeler  Cam Soper  Kris Crider
Azure SQL Database is a relational database-as-a service (DBaaS) in the Microsoft Cloud (Azure). In this tutorial, you learn how to use the Azure portal and ADO.NET with Visual Studio to:

Create a database in the Azure portal
Set up a server-level firewall rule in the Azure portal
Connect to the database with ADO.NET and Visual Studio
Create tables with ADO.NET
Insert, update, and delete data with ADO.NET
Query data ADO.NET
If you don't have an Azure subscription, create a free account before you begin.

Prerequisites
An installation of Visual Studio Community 2017, Visual Studio Professional 2017, or Visual Studio Enterprise 2017.

Log in to the Azure portal
Log in to the Azure portal.

Create a blank SQL database
An Azure SQL database is created with a defined set of compute and storage resources. The database is created within an Azure resource group and in an Azure SQL Database logical server.

Follow these steps to create a blank SQL database.

Click Create a resource in the upper left-hand corner of the Azure portal.

Select Databases from the New page, and select Create under SQL Database on the New page.

create empty-database

Fill out the SQL Database form with the following information, as shown on the preceding image:

Setting       Suggested value Description 
Database name mySampleDatabase For valid database names, see Database Identifiers.
Subscription Your subscription For details about your subscriptions, see Subscriptions.
Resource group myResourceGroup For valid resource group names, see Naming rules and restrictions.
Select source Blank database Specifies that a blank database should be created.
Click Server to create and configure a new server for your new database. Fill out the New server form with the following information: AZURE ONLINE TRAINING 

Setting       Suggested value Description 
Server name Any globally unique name For valid server names, see Naming rules and restrictions.
Server admin login Any valid name For valid login names, see Database Identifiers.
Password Any valid password Your password must have at least 8 characters and must contain characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters.
Location Any valid location For information about regions, see Azure Regions.
create database-server

Click Select.

Click Pricing tier to specify the service tier, the number of DTUs, and the amount of storage. Explore the options for the amount of DTUs and storage that is available to you for each service tier.

For this tutorial, select the Standard service tier and then use the slider to select 100 DTUs (S3) and 400 GB of storage.

create database-s1

Accept the preview terms to use the Add-on Storage option.

Important

* Storage sizes greater than the amount of included storage are in preview and extra costs apply. For details, see SQL Database pricing.

* In the Premium tier, more than 1 TB of storage is currently available in the following regions: Canada Central, Canada East, France Central, Germany Central, Japan East, Korea Central, South Central US, South East Asia, US East2, West US, US Gov Virginia, and West Europe. See P11-P15 Current Limitations.

After selecting the server tier, the number of DTUs, and the amount of storage, click Apply.

Select a collation for the blank database (for this tutorial, use the default value). For more information about collations, see Collations

Click Create to provision the database. Provisioning takes about a minute and a half to complete.

On the toolbar, click Notifications to monitor the deployment process.

notification

Create a server-level firewall rule
The SQL Database service creates a firewall at the server-level that prevents external applications and tools from connecting to the server or any databases on the server unless a firewall rule is created to open the firewall for specific IP addresses. Follow these steps to create a SQL Database server-level firewall rule for your client's IP address and enable external connectivity through the SQL Database firewall for your IP address only.AZURE ONLINE TRAINING HYDERABAD

Note

SQL Database communicates over port 1433. If you are trying to connect from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 1433.

After the deployment completes, click SQL databases from the left-hand menu and then click mySampleDatabase on the SQL databases page. The overview page for your database opens, showing you the fully qualified server name (such as mynewserver20170824.database.windows.net) and provides options for further configuration.

Copy this fully qualified server name for use to connect to your server and its databases in subsequent quick starts.

server name

Click Set server firewall on the toolbar. The Firewall settings page for the SQL Database server opens.

server firewall rule

Click Add client IP on the toolbar to add your current IP address to a new firewall rule. A firewall rule can open port 1433 for a single IP address or a range of IP addresses.

Click Save. A server-level firewall rule is created for your current IP address opening port 1433 on the logical server.

Click OK and then close the Firewall settings page.

You can now connect to the SQL Database server and its databases using SQL Server Management Studio or another tool of your choice from this IP address using the server admin account created previously.

Important

By default, access through the SQL Database firewall is enabled for all Azure services. Click OFF on this page to disable for all Azure services.

SQL server connection information
Get the fully qualified server name for your Azure SQL Database server in the Azure portal. You use the fully qualified server name to connect to your server using SQL Server Management Studio.

Log in to the Azure portal.
Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
In the Essentials pane in the Azure portal page for your database, locate and then copy the Server name.

connection information

Get more information on IOS from OnlineITGuru AZURE ONLINE COURSE


C# program example
The next sections of this article present a C# program that uses ADO.NET to send Transact-SQL statements to the SQL database. The C# program performs the following actions:

Connects to our SQL database using ADO.NET.
Creates tables.
Populates the tables with data, by issuing T-SQL INSERT statements.
Updates data by use of a join.
Deletes data by use of a join.
Selects data rows by use of a join.
Closes the connection (which drops any temporary tables from tempdb).
The C# program contains:

C# code to connect to the database.
Methods that return the T-SQL source code.
Two methods that submit the T-SQL to the database.
To compile and run
This C# program is logically one .cs file. But here the program is physically divided into several code blocks, to make each block easier to see and understand. To compile and run this program, do the following:

Create a C# project in Visual Studio.
The project type should be a console application, from something like the following hierarchy: Templates > Visual C# > Windows Classic Desktop > Console App (.NET Framework).
In the file Program.cs, erase the small starter lines of code.
Into Program.cs, copy and paste each of the following blocks, in the same sequence they are presented here.
In Program.cs, edit the following values in the Main method:

cb.DataSource
cd.UserID
cb.Password
InitialCatalog
Verify that the assembly System.Data.dll is referenced. To verify, expand the References node in the Solution Explorer pane.

To build the program in Visual Studio, click the Build menu.
To run the program from Visual Studio, click the Start button. The report output is displayed in a cmd.exe window.
Note

You have the option of editing the T-SQL to add a leading # to the table names, which creates them as temporary tables in tempdb. This can be useful for demonstration purposes, when no test database is available. Temporary tables are deleted automatically when the connection closes. Any REFERENCES for foreign keys are not enforced for temporary tables.


C# block 1: Connect by using ADO.NET
Next
C#

Copy
using System;
using System.Data.SqlClient;   // System.Data.dll
//using System.Data;           // For:  SqlDbType , ParameterDirection

namespace csharp_db_test
{
   class Program
   {
      static void Main(string[] args)
      {
         try
         {
            var cb = new SqlConnectionStringBuilder();
            cb.DataSource = "your_server.database.windows.net";
            cb.UserID = "your_user";
            cb.Password = "your_password";
            cb.InitialCatalog = "your_database";

            using (var connection = new SqlConnection(cb.ConnectionString))
            {
               connection.Open();

               Submit_Tsql_NonQuery(connection, "2 - Create-Tables",
                  Build_2_Tsql_CreateTables());

               Submit_Tsql_NonQuery(connection, "3 - Inserts",
                  Build_3_Tsql_Inserts());

               Submit_Tsql_NonQuery(connection, "4 - Update-Join",
                  Build_4_Tsql_UpdateJoin(),
                  "@csharpParmDepartmentName", "Accounting");

               Submit_Tsql_NonQuery(connection, "5 - Delete-Join",
                  Build_5_Tsql_DeleteJoin(),
                  "@csharpParmDepartmentName", "Legal");

               Submit_6_Tsql_SelectEmployees(connection);
            }
         }
         catch (SqlException e)
         {
            Console.WriteLine(e.ToString());
         }
         Console.WriteLine("View the report output here, then press any key to end the program...");
         Console.ReadKey();
      }

C# block 2: T-SQL to create tables
Previous   /   Next
C#

Copy
      static string Build_2_Tsql_CreateTables()
      {
         return @"
DROP TABLE IF EXISTS tabEmployee;
DROP TABLE IF EXISTS tabDepartment;  -- Drop parent table last.


CREATE TABLE tabDepartment
(
   DepartmentCode  nchar(4)          not null
      PRIMARY KEY,
   DepartmentName  nvarchar(128)     not null
);

CREATE TABLE tabEmployee
(
   EmployeeGuid    uniqueIdentifier  not null  default NewId()
      PRIMARY KEY,
   EmployeeName    nvarchar(128)     not null,
   EmployeeLevel   int               not null,
   DepartmentCode  nchar(4)              null
      REFERENCES tabDepartment (DepartmentCode)  -- (REFERENCES would be disallowed on temporary tables.)
);
";
      }
Entity Relationship Diagram (ERD)
The preceding CREATE TABLE statements involve the REFERENCES keyword to create a foreign key (FK) relationship between two tables. If you are using tempdb, comment out the --REFERENCES keyword using a pair of leading dashes.

Next is an ERD that displays the relationship between the two tables. The values in the #tabEmployee.DepartmentCode child column are limited to the values present in the #tabDepartment.Department parent column.

ERD showing foreign key


C# block 3: T-SQL to insert data
Previous   /   Next
C#

Copy
      static string Build_3_Tsql_Inserts()
      {
         return @"
-- The company has these departments.
INSERT INTO tabDepartment
   (DepartmentCode, DepartmentName)
      VALUES
   ('acct', 'Accounting'),
   ('hres', 'Human Resources'),
   ('legl', 'Legal');

-- The company has these employees, each in one department.
INSERT INTO tabEmployee
   (EmployeeName, EmployeeLevel, DepartmentCode)
      VALUES
   ('Alison'  , 19, 'acct'),
   ('Barbara' , 17, 'hres'),
   ('Carol'   , 21, 'acct'),
   ('Deborah' , 24, 'legl'),
   ('Elle'    , 15, null);
";
      }

C# block 4: T-SQL to update-join
Previous   /   Next
C#

Copy
      static string Build_4_Tsql_UpdateJoin()
      {
         return @"
DECLARE @DName1  nvarchar(128) = @csharpParmDepartmentName;  --'Accounting';


-- Promote everyone in one department (see @parm...).
UPDATE empl
   SET
      empl.EmployeeLevel += 1
   FROM
      tabEmployee   as empl
      INNER JOIN
      tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
   WHERE
      dept.DepartmentName = @DName1;
";
      }

C# block 5: T-SQL to delete-join
Previous   /   Next
C#

Copy
      static string Build_5_Tsql_DeleteJoin()
      {
         return @"
DECLARE @DName2  nvarchar(128);
SET @DName2 = @csharpParmDepartmentName;  --'Legal';


-- Right size the Legal department.
DELETE empl
   FROM
      tabEmployee   as empl
      INNER JOIN
      tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
   WHERE
      dept.DepartmentName = @DName2

-- Disband the Legal department.
DELETE tabDepartment
   WHERE DepartmentName = @DName2;
";
      }

C# block 6: T-SQL to select rows
Previous   /   Next
C#

Copy
      static string Build_6_Tsql_SelectEmployees()
      {
         return @"
-- Look at all the final Employees.
SELECT
      empl.EmployeeGuid,
      empl.EmployeeName,
      empl.EmployeeLevel,
      empl.DepartmentCode,
      dept.DepartmentName
   FROM
      tabEmployee   as empl
      LEFT OUTER JOIN
      tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
   ORDER BY
      EmployeeName;
";
      }

C# block 6b: ExecuteReader
Previous   /   Next
This method is designed to run the T-SQL SELECT statement that is built by the Build_6_Tsql_SelectEmployees method.

C#

Copy
      static void Submit_6_Tsql_SelectEmployees(SqlConnection connection)
      {
         Console.WriteLine();
         Console.WriteLine("=================================");
         Console.WriteLine("Now, SelectEmployees (6)...");

         string tsql = Build_6_Tsql_SelectEmployees();

         using (var command = new SqlCommand(tsql, connection))
         {
            using (SqlDataReader reader = command.ExecuteReader())
            {
               while (reader.Read())
               {
                  Console.WriteLine("{0} , {1} , {2} , {3} , {4}",
                     reader.GetGuid(0),
                     reader.GetString(1),
                     reader.GetInt32(2),
                     (reader.IsDBNull(3)) ? "NULL" : reader.GetString(3),
                     (reader.IsDBNull(4)) ? "NULL" : reader.GetString(4));
               }
            }
         }
      }

C# block 7: ExecuteNonQuery
Previous   /   Next
This method is called for operations that modify the data content of tables without returning any data rows.

C#

Copy
      static void Submit_Tsql_NonQuery(
         SqlConnection connection,
         string tsqlPurpose,
         string tsqlSourceCode,
         string parameterName = null,
         string parameterValue = null
         )
      {
         Console.WriteLine();
         Console.WriteLine("=================================");
         Console.WriteLine("T-SQL to {0}...", tsqlPurpose);

         using (var command = new SqlCommand(tsqlSourceCode, connection))
         {
            if (parameterName != null)
            {
               command.Parameters.AddWithValue(  // Or, use SqlParameter class.
                  parameterName,
                  parameterValue);
            }
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine(rowsAffected + " = rows affected.");
         }
      }
   } // EndOfClass
}

C# block 8: Actual test output to the console
Previous
This section captures the output that the program sent to the console. Only the guid values vary between test runs.

text

Copy
[C:\csharp_db_test\csharp_db_test\bin\Debug\]
>> csharp_db_test.exe

=================================
Now, CreateTables (10)...

=================================
Now, Inserts (20)...

=================================
Now, UpdateJoin (30)...
2 rows affected, by UpdateJoin.

=================================
Now, DeleteJoin (40)...

=================================
Now, SelectEmployees (50)...
0199be49-a2ed-4e35-94b7-e936acf1cd75 , Alison , 20 , acct , Accounting
f0d3d147-64cf-4420-b9f9-76e6e0a32567 , Barbara , 17 , hres , Human Resources
cf4caede-e237-42d2-b61d-72114c7e3afa , Carol , 22 , acct , Accounting
cdde7727-bcfd-4f72-a665-87199c415f8b , Elle , 15 , NULL , NULL

[C:\csharp_db_test\csharp_db_test\bin\Debug\]
>>
Next steps
In this tutorial, you learned basic database tasks such as create a database and tables, load and query data, and restore the database to a previous point in time. You learned how to:

Create a database
Set up a firewall rule
Connect to the database with Visual Studio and C#
Create tables
Insert, update, and delete data
Query data
Advance to the next tutorial to learn about migrating your data and azure online training India

Read More

SQL database Implement a geo-distributed database

Implement a geo-distributed database

Contributors
Carl Rabeler  Kris Crider
In this tutorial, you configure an Azure SQL database and application for failover to a remote region, and then test your failover plan. You learn how to:

Create database users and grant them permissions
Set up a database-level firewall rule
Create a geo-replication failover group
Create and compile a Java application to query an Azure SQL database
Perform a disaster recovery drill
If you don't have an Azure subscription, create a free account before you begin.

Prerequisites
To complete this tutorial, make sure the following prerequisites are completed:

Installed the latest Azure PowerShell.
Installed an Azure SQL database. This tutorial uses the AdventureWorksLT sample database with a name of mySampleDatabase from one of these quick starts:

Create DB - Portal
Create DB - CLI
Create DB - PowerShell
Have identified a method to execute SQL scripts against your database, you can use one of the following query tools:

The query editor in the Azure portal. For more information on using the query editor in the Azure portal, see Connect and query using Query Editor.
The newest version of SQL Server Management Studio, which is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows.
The newest version of Visual Studio Code, which is a graphical code editor for Linux, macOS, and Windows that supports extensions, including the mssql extension for querying Microsoft SQL Server, Azure SQL Database, and SQL Data Warehouse. For more information on using this tool with Azure SQL Database, see Connect and query with VS Code.
Create database users and grant permissions
Connect to your database and create user accounts using one of the following query tools:

Get more information on IOS from OnlineITGuru azure online training 

The Query editor in the Azure portal
SQL Server Management Studio
Visual Studio Code
These user accounts replicate automatically to your secondary server (and be kept in sync). To use SQL Server Management Studio or Visual Studio Code, you may need to configure a firewall rule if you are connecting from a client at an IP address for which you have not yet configured a firewall. For detailed steps, see Create a server-level firewall rule.

In a query window, execute the following query to create two user accounts in your database. This script grants db_owner permissions to the app_admin account and grants SELECT and UPDATE permissions to the app_user account.

SQL

Copy
CREATE USER app_admin WITH PASSWORD = 'ChangeYourPassword1';
--Add SQL user to db_owner role
ALTER ROLE db_owner ADD MEMBER app_admin;
--Create additional SQL user
CREATE USER app_user WITH PASSWORD = 'ChangeYourPassword1';
--grant permission to SalesLT schema
GRANT SELECT, INSERT, DELETE, UPDATE ON SalesLT.Product TO app_user;
Create database-level firewall
Create a database-level firewall rule for your SQL database. This database-level firewall rule replicates automatically to the secondary server that you create in this tutorial. For simplicity (in this tutorial), use the public IP address of the computer on which you are performing the steps in this tutorial. To determine the IP address used for the server-level firewall rule for your current computer, see Create a server-level firewall.

In your open query window, replace the previous query with the following query, replacing the IP addresses with the appropriate IP addresses for your environment.

SQL

Copy
-- Create database-level firewall setting for your public IP address
EXECUTE sp_set_database_firewall_rule @name = N'myGeoReplicationFirewallRule',@start_ip_address = '0.0.0.0', @end_ip_address = '0.0.0.0';
Create an active geo-replication auto failover group
Using Azure PowerShell, create an active geo-replication auto failover group between your existing Azure SQL server and the new empty Azure SQL server in an Azure region, and then add your sample database to the failover group.

Important

These cmdlets require Azure PowerShell 4.0. This sample requires the Azure PowerShell module version 5.1.1 or later. Run Get-Module -ListAvailable AzureRM to find the version. If you need to install or upgrade, see Install Azure PowerShell module. Run Login-AzureRmAccount to create a connection with Azure.

Populate variables for your PowerShell scripts using the values for your existing server and sample database, and provide a globally unique value for failover group name.

Get more information on IOS from OnlineITGuru azure online Training Hyderabad 

PowerShell

Copy
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
$myresourcegroupname = "<your resource group name>"
$mylocation = "<your resource group location>"
$myservername = "<your existing server name>"
$mydatabasename = "mySampleDatabase"
$mydrlocation = "<your disaster recovery location>"
$mydrservername = "<your disaster recovery server name>"
$myfailovergroupname = "<your unique failover group name>"
Create an empty backup server in your failover region.

PowerShell

Copy
$mydrserver = New-AzureRmSqlServer -ResourceGroupName $myresourcegroupname `
   -ServerName $mydrservername `
   -Location $mydrlocation `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$mydrserver 
Create a failover group between the two servers.

PowerShell

Copy
$myfailovergroup = New-AzureRMSqlDatabaseFailoverGroup `
   –ResourceGroupName $myresourcegroupname `
   -ServerName $myservername `
   -PartnerServerName $mydrservername  `
   –FailoverGroupName $myfailovergroupname `
   –FailoverPolicy Automatic `
   -GracePeriodWithDataLossHours 2
$myfailovergroup 
Add your database to the failover group.

PowerShell

Copy
$myfailovergroup = Get-AzureRmSqlDatabase `
   -ResourceGroupName $myresourcegroupname `
   -ServerName $myservername `
   -DatabaseName $mydatabasename | `
 Add-AzureRmSqlDatabaseToFailoverGroup `
   -ResourceGroupName $myresourcegroupname ` `
   -ServerName $myservername `
   -FailoverGroupName $myfailovergroupname
$myfailovergroup 
Install Java software
The steps in this section assume that you are familiar with developing using Java and are new to working with Azure SQL Database.

Mac OS
Open your terminal and navigate to a directory where you plan on creating your Java project. Install brew and Maven by entering the following commands:

bash

Copy
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install maven
For detailed guidance on installing and configuring Java and Maven environment, go the Build an app using SQL Server, select Java, select MacOS, and then follow the detailed instructions for configuring Java and Maven in step 1.2 and 1.3.

Linux (Ubuntu)
Open your terminal and navigate to a directory where you plan on creating your Java project. Install Maven by entering the following commands:

bash

Copy
sudo apt-get install maven
For detailed guidance on installing and configuring Java and Maven environment, go the Build an app using SQL Server, select Java, select Ubuntu, and then follow the detailed instructions for configuring Java and Maven in step 1.2, 1.3, and 1.4.

Windows
Install Maven using the official installer. Use Maven to help manage dependencies, build, test, and run your Java project. For detailed guidance on installing and configuring Java and Maven environment, go the Build an app using SQL Server, select Java, select Windows, and then follow the detailed instructions for configuring Java and Maven in step 1.2 and 1.3.

Create SqlDbSample project
In the command console (such as Bash), create a Maven project. bash mvn archetype:generate "-DgroupId=com.sqldbsamples" "-DartifactId=SqlDbSample" "-DarchetypeArtifactId=maven-archetype-quickstart" "-Dversion=1.0.0"
Type Y and click Enter.
Change directories into your newly created project.

bash

Copy
cd SqlDbSamples
Using your favorite editor, open the pom.xml file in your project folder.

Add the Microsoft JDBC Driver for SQL Server dependency to your Maven project by opening your favorite text editor and copying and pasting the following lines into your pom.xml file. Do not overwrite the existing values prepopulated in the file. The JDBC dependency must be pasted within the larger “dependencies” section ( ).

XML

Copy
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
 <version>6.1.0.jre8</version>
</dependency>
Specify the version of Java to compile the project against by adding the following “properties” section into the pom.xml file after the "dependencies" section.

XML

Copy
<properties>
  <maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
</properties>
Add the following "build" section into the pom.xml file after the "properties" section to support manifest files in jars.

XML

Copy
<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-jar-plugin</artifactId>
      <version>3.0.0</version>
      <configuration>
        <archive>
          <manifest>
            <mainClass>com.sqldbsamples.App</mainClass>
          </manifest>
        </archive>
     </configuration>
    </plugin>
  </plugins>
</build>
Save and close the pom.xml file.
Open the App.java file (C:\apache-maven-3.5.0\SqlDbSample\src\main\java\com\sqldbsamples\App.java) and replace the contents with the following contents. Replace the failover group name with the name for your failover group. If you have changed the values for the database name, user, or password, change those values as well.

Java

Copy
package com.sqldbsamples;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.util.Date;
import java.util.concurrent.TimeUnit;

public class App {

   private static final String FAILOVER_GROUP_NAME = "myfailovergroupname";

   private static final String DB_NAME = "mySampleDatabase";
   private static final String USER = "app_user";
   private static final String PASSWORD = "ChangeYourPassword1";

   private static final String READ_WRITE_URL = String.format("jdbc:sqlserver://%s.database.windows.net:1433;database=%s;user=%s;password=%s;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;", FAILOVER_GROUP_NAME, DB_NAME, USER, PASSWORD);
   private static final String READ_ONLY_URL = String.format("jdbc:sqlserver://%s.secondary.database.windows.net:1433;database=%s;user=%s;password=%s;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;", FAILOVER_GROUP_NAME, DB_NAME, USER, PASSWORD);

   public static void main(String[] args) {
      System.out.println("#######################################");
      System.out.println("## GEO DISTRIBUTED DATABASE TUTORIAL ##");
      System.out.println("#######################################");
      System.out.println("");

      int highWaterMark = getHighWaterMarkId();

      try {
         for(int i = 1; i < 1000; i++) {
             //  loop will run for about 1 hour
             System.out.print(i + ": insert on primary " + (insertData((highWaterMark + i))?"successful":"failed"));
             TimeUnit.SECONDS.sleep(1);
             System.out.print(", read from secondary " + (selectData((highWaterMark + i))?"successful":"failed") + "\n");
             TimeUnit.SECONDS.sleep(3);
         }
      } catch(Exception e) {
         e.printStackTrace();
   }
}

private static boolean insertData(int id) {
   // Insert data into the product table with a unique product name that we can use to find the product again later
   String sql = "INSERT INTO SalesLT.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES (?,?,?,?,?,?);";

   try (Connection connection = DriverManager.getConnection(READ_WRITE_URL);
           PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setString(1, "BrandNewProduct" + id);
      pstmt.setInt(2, 200989 + id + 10000);
      pstmt.setString(3, "Blue");
      pstmt.setDouble(4, 75.00);
      pstmt.setDouble(5, 89.99);
      pstmt.setTimestamp(6, new Timestamp(new Date().getTime()));
      return (1 == pstmt.executeUpdate());
   } catch (Exception e) {
      return false;
   }
}

private static boolean selectData(int id) {
   // Query the data that was previously inserted into the primary database from the geo replicated database
   String sql = "SELECT Name, Color, ListPrice FROM SalesLT.Product WHERE Name = ?";

   try (Connection connection = DriverManager.getConnection(READ_ONLY_URL);
           PreparedStatement pstmt = connection.prepareStatement(sql)) {
      pstmt.setString(1, "BrandNewProduct" + id);
      try (ResultSet resultSet = pstmt.executeQuery()) {
         return resultSet.next();
      }
   } catch (Exception e) {
      return false;
   }
}

private static int getHighWaterMarkId() {
   // Query the high water mark id that is stored in the table to be able to make unique inserts
   String sql = "SELECT MAX(ProductId) FROM SalesLT.Product";
   int result = 1;

   try (Connection connection = DriverManager.getConnection(READ_WRITE_URL);
           Statement stmt = connection.createStatement();
           ResultSet resultSet = stmt.executeQuery(sql)) {
      if (resultSet.next()) {
          result =  resultSet.getInt(1);
         }
      } catch (Exception e) {
       e.printStackTrace();
      }
      return result;
   }
}
Save and close the App.java file.
Compile and run the SqlDbSample project
In the command console, execute to following command.

bash

Copy
mvn package
When finished, execute the following command to run the application (it runs for about 1 hour unless you stop it manually):

bash

Copy
mvn -q -e exec:java "-Dexec.mainClass=com.sqldbsamples.App"

#######################################
## GEO DISTRIBUTED DATABASE TUTORIAL ##
#######################################

1. insert on primary successful, read from secondary successful
2. insert on primary successful, read from secondary successful
3. insert on primary successful, read from secondary successful
Perform disaster recovery drill
Call manual failover of failover group.

PowerShell

Copy
Switch-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $myresourcegroupname  `
-ServerName $mydrservername `
-FailoverGroupName $myfailovergroupname
Observe the application results during failover. Some inserts fail while the DNS cache refreshes.

Find out which role your disaster recovery server is performing.

PowerShell

Copy
$mydrserver.ReplicationRole
Failback.

PowerShell

Copy
Switch-AzureRMSqlDatabaseFailoverGroup `
-ResourceGroupName $myresourcegroupname  `
-ServerName $myservername `
-FailoverGroupName $myfailovergroupname
Observe the application results during failback. Some inserts fail while the DNS cache refreshes.

Find out which role your disaster recovery server is performing.

PowerShell

Copy
$fileovergroup = Get-AzureRMSqlDatabaseFailoverGroup `
   -FailoverGroupName $myfailovergroupname `
   -ResourceGroupName $myresourcegroupname `
   -ServerName $mydrservername
$fileovergroup.ReplicationRole
Next steps
For more information, see Active geo-replication and failover groups.

Feedback
What type of feedback would you like to provide?

Give product feedback   Sign in to give documentation feedback
Our new feedback system is built on GitHub Issues. For more information on this change, please read our blog post. more informastion visit AZURE ONLINE COURSE 

There is currently no feedback for this document.

Read More

How Devops became useful to Microsoft Azure?

How Devops became useful to Microsoft Azure?

Devops could be a combination a mixture of 2 words particularly development and operations.  It implies that so as to complete the project the event team should integrate with operations so as deliver the project to the top user inside the required amount of the top user. This powerful Devops platform makes acceptable IT operations that we tend to were doing these days and Azure is that the best cloud computing service supplier in providing all the IT Services needed for the project.   These 2 were provided by totally different vendors. however there's some affiliation between them. these days we tend to ar reaching to see however Devops became useful to Microsoft Azure.

Before reaching to see their affiliation, allow us to have a glance at what's Infrastructure as a code?

Infrastructure as a Code (IAC) :

Infrastructure as a code (IAC) could be a kind of IT Infrastructure, that operations manage the code rather the manual method. it's typically referred as programmable infrastructure. The conception of IAC is comparable to programming scripts which might automatise the manually written method. Basically, scripts were written to automatise the manually written method within which a precise piece of code are going to be dead by multiple times. however the term IAC are going to be ever-changing during a totally different manner. Here the bound piece of code are going to be dead multiple numbers of times, by creating the changes to the scripts slightly.  Here the auto maker ought to have the information of code wherever he must modification and what will the output should return. IAC uses a high level or descriptive language to code a lot of versatile language and adaptive  process and readying method. The progress of IAC is shown below :

Get a more robust understanding of Microsoft Azure from OnlineITGuru through Azure online training

For example, the IAC is coded with Ansible, associate degree It configurations and management tool wherever it will install the MYSQL server to verify that it's been run properly. and serves within the creation of user accounts, management of recent accounts, putting in a replacement information,  deletion of recurrent databases so on. through this, the Operations team will ready to perform some code automation instead of looking on the developer.

Now let’s have an summary of however Devops became useful to Azure.

IAC is that the major conception employed in integration devops with Azure.  It includes the subsequent points:

Azure automation:   As we all know that Azure uses numerous tools so as to complete the project.  One the numerous tools that it uses in its Cloud surroundings is Devops that is largely accustomed automatise the method.  Devops powerful automation makes the work easier and smarter and reduces the manual work that must be done by the developers.

Desired State Configuration: each surroundings has its own configuration by default. Some default configuration my satisfy the top user necessities whereas some don't.In order to beat that drawback Azure surroundings has designed in such a fashion wherever the top user will switch to the required state configuration as per their demand within the IAC.

ARM templates:  Here ARM stands for Azure Resource manager templates. It the bespoken guide that may be created within the Azure surroundings, wherever the users will customise the guide and deploy within the Azure cloud surroundings. The declarative code doesn't have any readying info, however rather it contains the top state of the targeted surroundings. Moreover, when readying, you've got a capability to change and update them during a managementled and certain thanks to apply a version control to your infrastructure. More details learn azure online course

Benefits of IAC:

     Usage of declarative files

     Self-documented systems and processes

     Version management

 immutable  Infrastructure

 little changes in code instead of batches

    Agile and high accessibility of services.

Because of the on top of options and facilities, offered by IAC, and nice ability of devops in numerous environments, devops will be adopted simply in any cloud environments.

Recommended Audience:

Software developers

Read More