Skip to content

Latest commit

 

History

History
258 lines (233 loc) · 13.9 KB

5.8. Develop database solutions.md

File metadata and controls

258 lines (233 loc) · 13.9 KB
Raw

Develop database solutions

Develop solutions that use Cosmos DB storage

  • Create, read, update, and delete data by using appropriate APIs
    • Use async, use interfaces!
    • SQL API: Standard API.
      • DocumentClient.CreateDocumentAsync, DocumentClient.ReadDocumentAsync, DocumentClient.ReadDocumentFeedAsync (read all documents), DocumentClient.CreateDocumentQuery, DocumentClient.ReplaceDocumentAsync, DocumentClient.UpsertDocumentAsync, DocumentClient.DeleteDocumentAsync.
    • Azure Cosmos DB's API for MongoDB
    • Gremlin API: It's is used to store and operate on graph data. Gremlin API supports modeling Graph data and provides APIs to traverse through the graph data.
    • Cassandra API: You can switch from using Apache Cassandra to using Azure Cosmos DB 's Cassandra API, by just changing a connection string.
    • Table API: Azure Table storage can migrate to Azure Cosmos DB by using the Table API with no code changes and take advantage of premium capabilities.
  • Implement partitioning schemes
    • Cosmos DB uses hash-based partitioning to spread logical partitions across physical partitions. The partition key value of an item is hashed by Cosmos DB, and the hashed result determines the physical partition.
    • ❗ Limitations
      • A single logical partition is allowed an upper limit of 10 GB of storage.
      • Partitioned containers are configured with minimum throughput of 400 RU/s. Requests to the same partition key can't exceed the throughput allocated to a partition.
        • It's important to pick a partition key that doesn't result in "hot spots" within your application.
    • Syntatic partition keys

      • It's a best practice to have a partition key with many distinct values, such as hundreds or thousands.

      • Concatenate multiple properties of an item

        • E.g.

          {
"deviceId": "abc-123",
"date": 2018,
"partitionKey": "abc-123-2018"
}

      • Use a partition key with a random suffix

        • Distribute the workload more evenly is to append a random number at the end of the partition key value.
        • You can perform parallel write operations across partitions.
        • E.g. you might choose a random number between 1 and 400 and concatenate it as a suffix to the date.
          • like 2018-08-09.1, 2018-08-09.2, and so on, through 2018-08-09.400
          • This method results in better parallelism and overall higher throughput
        • The randomizing strategy can greatly improve write throughput, but it's difficult to read a specific item

      • Use a partition key with precalculated suffixes

        • Easier to read then randomizing.
        • E.g. you have ID, you create partitions key with date + hash of ID.
        • The writes are evenly spread across the partition key values, and across the partitions.
        • You can easily read a particular item and date because you can calculate the partition key value for a specific ID.
        • The benefit of this method is that you can avoid creating a single hot partition key.
          • A hot partition key is the partition key that takes all the workload.
    • You can provision throughput for a CosmosDb database or container during creation e.g. OfferThroughput = 100000
  • Set the appropriate consistency level for operations
    • Make the fundamental tradeoff between the read consistency vs. availability, latency, and throughput.
    • Strong consistency model (or linearization)
      • Adds a steep price of higher latency (in steady state) and reduced availability (during failures).
      • Easy to program applicatoins
    • Eventual consistency
      • Higher availability and better performance
      • Hard to program applications.
    • Consistensy levels in Cosmos Db (from strongest to weakest)
      • Strong, Bounded staleness, Session, Consistent prefix, Eventual
        • Strong: Always guaranteed to read the latest committed write.
        • Bounded staleness
          • Staleness can be configured in two ways:
            • The number of versions (K) of the item
            • The time interval (t) by which the reads might lag behind the writes
            • 💡 Recommended for high consistency
        • Session
          • Scoped to client session.
          • honors the consistent-prefix (assuming a single "writer" session), monotonic reads, monotonic writes, read-your-writes, and write-follows-reads guarantees
            • Monotonic reads: If a process reads the value of a data item x, any successive read operation on x by that process will always return that same value or a more recent value.
            • Monotonic writes: A write operation by a process on a data item X is completed before any successive write operation on X by the same process.
          • 💡 Recommended option for most scenerios.
        • Consistent prefix
          • Updates that are returned contain some prefix of all the updates, with no gaps. Consistent prefix guarantees that reads never see out-of-order writes.
        • Eventual: There's no ordering guarantee for reads. In the absence of any further writes, the replicas eventually converge.
      • Consistent prefix, and eventual consistency provide about two times the read throughput when compared with strong and bounded staleness.
      • For a given type of write operation, the write throughput for request units is identical for all consistency levels.
    • You can configure the default consistency level on your Azure Cosmos account at any time

Develop solutions that use a relational database

Provision and configure relational databases

  • Create single database
    1. Create a resource -> Databses -> SQL Database
      • Type database name, select subscription, resource group
      • Select source: Blank, Sample, Back-up
    2. Server -> Create new
      • Type server name, admin login, password, location
    3. Select pricing tier
      • Standard/Basic/Premium (sets minimum DTU)
      • Select DTUs & maximum storage
    4. You can query in SQL Database -> Query Editor
  • Create managed instance
    • Azure SQL Database Managed Instance is a fully managed SQL Server Database Engine Instance hosted in Azure cloud. This is the best PaaS option for migrating your SQL Server database to the cloud.
      • The managed instance deployment option provides high compatibility with on-premises SQL Server Database Engine.
      • Cross-database query support is one of the main reasons to use managed instance over Azure SQL Database.
        • ❗ Elastic Transactions / The Microsoft Distributed Transaction Coordinator is not supported.
    • Flow: Create a resource -> Managed Instance -> Azure SQL Managed Instance
      • Set e.g. collation, VNet
    • Features:
      • Availability: Always-on, backup
      • Security: Auditing, certificates, credentials through Azure Key Vault or Shared Access Signature
      • Set cryptographic providers
      • Configuration:
        • Collation
          • Defines a collation of a database or table column, or a collation cast operation when applied to character string expression
          • Sets the alphabetical order based on culter, e.g. Latin1_General_CS_AS_KS_WS, or Traditional_Spanish_ci_ai
        • SQL Server Agent: Server options, e.g. replication options such as snapshot, transaction-log reader, notifications.
      • Functionalities
        • Bulk insert / openrowset
        • Distributed transactions: ❗ Neither MSDTC (The Microsoft Distributed Transaction Coordinator) nor Elastic Transactions are currently supported in managed instances.
        • Service broker, stored procedures/functions/triggers
      • Behavior changes: Returns through e.g. ServerProperty('InstanceName')

Configure elastic pools for Azure SQL Database

  • SQL Database elastic pools
    • Simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands.
    • The databases are on a single Azure SQL Database server.
    • Share a set number of resources at a set price
  • Problem & solution
    • Problem:
      • A common application pattern is to provision a single database for each customer.
      • But different customers often have varying and unpredictable usage patterns, and it is difficult to predict the resource requirements of each individual database user.
      • You over-provision or under-provision
    • Solution
      • Elastic pools solve this problem by ensuring that databases get the performance resources they need when they need it. They provide a simple resource allocation mechanism within a predictable budget.
  • eDTUs are shared between many databases.
    • Costs 1.5x more than DTU's but pool eDTUs can be shared by many databases and fewer total eDTUs are needed.
  • Choose the correct pool size
    • Determine
      • Maximum resources utilized by all databases in the pool.
        • eDTUs: MAX(<Total number of DBs X average DTU utilization per DB>)
      • Maximum storage bytes utilized by all databases in the pool.
        • Sum of storage needed per DB
  • Elastic jobs
    • Management tasks are simplified by running scripts in elastic jobs.
  • Flow:
    1. Create pool: SQL elastic pool -> +Add -> Create pool on new server or existing SQL server.
    2. In pool -> Configure pool
      • Select a service tier, add databases to the pool, and configure the resource limits for the pool and its databases.
        • DTU-based resource limits
          • Max eDTUs per database, Min eDTUs per database, Max storage per database
        • vCore-based resource limits
          • Max vCores per database, Min vCores per database, Max storage per database
  • Rescaling
    • When rescaling pool, database connections are briefly dropped
    • The duration to rescale pool can depend on the total amount of storage space used by all databases in the pool

Elastic transactions

  • Allows you to write queries against multiple databases.

  • If both SQL databases supportselastic transactions, you can create server communication link.

    • New-AzureRmSqlServerCommunicationLink: Create a new communication relationship between two SQL Database servers in Azure SQL Database. The relationship is symmetric which means both servers can initiate transactions with the other server.

  • In C#

    • Multi-database applications

      • The TransactionScope class establishes an ambient transaction.

        • Ambient transaction = transaction that lives in the current thread.

      • All connections opened within the TransactionScope participate in the transaction. If different databases participate, the transaction is automatically elevated to a distributed transaction.

      • Ex:

        using (var scope = new TransactionScope())
{
    using (var conn1 = new SqlConnection(connStrDb1))
    {
        conn1.Open();
        SqlCommand cmd1 = conn1.CreateCommand();
        cmd1.CommandText = string.Format("insert into T1 values(1)");
        cmd1.ExecuteNonQuery();
    }

    using (var conn2 = new SqlConnection(connStrDb2))
    {
        conn2.Open();
        var cmd2 = conn2.CreateCommand();
        cmd2.CommandText = string.Format("insert into T2 values(2)");
        cmd2.ExecuteNonQuery();
    }

    scope.Complete();
}

      • Sharded database applications

        • Use OpenConnectionForKey method of the elastic database client library to open connections for a scaled out data tier.

        • C#:

          using (var scope = new TransactionScope())
{
    using (var conn1 = shardmap.OpenConnectionForKey(tenantId1, credentialsStr))
    {
        conn1.Open();
        SqlCommand cmd1 = conn1.CreateCommand();
        cmd1.CommandText = string.Format("insert into T1 values(1)");
        cmd1.ExecuteNonQuery();
    }

    using (var conn2 = shardmap.OpenConnectionForKey(tenantId2, credentialsStr))
    {
        conn2.Open();
        var cmd2 = conn2.CreateCommand();
        cmd2.CommandText = string.Format("insert into T1 values(2)");
        cmd2.ExecuteNonQuery();
    }

    scope.Complete();
}

  • Monitoring

    • Use Dynamic Management Views (DMVs) in SQL DB.
      • E.g. sys.dm_tran_active_transactions, sys.dm_tran_database_transactions, sys.dm_tran_locks.

Create, read, update, and delete data tables by using code

  • First ensure you can reach the database: Database -> Set server firewall -> Add client IP

  • You create/delete table, populate table or update/delete and select da ta with TSQL

  • Example using C# + ADO .NET:

      string query = "UPDATE [guitarBrands] SET type = @type, name = @name, image = @image WHERE id = @id";

  using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["brandsConnection"].ToString()))
  using (SqlCommand command = new SqlCommand(query, connection)
  {
      try
      {
          // open the connection, execute, etc
          List<SqlParameter> p = new List<SqlParameter>();
          p.Add(new SqlParameter("@type", newType.Text));
          p.Add(new SqlParameter("@name", newName.Text));
          p.Add(new SqlParameter("@image", newImage.Text));
          p.Add(new SqlParameter("@id", id));
          connection.Open();

          /* Submit query
          command.ExecuteNonQuery(); // Non query does not return results
          */

          /* Read
          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));
              }
          } */
      }
      catch
      {
          // log and handle exception(s)
      }
  }