<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Rajendra Jagtap on Medium]]></title>
        <description><![CDATA[Stories by Rajendra Jagtap on Medium]]></description>
        <link>https://medium.com/@rajendra-jagtap?source=rss-140ac25c9323------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*yCQ1-V4RqxvZnHVC</url>
            <title>Stories by Rajendra Jagtap on Medium</title>
            <link>https://medium.com/@rajendra-jagtap?source=rss-140ac25c9323------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 15:57:37 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rajendra-jagtap/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Visualize AWS DynamoDB Data as a Dashboard without QuickSight]]></title>
            <link>https://medium.com/@rajendra-jagtap/visualize-dynamodb-data-as-a-dashboard-a440aa7514c3?source=rss-140ac25c9323------2</link>
            <guid isPermaLink="false">https://medium.com/p/a440aa7514c3</guid>
            <category><![CDATA[visualise-dynamodb]]></category>
            <category><![CDATA[dynamodbrealtimedashboard]]></category>
            <dc:creator><![CDATA[Rajendra Jagtap]]></dc:creator>
            <pubDate>Sun, 06 Oct 2024 18:03:35 GMT</pubDate>
            <atom:updated>2024-10-07T03:24:23.627Z</atom:updated>
            <content:encoded><![CDATA[<p>This article covers how to visualize real-time DynamoDB table data in a dashboard, presented in a tabular format.</p><p>In this process, a Lambda function scans the DynamoDB table, generates an HTML file, and uploads it to S3. The S3 bucket is configured for static website hosting to display the DynamoDB data as a dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/1*bb8am8EEp5fSv2Ay0t9k3Q.png" /></figure><ol><li>Create a Lambda IAM Role with Trusted entity type “AWS Service” and Use Case “Lambda”</li><li>Trust policy will be as below</li></ol><pre>{<br>    &quot;Version&quot;: &quot;2012-10-17&quot;,<br>    &quot;Statement&quot;: [<br>        {<br>            &quot;Effect&quot;: &quot;Allow&quot;,<br>            &quot;Action&quot;: [<br>                &quot;sts:AssumeRole&quot;<br>            ],<br>            &quot;Principal&quot;: {<br>                &quot;Service&quot;: [<br>                    &quot;lambda.amazonaws.com&quot;<br>                ]<br>            }<br>        }<br>    ]<br>}</pre><p>3. Add below inline policy to IAM Role</p><pre>{<br> &quot;Version&quot;: &quot;2012-10-17&quot;,<br> &quot;Statement&quot;: [<br>  {<br>   &quot;Sid&quot;: &quot;VisualEditor0&quot;,<br>   &quot;Effect&quot;: &quot;Allow&quot;,<br>   &quot;Action&quot;: [<br>    &quot;s3:PutObject&quot;,<br>    &quot;logs:CreateLogStream&quot;,<br>    &quot;dynamodb:Scan&quot;,<br>    &quot;logs:CreateLogGroup&quot;,<br>    &quot;logs:PutLogEvents&quot;<br>   ],<br>   &quot;Resource&quot;: [<br>    &quot;arn:aws:logs:&lt;region&gt;:&lt;account_id&gt;:log-group:/aws/lambda/&lt;lambda_function_name&gt;&quot;,<br>    &quot;arn:aws:dynamodb:&lt;region&gt;:&lt;account_id&gt;:table/&lt;table_name&gt;&quot;,<br>    &quot;arn:aws:s3:::&lt;s3_bucket_name&gt;/*&quot;<br>   ]<br>  }<br> ]<br>}</pre><p>4. Create a S3 bucket with public access</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B_VTPrQTVIJ0PWfpCulY7A.png" /></figure><p>5. Enable S3 Static Website hosting, define index document</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ze5X4mstGze16lDH4o9f_A.png" /></figure><p>6. Create DynamoDB Table with some sample data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MXkmD_qZh8WvVVaRW8tvVg.png" /></figure><p>7. You can add the items in DynamoDB table as below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*w_ukhQygatRSGExmprGAjA.png" /></figure><p>8. Create a Lambda Function with Runtime “Python 3.12”, attach IAM role created in step 1st and use below code. This Lambda function will scan the DynamoDB table, generate html file and store it in s3 bucket.</p><pre>import boto3<br>import os<br>import json<br><br># Initialize boto3 clients<br>s3 = boto3.client(&#39;s3&#39;)<br>dynamodb = boto3.client(&#39;dynamodb&#39;)<br><br># Environment variables for DynamoDB table and S3 bucket<br>DYNAMODB_TABLE = os.environ.get(&#39;DYNAMODB_TABLE&#39;)<br>S3_BUCKET = os.environ.get(&#39;S3_BUCKET&#39;)<br>S3_KEY = os.environ.get(&#39;S3_KEY&#39;, &#39;dynamodb_data.html&#39;)<br><br>def lambda_handler(event, context):<br>    try:<br>        # Check if DynamoDB table and S3 bucket are defined<br>        if not DYNAMODB_TABLE or not S3_BUCKET:<br>            raise ValueError(&quot;DYNAMODB_TABLE or S3_BUCKET environment variable is not defined.&quot;)<br>        <br>        # Scan the entire DynamoDB table<br>        response = dynamodb.scan(TableName=DYNAMODB_TABLE)<br>        items = response.get(&#39;Items&#39;, [])<br>        <br>        if not items:<br>            raise ValueError(&quot;No items found in the DynamoDB table.&quot;)<br>        <br>        # Convert the items to a more readable format<br>        formatted_items = [format_dynamodb_item(item) for item in items]<br>        <br>        # Generate the HTML content from the scanned items<br>        html_content = generate_html(formatted_items)<br>        <br>        # Upload the HTML content to S3<br>        s3.put_object(Bucket=S3_BUCKET, Key=S3_KEY, Body=html_content, ContentType=&#39;text/html&#39;)<br>        <br>        return {<br>            &#39;statusCode&#39;: 200,<br>            &#39;body&#39;: json.dumps(f&#39;Data successfully written to S3 at {S3_KEY}&#39;)<br>        }<br>    <br>    except Exception as e:<br>        print(f&quot;Error processing DynamoDB scan: {str(e)}&quot;)<br>        return {<br>            &#39;statusCode&#39;: 500,<br>            &#39;body&#39;: json.dumps(f&quot;Error processing event: {str(e)}&quot;)<br>        }<br><br>def format_dynamodb_item(item):<br>    &quot;&quot;&quot;Extracts all fields from a DynamoDB item, handling different data types.&quot;&quot;&quot;<br>    formatted_item = {}<br>    <br>    for key, value in item.items():<br>        if &#39;S&#39; in value:<br>            formatted_item[key] = value[&#39;S&#39;]  # String<br>        elif &#39;N&#39; in value:<br>            formatted_item[key] = value[&#39;N&#39;]  # Number<br>        elif &#39;M&#39; in value:<br>            formatted_item[key] = format_dynamodb_item(value[&#39;M&#39;])  # Map (nested object)<br>        elif &#39;L&#39; in value:<br>            formatted_item[key] = [format_dynamodb_item(v[&#39;M&#39;]) if &#39;M&#39; in v else v for v in value[&#39;L&#39;]]  # List (handle map inside list)<br>        elif &#39;BOOL&#39; in value:<br>            formatted_item[key] = value[&#39;BOOL&#39;]  # Boolean<br>        elif &#39;NULL&#39; in value:<br>            formatted_item[key] = None  # Null value<br>        else:<br>            formatted_item[key] = str(value)  # Handle other types as strings<br>    <br>    return formatted_item<br><br>def generate_html(items):<br>    &quot;&quot;&quot;Generate HTML table to display DynamoDB items.&quot;&quot;&quot;<br>    html = &#39;&#39;&#39;<br>    &lt;html&gt;<br>    &lt;head&gt;<br>        &lt;title&gt;DynamoDB Data&lt;/title&gt;<br>        &lt;style&gt;<br>            body { font-family: Arial, sans-serif; margin: 20px; }<br>            h1 { color: #333; }<br>            table { border-collapse: collapse; width: 100%; }<br>            th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }<br>            th { background-color: #f2f2f2; }<br>            tr:hover { background-color: #f5f5f5; }<br>        &lt;/style&gt;<br>    &lt;/head&gt;<br>    &lt;body&gt;<br>        &lt;h1&gt;DynamoDB Dashboard&lt;/h1&gt;<br>        &lt;table&gt;<br>    &#39;&#39;&#39;<br>    <br>    # Generate table headers<br>    all_keys = set()<br>    for item in items:<br>        all_keys.update(item.keys())<br>    <br>    # Sort keys, with &#39;env&#39; first, and others in alphabetical order<br>    sorted_keys = [&#39;env&#39;] + sorted([key for key in all_keys if key != &#39;env&#39;])<br>    <br>    # Generate table headers<br>    html += &#39;&lt;tr&gt;&#39; + &#39;&#39;.join([f&#39;&lt;th&gt;{header}&lt;/th&gt;&#39; for header in sorted_keys]) + &#39;&lt;/tr&gt;&#39;<br>    <br>    # Generate table rows<br>    for item in items:<br>        html += &#39;&lt;tr&gt;&#39; + &#39;&#39;.join([f&#39;&lt;td&gt;{item.get(header, &quot;&quot;)}&lt;/td&gt;&#39; for header in sorted_keys]) + &#39;&lt;/tr&gt;&#39;<br>    <br>    # Close the HTML<br>    html += &#39;&#39;&#39;<br>        &lt;/table&gt;<br>    &lt;/body&gt;<br>    &lt;/html&gt;<br>    &#39;&#39;&#39;<br>    <br>    return html</pre><p>9. Set below env variables in lambda function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2ejI0zCq0pjBuGAT0ENEjw.png" /></figure><p>10. Run the lambda function and see the s3 static webhost url in browser. It should show the dynamoDB data</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cwu2GnCUbItLDV-h52Eddw.png" /></figure><p>11. To fetch the realtime data from dynamoDB table, enable DynamoDB Stream with “New Image” view type. This will capture item-level changes in your table, and push the changes to a DynamoDB stream.</p><p>12. Add DynamoDB Stream as trigger for lambda function so whenever there will be any change in DynamoDB table, it will trigger the lambda function</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VAVI433GOGbrpG4DmF0GZg.png" /></figure><p>13. Lets try to add new item in DynamoDB table as below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YL8g-D1W_d6EiTLbi5BoFA.png" /></figure><p>14. This change will trigger the lambda function and lambda function will scan the table, generate html file and store it in s3 bucket.</p><p>15. Verify the new item details in browser</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RdI_OYOIB4AT6jd-OBDHWA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a440aa7514c3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Crossplane on AWS EKS]]></title>
            <link>https://medium.com/@rajendra-jagtap/crossplane-on-aws-eks-af3dc8b2d9a2?source=rss-140ac25c9323------2</link>
            <guid isPermaLink="false">https://medium.com/p/af3dc8b2d9a2</guid>
            <category><![CDATA[ekscrossplane]]></category>
            <category><![CDATA[crossplane]]></category>
            <category><![CDATA[crossplaneaws]]></category>
            <dc:creator><![CDATA[Rajendra Jagtap]]></dc:creator>
            <pubDate>Sun, 25 Feb 2024 09:52:17 GMT</pubDate>
            <atom:updated>2024-02-25T09:52:17.042Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>Crossplane is an open-source Kubernetes add-on that extends Kubernetes to enable you to manage your infrastructure, services, and applications directly from Kubernetes clusters. It turns Kubernetes into a universal control plane by allowing you to provision, manage, and compose cloud resources and services alongside your applications, using Kubernetes-style declarative configuration. Crossplane adheres to the Kubernetes philosophy of declarative management and automation, aiming to leverage the power of Kubernetes to manage external resources with the same ease as managing pods or deployments within a cluster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/1*e2bmSj8KpvHIZGHthEuNsg.png" /></figure><h3>Why We Need Crossplane</h3><ul><li>Unified API for Infrastructure Management: Crossplane provides a consistent API to manage different cloud services and resources, enabling developers to provision and manage infrastructure using kubectl or any Kubernetes tools. This reduces context switching and the need to learn multiple cloud provider APIs.</li><li>Infrastructure as Code (IaC): With Crossplane, you can define your infrastructure using Kubernetes manifests. This approach to IaC enables version control, peer review, and repeatable deployments across environments.</li><li>Workload Portability: Crossplane enables workload portability across different clouds and environments by abstracting the underlying infrastructure details. This makes it easier to migrate applications or run them in multi-cloud and hybrid-cloud environments.</li><li>Declarative Configuration: Leveraging Kubernetes’ declarative configuration model, Crossplane allows you to specify the desired state of your infrastructure and services, with Kubernetes ensuring that the actual state matches the desired state.</li><li>Self-Service Infrastructure: Crossplane can be configured to offer self-service infrastructure provisioning within the bounds of policies and best practices defined by administrators. This empowers developers to provision the resources they need without waiting for operations teams, while still adhering to organizational guidelines.</li></ul><h3>Crossplane Setup on AWS EKS</h3><h4>Prerequisites</h4><ol><li>Install Git: <a href="https://github.com/git-guides/install-git">Git Installation Guide</a></li><li>Install Terraform: <a href="https://developer.hashicorp.com/terraform/tutorials/aws-get-started/install-cli">Terraform Installation Guide</a></li><li>Install eksctl: <a href="https://docs.aws.amazon.com/emr/latest/EMR-on-EKS-DevelopmentGuide/setting-up-eksctl.html">eksctl Installation Guide</a></li><li>Install kubectl: <a href="https://kubernetes.io/docs/tasks/tools/install-kubectl-linux/">kubectl Installation Guide</a></li><li>Install Helm: <a href="https://helm.sh/docs/intro/install/">Helm Installation Guide</a></li><li>Install Crossplane CLI: <a href="https://docs.crossplane.io/latest/cli/">Crossplane CLI Installation Guide</a></li></ol><h4><strong>Steps</strong></h4><p>1. <strong>Provision the EKS Cluster</strong></p><p>Clone the repository and apply the Terraform configuration to provision the EKS cluster.</p><pre>git clone https://github.com/rajendra-jagtap/aws-eks-crossplane.git<br>cd aws-eks-crossplane<br>terraform init<br>terraform plan<br>terraform apply</pre><p>2. <strong>Enabling IAM principal access to your cluster</strong></p><p>Create an IAM identity mapping for cluster access</p><pre>eksctl create iamidentitymapping --cluster test-eks-cluster --arn &lt;iam-user-arn&gt; --username &lt;username&gt; --group system:masters</pre><p>3. <strong>Update the kubeconfig</strong></p><p>Update the kubeconfig.</p><pre>aws eks update-kubeconfig --region &lt;region&gt; --name &lt;cluster-name&gt;</pre><p>4. <strong>Verify Cluster Access</strong></p><p>Verify access to the Kubernetes cluster.</p><pre>kubectl get svc</pre><p>5. <strong>Install Crossplane Using Helm</strong></p><p>Add the Crossplane Helm repository and install Crossplane.</p><pre>helm repo add crossplane-stable https://charts.crossplane.io/stable<br>helm repo update<br>helm install crossplane crossplane-stable/crossplane --namespace crossplane-system --create-namespace</pre><p>6. <strong>Verify Crossplane Installation</strong></p><p>Check the Crossplane pods and API resources</p><pre>kubectl get pods -n crossplane-system<br>kubectl api-resources | grep crossplane</pre><p>7. <strong>Install AWS Provider for Crossplane</strong></p><p>Apply the AWS provider configuration.</p><pre>cat &lt;&lt;EOF | kubectl apply -f -<br>apiVersion: pkg.crossplane.io/v1<br>kind: Provider<br>metadata:<br>   name: provider-aws-s3<br>spec:<br>   package: xpkg.upbound.io/upbound/provider-aws-s3:v1.1.0<br>EOF</pre><p>8. <strong>Verify Provider Installation</strong></p><p>List the installed providers..</p><pre>kubectl get providers</pre><p>9. <strong>Create a Kubernetes secret for AWS</strong></p><p><em>Note: The provider requires credentials to create and manage AWS resources.</em></p><p>Create a text file containing the AWS account aws_access_key_id and aws_secret_access_key. Save this text file as aws-credentials.txt.</p><pre>aws_access_key_id = YOUR_ACCESS_KEY_ID<br>aws_secret_access_key = YOUR_SECRET_ACCESS_KEY</pre><p>Create the secret in the crossplane-system namespace.</p><pre>kubectl create secret generic aws-secret -n crossplane-system --from-file=creds=./aws-credentials.txt</pre><p>10. <strong>Create a ProviderConfig</strong></p><p>Apply the ProviderConfig to use the secret for AWS operations.</p><pre>cat &lt;&lt;EOF | kubectl apply -f -<br>   apiVersion: aws.upbound.io/v1beta1<br>   kind: ProviderConfig<br>   metadata:<br>     name: default<br>   spec:<br>     credentials:<br>       source: Secret<br>       secretRef:<br>         namespace: crossplane-system<br>         name: aws-secret<br>         key: creds<br>   EOF</pre><p>11. <strong>Create a managed resource in AWS</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/751/1*PxoNAc7yIhfybDtLJqK-RA.png" /></figure><p>Example: Create an S3 bucket.</p><pre>cat &lt;&lt;EOF | kubectl create -f -<br>   apiVersion: s3.aws.upbound.io/v1beta1<br>   kind: Bucket<br>   metadata:<br>     generateName: crossplane-bucket-<br>   spec:<br>     forProvider:<br>       region: ap-south-1<br>     providerConfigRef:<br>       name: default<br>   EOF</pre><p>12. <strong>Get buckets created using Crossplane</strong></p><p>List the buckets managed by Crossplane.</p><pre>kubectl get buckets</pre><p>13. <strong>Delete managed resource from AWS</strong></p><p>Delete an S3 bucket managed by Crossplane.</p><pre>kubectl delete bucket &lt;bucket-name&gt;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=af3dc8b2d9a2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Migrate RDS SQL Server database to Custom RDS SQL Server]]></title>
            <link>https://medium.com/@rajendra-jagtap/migrate-rds-sql-server-database-to-custom-rds-sql-server-d362dbf4a71c?source=rss-140ac25c9323------2</link>
            <guid isPermaLink="false">https://medium.com/p/d362dbf4a71c</guid>
            <category><![CDATA[sql-backup-restore]]></category>
            <category><![CDATA[aws-custom-rds]]></category>
            <category><![CDATA[migrate-rds-to-custom-rds]]></category>
            <category><![CDATA[custom-rds-sql-server]]></category>
            <category><![CDATA[migrate]]></category>
            <dc:creator><![CDATA[Rajendra Jagtap]]></dc:creator>
            <pubDate>Sun, 22 May 2022 17:15:25 GMT</pubDate>
            <atom:updated>2022-05-22T17:15:25.988Z</atom:updated>
            <content:encoded><![CDATA[<h4><strong>Prerequisite Steps:</strong></h4><ol><li>Create symmetric KMS key for Custom RDS</li></ol><pre><em>aws kms create-key</em></pre><p>2. Create IAM Role for Custom RDS SQL Server</p><pre><em>aws iam create-role \<br> — role-name AWSRDSCustomSQLServerInstanceRole \<br> — assume-role-policy-document ‘{<br> “Version”: “2012–10–17”,<br> “Statement”: [<br> {<br> “Action”: “sts:AssumeRole”,<br> “Effect”: “Allow”,<br> “Principal”: {<br> “Service”: “ec2.amazonaws.com”<br> }<br> }<br> ]<br> }’</em></pre><p>3. Add below policy to above role. <br> Update region, account_id and KMS key ARN in below policy</p><pre><em>aws iam put-role-policy \<br>    --role-name AWSRDSCustomSQLServerInstanceRole \<br>    --policy-name AWSRDSCustomSQLServerIamRolePolicy \<br>    --policy-document &#39;{<br>        &quot;Version&quot;: &quot;2012-10-17&quot;,<br>        &quot;Statement&quot;: [<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent1&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ssm:GetDeployablePatchSnapshotForInstance&quot;,<br>                    &quot;ssm:ListAssociations&quot;,<br>                    &quot;ssm:PutInventory&quot;,<br>                    &quot;ssm:PutConfigurePackageResult&quot;,<br>                    &quot;ssm:UpdateInstanceInformation&quot;,<br>                    &quot;ssm:GetManifest&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent2&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ssm:ListInstanceAssociations&quot;,<br>                    &quot;ssm:PutComplianceItems&quot;,<br>                    &quot;ssm:UpdateAssociationStatus&quot;,<br>                    &quot;ssm:DescribeAssociation&quot;,<br>                    &quot;ssm:UpdateInstanceAssociationStatus&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;arn:aws:ec2:&lt;region&gt;:&lt;accound_id&gt;:instance/*&quot;,<br>                &quot;Condition&quot;: {<br>                    &quot;StringLike&quot;: {<br>                        &quot;aws:ResourceTag/AWSRDSCustom&quot;: &quot;custom-sqlserver&quot;<br>                    }<br>                }<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent3&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ssm:UpdateAssociationStatus&quot;,<br>                    &quot;ssm:DescribeAssociation&quot;,<br>                    &quot;ssm:GetDocument&quot;,<br>                    &quot;ssm:DescribeDocument&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;arn:aws:ssm:*:*:document/*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent4&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ssmmessages:CreateControlChannel&quot;,<br>                    &quot;ssmmessages:CreateDataChannel&quot;,<br>                    &quot;ssmmessages:OpenControlChannel&quot;,<br>                    &quot;ssmmessages:OpenDataChannel&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent5&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ec2messages:AcknowledgeMessage&quot;,<br>                    &quot;ec2messages:DeleteMessage&quot;,<br>                    &quot;ec2messages:FailMessage&quot;,<br>                    &quot;ec2messages:GetEndpoint&quot;,<br>                    &quot;ec2messages:GetMessages&quot;,<br>                    &quot;ec2messages:SendReply&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent6&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ssm:GetParameters&quot;,<br>                    &quot;ssm:GetParameter&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;arn:aws:ssm:*:*:parameter/*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;ssmAgent7&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;ssm:UpdateInstanceAssociationStatus&quot;,<br>                    &quot;ssm:DescribeAssociation&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;arn:aws:ssm:*:*:association/*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;eccSnapshot1&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: &quot;ec2:CreateSnapshot&quot;,<br>                &quot;Resource&quot;: [<br>                    &quot;arn:aws:ec2:&lt;region&gt;:&lt;accound_id&gt;:volume/*&quot;<br>                ],<br>                &quot;Condition&quot;: {<br>                    &quot;StringLike&quot;: {<br>                        &quot;aws:ResourceTag/AWSRDSCustom&quot;: &quot;custom-sqlserver&quot;<br>                    }<br>                }<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;eccSnapshot2&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: &quot;ec2:CreateSnapshot&quot;,<br>                &quot;Resource&quot;: [<br>                    &quot;arn:aws:ec2:&lt;region&gt;::snapshot/*&quot;<br>                ],<br>                &quot;Condition&quot;: {<br>                    &quot;StringLike&quot;: {<br>                        &quot;aws:RequestTag/AWSRDSCustom&quot;: &quot;custom-sqlserver&quot;<br>                    }<br>                }<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;eccCreateTag&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: &quot;ec2:CreateTags&quot;,<br>                &quot;Resource&quot;: &quot;*&quot;,<br>                &quot;Condition&quot;: {<br>                    &quot;StringLike&quot;: {<br>                        &quot;aws:RequestTag/AWSRDSCustom&quot;: &quot;custom-sqlserver&quot;,<br>                        &quot;ec2:CreateAction&quot;: [<br>                            &quot;CreateSnapshot&quot;<br>                        ]<br>                    }<br>                }<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;s3BucketAccess&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;s3:putObject&quot;,<br>                    &quot;s3:getObject&quot;,<br>                    &quot;s3:getObjectVersion&quot;,<br>                    &quot;s3:AbortMultipartUpload&quot;<br>                ],<br>                &quot;Resource&quot;: [<br>                    &quot;arn:aws:s3:::do-not-delete-rds-custom-*/*&quot;<br>                ]<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;customerKMSEncryption&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;kms:Decrypt&quot;,<br>                    &quot;kms:GenerateDataKey*&quot;<br>                ],<br>                &quot;Resource&quot;: [<br>                    &quot;arn:aws:kms:&lt;region&gt;:&lt;accound_id&gt;:key/xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx&quot;<br>                ]<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;readSecretsFromCP&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;secretsmanager:GetSecretValue&quot;,<br>                    &quot;secretsmanager:DescribeSecret&quot;<br>                ],<br>                &quot;Resource&quot;: [<br>                    &quot;arn:aws:secretsmanager:&lt;region&gt;:&lt;accound_id&gt;:secret:do-not-delete-rds-custom-*&quot;<br>                ],<br>                &quot;Condition&quot;: {<br>                    &quot;StringLike&quot;: {<br>                        &quot;aws:ResourceTag/AWSRDSCustom&quot;: &quot;custom-sqlserver&quot;<br>                    }<br>                }<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;publishCWMetrics&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: &quot;cloudwatch:PutMetricData&quot;,<br>                &quot;Resource&quot;: &quot;*&quot;,<br>                &quot;Condition&quot;: {<br>                    &quot;StringEquals&quot;: {<br>                        &quot;cloudwatch:namespace&quot;: &quot;rdscustom/rds-custom-sqlserver-agent&quot;<br>                    }<br>                }<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;putEventsToEventBus&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: &quot;events:PutEvents&quot;,<br>                &quot;Resource&quot;: &quot;arn:aws:events:&lt;region&gt;:&lt;accound_id&gt;:event-bus/default&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;cwlOperations1&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: [<br>                    &quot;logs:PutRetentionPolicy&quot;,<br>                    &quot;logs:PutLogEvents&quot;,<br>                    &quot;logs:DescribeLogStreams&quot;,<br>                    &quot;logs:CreateLogStream&quot;,<br>                    &quot;logs:CreateLogGroup&quot;<br>                ],<br>                &quot;Resource&quot;: &quot;arn:aws:logs:&lt;region&gt;:&lt;accound_id&gt;:log-group:rds-custom-instance-*&quot;<br>            },<br>            {<br>                &quot;Sid&quot;: &quot;cwlOperations2&quot;,<br>                &quot;Effect&quot;: &quot;Allow&quot;,<br>                &quot;Action&quot;: &quot;logs:DescribeLogGroups&quot;,<br>                &quot;Resource&quot;: &quot;arn:aws:logs:&lt;region&gt;:&lt;accound_id&gt;:log-group:*&quot;<br>            }<br>        ]<br>    }&#39;</em></pre><p>4. Create Instance profile and add role to instance profile</p><pre><em>aws iam create-instance-profile \<br>    --instance-profile-name AWSRDSCustomSQLServerInstanceProfile<br><br>aws iam add-role-to-instance-profile \<br>    --instance-profile-name AWSRDSCustomSQLServerInstanceProfile \<br>    --role-name AWSRDSCustomSQLServerInstanceRole</em></pre><h4><strong>Create Custom RDS for SQL Server:</strong></h4><ol><li>Create new RDS database with Engine<strong> Microsoft SQL Server</strong> and management type with <strong>Amazon RDS Custom</strong></li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/703/0*-iJadxP0hB1uNFG5" /></figure><p>2. Provide all the other details as we mention for normal RDS like DB user, password, instance type, storage, security group, etc and also select KMS key, IAM Profile which we have created in perquisite steps. Refer <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-creating-sqlserver.html">Create custom SQL RDS</a></p><p>3. The new custom RDS will be available in a few minutes. It will also create EC2 instance which is associated with Custom RDS.</p><p>4. Take native backup of existing RDS SQL Server instance. To take native backup of RDS SQL Server instance, use below query</p><pre><em>exec msdb.dbo.rds_backup_database <br></em><a href="http://twitter.com/source_db_name"><em>@source_db_name</em></a><em>=&#39;&lt;test_db&gt;&#39;, </em><a href="http://twitter.com/s3_arn_to_backup_to"><em>@s3_arn_to_backup_to</em></a><em>=&#39;arn:aws:s3:::&lt;s3-bucket-name&gt;/rds-native-backup/&lt;test_db&gt;.bak&#39;, <br></em><a href="http://twitter.com/overwrite_S3_backup_"><em>@overwrite_S3_backup_</em></a><em>file=1;</em></pre><p>To take native backup of existing server, we need to make few changes in option group. Please refer <a href="https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/">RDS SQL Server native backup</a></p><p>5. Follow below steps and Login EC2 instance which is associated with Custom RDS</p><p>a) Allow your IP in security group for RDP connection (port 3389)</p><p>b) Set the firewall rule on host. Start ssm session by executing below command</p><pre>aws ssm start-session --target &quot;&lt;instance-id&gt;&quot;</pre><p>Once the session is started, execute below command</p><pre>Set-NetFirewallRule -DisplayName &quot;Remote Desktop - User Mode (TCP-In)&quot; -Direction Inbound -LocalAddress Any -Profile Any</pre><p>c)<strong> </strong>Retrieve the secret key<br> In the details section of EC2 server, find key pair name and then check the secret in Secret Manager service with same key pair name. Retrieve the secret value. This server will be a Windows server. So you need to copy a pem file from secret and then generate a password from that pem file.</p><p>6. Once you logged into the server, copy the native backup files from s3 to this server. Open <strong>Microsoft SQL Server Management Studio</strong> and start to restore DB’s with below query. For more reference<strong> </strong><a href="https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/">RDS SQL Server native backup restore</a></p><pre><em>exec msdb.dbo.rds_restore_database <br></em><a href="http://twitter.com/restore_db_name"><em>@restore_db_name</em></a><em>=&#39;test_db&#39;, <br></em><a href="http://twitter.com/s3_arn_to_restore_fr"><em>@s3_arn_to_restore_fr</em></a><em>om=&#39;C:\&lt;path of db backup file&gt;/test_db.bak&#39;;</em></pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d362dbf4a71c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>