What is Flexible Server in Azure Database for PostgreSQL?

by Contributed | Oct 3, 2020 | Azure, Technology, Uncategorized

This article is contributed. See the original author and article here.

At Ignite, we announced the preview of a new deployment option for Azure Database for PostgreSQL: Flexible Server. Flexible Server is the result of a multi-year Azure engineering effort to deliver a reimagined database service to those of you who run Postgres in the cloud. Over the past several years, our Postgres engineering team has had the opportunity to learn from many of you about your challenges and expectations around the Single Server deployment option in Azure Database for PostgreSQL. Your feedback and our learnings have informed the creation of Flexible Server.

If you are looking for a technical overview of what Flexible Server is in Azure Database for PostgreSQL—and what the key capabilities are, let’s dive in.

Flexible server is architected to meet requirements for modern apps

Our Flexible Server deployment option for Postgres is hosted on the same platform as Azure Database for PostgreSQL – Hyperscale (Citus), our deployment option that scales out Postgres horizontally (by leveraging the Citus open source extension to Postgres).

Flexible Server is hosted in a single-tenant Virtual Machine (VM) on Azure, on a Linux based operating system that aligns naturally with the Postgres engine architecture. Your Postgres applications and clients can connect directly to Flexible Server, eliminating the need for redirection through a gateway. The direct connection also eliminates the need for an @ sign in your username on Flexible Server. Additionally, you can now place Flexible Server’s compute and storage—as well as your application—in the same Azure Availability Zone, resulting in lower latency to run your workloads. For storage, our Flexible Server option for Postgres uses Azure Premium Managed Disk. In the future, we will provide an option to use Azure Ultra SSD Managed Disk. The database and WAL archive (WAL stands for write ahead log) are stored in zone redundant storage.

Flexible Server Architecture showing PostgreSQL engine hosted in a VM with zone redundant storage for data/log backups and client, database compute and storage in the same Availability Zone

There are numerous benefits of using a managed Postgres service, and many of you are already using Azure Database for PostgreSQL to simplify or eliminate operational complexities. With Flexible Server, we’re improving the developer experience even further, as well as providing options for scenarios where you want more control of your database.

A developer-friendly managed Postgres service

For many of you, your primary focus is your application (and your application’s customers.) If your application needs a database backend, the experience to provision and connect to the database should be intuitive and cost-effective. We have simplified your developer experience with Flexible Server on Azure Database for PostgreSQL, in few key ways.

• Intuitive and simplified provisioning experience. To provision Flexible Server, some of the fields are automatically filled based on your profile. For example, Admin username and password use defaults but you can always overwrite them.

• Simplified CLI experience. For example, it’s now possible to provision Flexible Server inside a virtual network in one command, and the number of keystrokes for the command can be reduced by using local context. For more details, see Flexible server CLI reference.

CLI command to provision the Flexible Server

• Connection string requirement. The requirement to include @servername suffix in the username has been removed. This allows you to connect to Flexible Server just like  you would to any other PostgreSQL engine running on-premise or on a virtual machine.

• Connection management: Pgbouncer is now natively integrated to simplify PostgreSQL connection pooling.

• Burstable compute: You can optimize cost with lower-cost, burstable compute SKUs that let you pay for performance only when you need it.

• Stop/start: Reduce costs with the ability to stop/start the Flexible Server when needed, to stop a running service or to start a stopped Service. This is ideal for development or test scenarios where it’s not necessary to run your database 24×7. When Flexible Server is stopped, you only pay for storage, and you can easily start it back up with just a click in the Azure portal.

Screenshot from the Azure Portal showing how to stop compute in your Azure Database for PostgreSQL flexible server when you don’t need it to be operational.

Screenshot from the Azure Portal depicting how to start compute for your Azure Database for PostgreSQL flexible server, when you’re ready to restart work.

Maximum database control

Flexible Server brings more flexibility and control to your managed Postgres database, with key capabilities to help you meet the needs of your application.

• Scheduled maintenance: Enterprise applications must be available all the time, and any interruptions during peak business hours can be disruptive. Similarly, if you’re a DBA who is running a long transaction—such as a large data load or index create/rebuild operations—any disruption will abort your transaction prematurely. Some of you have asked for the ability to control Azure maintenance windows to meet your business SLAs. Flexible Server will schedule one maintenance window every 30 days at the time of your choosing. For many customers, the system-managed schedule is fine, but the option to control is helpful for some mission-critical workloads.

Screenshot from the maintenance settings for Azure Database for PostgreSQL flexible server in the Azure Portal, showing where you can select the day of week and start time for your maintenance schedule.

• Configuration parameters: Postgres offers a wide range of server parameters to fine tune the database engine performance, and some of you want similar control in a managed service as well. For example, there is sometimes a need to mimic the configuration you had on-premises or in a VM. Flexible Server has enabled control over additional server parameters, such as Max_Connections, and we will add even more by Flexible Server GA.

• Lower Latency: To provide low latency for applications, some of you have asked for the ability to co-locate Azure Database for PostgreSQL and your application in physical proximity (i.e. the same Availability Zone). Flexible Server provides the ability to co-locate the client, database, and storage for lower latency and improved out-of-the-box performance. Based on our internal testing and customer testimonials, we are seeing much better out-of-the-box performance.

• Network Isolation: Some of you need the ability to provision servers with your own VNet or subnet, to ensure complete lock down from any outside access. With Flexible Server private endpoints, you can completely isolate the network by preventing any public endpoint to exist for the database workload. All connections to the server on public or private endpoints are secured and encrypted by default with SSL/TLS v1.2.

Zone-redundant high availability

With the new Flexible Server option for Azure Database for PostgreSQL, you can choose to turn on zone redundant high availability (HA). If you do, our managed Postgres service will spin up a hot standby with the exact same configuration, for both compute and storage, in a different Availability Zone. This allows you to achieve fast failover and application availability should the Availability Zone of the primary server become unavailable.

Any failure on the primary server is automatically detected, and it will fail over to the standby which becomes the new primary. Your application can connect to this new primary with no changes to the connection string.

Zone redundancy can help with business continuity during planned or unplanned downtime events, protecting your mission-critical databases. Given that the zone redundant configuration provides a full standby replica server, there are cost implications, and zone redundancy can be enabled or disabled at any time.

Screenshot from the Azure Portal depicting an Azure Database for PostgreSQL flexible server in a zone-redundant HA configuration, with the primary server in Availability Zone 1 and the standby server in Availability Zone 2.

Get started with Flexible Server today!

We can’t wait to see how you will use our new Flexible Server deployment option that is now in preview in Azure Database for PostgreSQL. If you’re ready to try things out, here are some quickstarts to get you started:

• Create an Azure Database for PostgreSQL Flexible Server using Azure portal
• Create an Azure Database for PostgreSQL Flexible Server using Azure CLI

• Create an Azure Database for PostgreSQL Flexible Server using ARM template

Azure Database for PostgreSQL Single Server remains the enterprise ready database platform of choice for your mission-critical workloads, until Flexible Server reaches GA. For those of you who want to migrate over to Flexible Server, we are also working to provide you a simplified migration experience from Single Server to Flexible Server with minimal downtime.

If you want to dive deeper, the new Flexible Server docs are a great place to roll up your sleeves, and visit our website to learn more about our Azure Database for PostgreSQL managed service. We are always eager to hear your feedback so please reach out via email using Ask Azure DB for PostgreSQL.

Sunil Agarwal

Twitter: @s_u_n_e_e_l

Interacting with Azure Stack Hub REST API

by Contributed | Oct 2, 2020 | Azure, Technology, Uncategorized

This article is contributed. See the original author and article here.

In this blog post, we have invited Shriram Natarajan (Senior Program Manager, Azure Stack Hub) to walk us through how to interact with Azure Stack Hub using REST APIs. 

Microsoft Azure cloud provides a robust platform for developers and operators to build and deploy their solutions seamlessly to the public cloud, the regional clouds as well as to Azure Stack Hub on-premises solutions. This ease of management is enabled by the consistent APIs, provided by the Cloud services available across all of these instances. Each cloud is fronted by the Azure Resource Manager (ARM) which simplifies the manage of application resources, enabling you to repeatedly deploy applications, with confidence that the resources are deployed in a consistent state. Azure Resource Manager makes it easy for you to manage and visualize resources in your app. You no longer have to deploy parts of your app separately and then manually stitch them together. You put resources with a common lifecycle into a resource group that can be deployed or deleted in a single action. You can see which resources are linked by a dependency. You can apply tags to resources to categorize them for management tasks, such as billing. On Azure Stack Hub, ARM is available for the cloud Operators as well to manage the cloud easily and ensure high availability to their customers.

Azure has robust tooling with SDKs in a variety of languages to help customers interact with the ARM APIs wherever they are. These tools are the recommended way to interact with any of the Azure clouds. However, there may be some cases where you would want to call the ARM APIs directly. For example:

1. You may need to call APIs that are not supported by tooling yet.
2. The tools standardize the parsing of the request responses to provide a good usability experience regardless of the service you’re calling. However, advanced users may require a different processing of this response based on their scenario.
3. The Azure SDKs are not available in the language of your choice and so you need to make direct REST calls to the ARM API.

For such scenarios, this blog will detail the process of making Authenticated API calls to the Azure Resource Manager. For the purposes of this blog we will use the ARM instance in Azure Stack Hub, but the process is the same regardless of whether you authenticate to Public Azure, any of the regional clouds or Azure Stack Edge.

This blog will help you get boots on ground quick and get started with calling ARM.

Note:

1. This post focuses on Azure Stack Operators and the Admin ARM in Azure Stack Hub, but know that it’s the same process for the User ARM as well.
2. This post will use Postman as a way to show the API calls. You can use the code generation part of the tool to generate the calls in a variety of languages.

Pre-Requisites:

1. Create a Service Principal in Azure and give it the right level of RBAC privileges on your Azure Stack Hub installation. Following this documentation will get you through it.

Note: this post will use Service Principal with a secret.

2. Download and install Postman. We’re going to assume some level of familiarity with Postman here to the extent of being able to modify values and being able to send requests.

Setting up the Requests and Environment Variables in Postman

Download the API requests and Environment Variables.

Import the API requests into postman using the import wizard.

Click on Upload files and select both the Azure Stack – Admin ARM REST.postman_collection.json and Azure Stack  – Admin ARM REST.postman_environment.json files. This will set up the necessary requests and the environment variables needed for you to follow along.

Make sure you have the API request collection imported on the left nav. To see the Environment variables, click on the dropdown on the top right.

Configure your Environment

Before you can start communicating with your Azure Stack Environment, you need to know a couple of things:

1. The URL of the ARM endpoint for that Azure Stack . As an Azure Stack Operator, you interact with he Admin ARM which is typically at https://adminmanagement.{region}.{fqdn}
2. The Azure Active Directory Tenant ID – the tenantID of the Directory that you’re going to log into using the Service Principal
3. The Service Principal credentials (App ID and Secret) that you’re going to use to authenticate to that ARM endpoint

You can enter these values in the Environment setup in Postman. Click on the Manage Environments icon and Select the Azure Stack – Admin ARM REST environment.

Replace the “Current Value” field for all the variables with the values from your environment.

The Theory

Before we start executing the postman requests, let’s quickly see an overview of the steps that need to be done:

1. Discover the endpoints for the Azure Stack Hub – We’ll need a few endpoints other than ARM to interact with the cloud (e.g. the authentication/token endpoint where you need to go to get a token, the audience to get the token for etc.). Fortunately, these are available to us through an unauthenticated API on ARM itself, so the first step is to call that API and get the values.
2. Get the AAD or AD FS Token endpoint by looking at the Open Id configuration – I’ll not get into details here about OpenID, Identity tokens and Access tokens etc. but know that the token endpoint is where you go and provide your credentials and get a token back after authentication. You will send this token to whichever application you are interacting with (ARM in this case) for it to authorize you and permit you to call it’s APIs
3. Authenticate with the identity system – As mentioned above, you will need to send a request to the Token endpoint of the identity system and present your credentials (SP ID and secret), and the target application (audience) you’re looking to interact with. If all the information you provided matches, the Identity System will now issue a token in response to this request.
4. Make an authenticated request to the necessary ARM API – You can now call the ARM APIs by passing the token as a Bearer token in the header of the request. ARM will receive the token and verify if you have access to the API you’re calling and will respond with the right value as appropriate!

Now you’re ready to call the ARM endpoint and have authenticated interactions with the Cloud!

Note: Make sure the right Environment is selected in Postman before you execute the requests below.

This is one of the common causes for failure in following this guide.

Execute Endpoint Discovery

This will send a request to {{adminArmEndpoint}}/metadata/endpoints?api-version=2015-11-01 with the adminArmEndpoint value being the one you set up in the Environment.

You will get the following response back from ARM.

Using the “Tests” functionality in POSTMAN, we’re creating additional environment variables using the values in the response. The most important ones are the ARM Audience and the loginEndpoint. These variables will be used for subsequent requests.

Execute AAD Token Endpoint Discovery

This step will send a call to the AAD or AD FS endpoint discovered in the previous step to fetch the actual token endpoint where we will need to send our credentials. The token endpoint can be read from the “Open ID connect Configuration” for that Identity system.

We will again use the Tests to save this into a new environment variable.

Authenticating with the Identity System and getting a token

Execute the Authenticate Service Principal request next and make sure that the service Principal ID and secret are set in the environment. This will send a request to the token endpoint discovered in the previous step along with the credentials and the ARM audience inferred from the endpoint discovery step.

Assuming your credentials are correct and you do have the permission to request a token, you will get the access token back in the response as seen above. Using Tests, we will store this token in a new variable inside the environment.

At this point you have everything you need to interact with ARM! The first thing you would need to know is the subscription ID – in this scenario, the operator would need to know the id of the Default Provider Subscription.

Execute Get Default Provider Subscription

This will send a request to the {{adminArmEndpoint}}/subscriptions?api-version=2015-01-06 endpoint. Note that the token is sent as a Bearer token in the Authorization Header of the request.

Note: the number of subscriptions returned in this request is dependent on the number of subscriptions the Service Principal has access to on the Admin ARM. In this case, the Service Principal has access only to the Default Provider Subscription, and so only one will be returned.

Execute Get Location

Following the same template for get subscription, we can also get the default location for that Azure Stack Stamp.

Execute Get namespaces within a subscription

Executing this will send a request to the /providers API which will return all the namespaces and the corresponding resource types and api-versions available under them.

With the information we’ve obtained from the above requests viz. Arm Endpoint, subscriptionID, location, namespaces, resource types and API versions that are available to the subscription, we can compose other requests to ARM. The Postman Request collection has samples of other such API calls you can compose with this information. Feel free to give them a whirl!

Resources

Here are some resources that do a great job in providing you an overview of ARM and how to interact with it.

• This article provides a great overview of ARM and its placement in the overall Azure Architecture.
• This is a good doc with details on authenticating and calling Azure Resource Manager on Azure Stack Hub. It provides a detailed description of how to interact with Azure Stack Hub
• As always, check out the Azure API reference.

 For learning more about operating an Azure Stack Hub, check the https://github.com/Azure-Samples/Azure-Stack-Hub-Foundation-Core which includes videos, slides, and workshops.

Memory errors during data extraction from SAP using Azure Data Factory SAP Table connector

by Contributed | Oct 2, 2020 | Azure, Technology, Uncategorized

This article is contributed. See the original author and article here.

Memory errors during data extraction from SAP using Azure Data Factory SAP Table connector

Azure Data Factory (ADF) is a fully managed data integration service ​for cloud-scale analytics in Azure​.  ADF provides more than 90 out of the box connectors to integrate with your source and target system. When we think about enterprise systems, SAP play a major role.

ADF has six different connectors to integrate with your SAP systems. Here are the connectors for ADF.

1. SAP BW Open Hub​
2. SAP BW MDX​
3. SAP HANA​
4. SAP Table​
5. SAP C4C​ (Cloud for Customer)
6. SAP ECC​

You can read more about these connectors, when to use which one, support scenarios and more details here: https://github.com/Azure/Azure-DataFactory/blob/master/whitepaper/SAP%20Data%20Integration%20using%20Azure%20Data%20Factory.pdf

In this bog, we focus on memory error during data extraction from SAP using Azure Data Factory SAP Table connector. The SAP table depends on three layers of implementation:

1. The SAP RFC Function module (/SAPDS/RFC_READ_TABLE2 or custom one)
2. The SAP Driver – SAP Nco
3. ADF SAP Table Runtime – Compile the partition queries/Trigger the SAP connection/calls/Wash the data/put the data into the downstream ADF runtime/sinks

In the recent customer engagement, we learned that while creating ADF Pipeline and transferring data, the following error occurs on a specific table.

 Operation on target Copy_z0z failed: ‘Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to invoke function /SAPDS/RFC_READ_TABLE2 with error: SAP.Middleware.Connector.RfcAbapRuntimeException, message: No more memory available to add rows to an internal table., stacktrack: at SAP.Middleware.Connector.RfcConnection.ThrowRfcErrorMsg() at SAP.Middleware.Connector.RfcConnection.ReadBytes(Byte* buffer, Int32 count) at SAP.Middleware.Connector.RfcConnection.ReadRfcIDBegin(Int32& length) at SAP.Middleware.Connector.RfcConnection.ReadUpTo(RFCGET readState, RfcFunction function, RFCID toRid) at SAP.Middleware.Connector.RfcConnection.RfcReceive(RfcFunction function) at SAP.Middleware.Connector.RfcFunction.RfcDeserialize(RfcConnection conn, IRfcIOStream stream) at SAP.Middleware.Connector.RfcFunction.RfcCallReceive(RfcConnection conn, IRfcIOStream stream, RFCID rid) at SAP.Middleware.Connector.RfcFunction.Invoke(RfcDestination destination) at Microsoft.DataTransfer.Runtime.SapRfcHelper.SapRfcClient.InvokeFunctionInternal(IRfcFunction function, Boolean initStatefulSession, Boolean checkForErrors) at Microsoft.Practices.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0() at Microsoft.Practices.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Microsoft.DataTransfer.Runtime.SapRfcHelper.SapRfcClient.InvokeFunction(IRfcFunction function, Boolean initStatefulSession, Boolean checkForErrors).,Source=Microsoft.DataTransfer.Runtime.SapRfcHelper,”Type=SAP.Middleware.Connector.RfcAbapRuntimeException,Message=No more memory available to add rows to an internal table.,Source=sapnco,’

This message also generated the short dump (ST22) in the SAP system. If you run a transaction ST22 in SAP application, you can see below error indicates SAP memory allocation to user is not enough.

This obviously means, you are extracting a way too much data that your SAP application with current memory configuration can handle. It does not mean, you server or VM running SAP is not capable, it just that you have restricted SAP application instance to restrict the memory.  Now, logically there are a few possible solutions:

1. Increase the memory parameters to high value in your application instance so it can handle more load.
   1. Use ABAP program RSMEMORY to adjust memory dynamically in the SAP application server. Temporary fix and reboot of app servers is not necessary.
   2. This can be done by adjusting the PHYS_MEMSIZE parameter to high value. You set this parameter in application instance profile, and it requires an SAP application instance where the parameter is configured, to be restarted (not a VM reboot)
2. Reduce the data extraction size.
   1. This may be possible when you are doing incremental or delta extract. However, if you are doing a full pull it may not be possible.
3. Reduce the data at source itself!
   1. This is sometimes possible by archiving or deleting the data from the source system itself. Be careful and ensure your decision of archiving/deleting before you do it.

Note: When you extract for the first time, you may have tons of data to load and need a lot of memory, and later with incremental load, you may reduce that memory footprint.

With the above recommendations, you did the first aid, and it may already solve your problem. However, you can further tune it for optimal performance.

Let’s take a look.

Sometimes, this can be a code issue, which can be an  SAP standard code, or a Customer Code (aka Z programs)?

This usually results from reading in a dataset into an Internal Table, and trying to process it. This works for smaller source data. But if you try to do this and read from a database table that  has grown beyond the size that a single work process is allowed to allocate, this error will result. The program needs to be able to handle the data in smaller chunks, Otherwise you will end up in a situation where you will progressively need to increase these parameters and will lead to poor performance over time. This is where you work with basis and dev team to run traces to look memory usage, runtime etc.  One of the useful tools to assess SAP ABAP program is ABAP Runtime Analysis (transaction SE30).

For performance issues and ABAP runtime terminations due to data size, tracing the load using ST12 is a good starting point. It will give you additional insights on ABAP code, reading type, Programs and function module involved.  Additionally, it is important to do a Table Analysis to identify which field can be used for range calculation, transaction DB05 can be used to analyze the distinct values of a table.

For huge SAP tables, the analysis should be started in a background task. The result is stored in the spool of background job TABLE_ANALYSIS_<table name>.

Recommendation

Here are some recommendations to consider while using SAP table connectors with ADF:

1. Use the latest SHIR: Changing protocol from RFC to BASXML in runtime (which should be part of recent runtime anyway) not only helped to handle the bigger size data, but performance was improved significantly.
2. Optimize the SAP application instance memory. Sometimes this could take several iterations to get the optimal memory value.
3. Review the program code. For SAP program causing performance issues, often you have SAP OSS note to fix it. For customer codes (aka Z programs), dev team can trace and optimize it. Avoid using “Select *” in your extraction.
4. Trigger extraction using a batch job. Often, dialog process has session expiration limit and cause process to fail.
5. When planning Data extraction or replication scenarios, review the following:
   1. Table size
   2. Change rate.
   3. Memory and CPU requirements on the source, target and middleware layer

The classification of tables according to their size (Small, Medium and Large) and change rate derives proper sizing of the of all components involved. 

6. If the volume is quite large and there is column that can be used for partitioning, it is recommended to leverage the partitioning setting of the SAP Table source which will bring three main benefits:
   1. It can alleviate the memory consumption on the SAP instance
   2. It can help alleviate the memory burden on the SHIR machine as well
   3. Besides, it will be best for your performance since we offer the capabilities to retrieve data in parallel. The Unit of each processing is the partition you defined using the partition settings. For example, there is a table with 10 million rows, if you can have some partition settings to split it into 100 partitions which means each partition will have around 100K rows.  With the parallelCopies set to 10(once partition Option is set with some NONE value, the default is 4. We are also enhancing the auto-Tuning logic to also increase based on the IR nodes number) then there will be 10 partitions processed at the same time. Suppose each thread has 3 M/s throughput, in ideal case, we can get 3 M/s * 10 = 30 M/s throughput. 
   4. Currently we support 4 types of partitions: Int, Year(yyyy), CalendarMonth(yyyyMM) and CalendarDate(yyyyMMdd)

Thank you Damien Rodriguez, Wenbo Fu, Roman Broich, Morgan Deegan, Hitoshi Ikemoto, and Chan Uk Jin for your contribution to this blog.