Questions tagged with Microsoft SQL Server

How to migrate DB on premise Oracle to Microsoft SQL Server DB in AWS. We see SCT will not allow MS SQL Server as Target when source is Oracle. Looking for tools like SCT to move Schema from Oracle. We see SCT is useful to move Source Schema (Eg: Oracle ) to Target Schema DB (Eg: Aurora Postgres) and other limited DB combinations. Now requirement is to migrate from On-premise Oracle to MS SQL Server in AWS Cloud. Please let me know if anyone has worked on this task.

Can I bring my own (BYOL) Microsoft SQL Server licenses for SAP on AWS? *NOTE:* This is a common question asked by AWS customers. The AWS team is posting it to provide an answer that can benefit everyone.

When choosing to use SQL Server Web Edition on RDS what are the licencing considerations? Having tried to decipher both Microsoft's and AWS Licence information it's still not clear. We're looking to transition our self hosted SQL Server instance to RDS, the Database Server is used for a multi-tenanted SaaS offering.

I hope we're asking this in the right area. We are very new to AWS. We have an EC2 server with WHM running two websites. One using MySQL. The other is in development and the developer is suggesting we use MongaDB. We have developer saying we cannot run both on the same server, the other developer saying we can. We looking on feedback and what might be the best server structure to use. Ideally we'd like to run both from one server. How would we best achieve this, if at all? Thanks.

Microsoft announced they will end support for SQL Server 2012 on July 12, 2022. On that date, Microsoft will stop critical patch updates for SQL Server 2012. We strongly recommend that you upgrade your RDS for SQL Server 2012 database instance to a different major version at your earliest convenience \[1]. Starting September 1, 2021, we will begin disabling the creation of new Amazon RDS for SQL Server database instances using Microsoft SQL Server 2012. Starting June 1, 2022, we plan to end support of Microsoft SQL Server 2012 on Amazon RDS SQL Server. At that time, any remaining instances will be scheduled to migrate to SQL Server 2014 (latest minor version available) as described below. We recommend that you upgrade your Microsoft SQL Server 2012 instances to Microsoft SQL Server 2014 or later at a time convenient to you. You can schedule an upgrade to a different major version by going to the instance modify page in the AWS Management Console and changing the database version to a desired value. If you choose the "Apply Immediately" option, the upgrade will be initiated immediately after exiting the modify page. If you choose not to apply the change immediately, the upgrade will be scheduled during your maintenance window. Upgrade Options: We support five (or four on some regions) different major/minor version combinations of SQL Server 2012. These database instances can be upgraded to the latest minor version of SQL Server 2014, 2016, 2017, and 2019 directly. To find out more information on upgrading please reference this document \[2]. You will still be able to restore a SQL Server 2012 database to any major version supported instance on Amazon RDS SQL Server, even after the deprecation. For more information on restoring a database in RDS see here \[3]. Should you have any questions or concerns, the AWS Support Team is available via AWS Premium Support \[4]. \[1] https://docs.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2012 \[2] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.SQLServer.html \[3] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html \[4] https://aws.amazon.com/support

Hello All, My insstances have dissapeard for my SQL DB. Yesterday I could not connect to them and today they are gone? What do I do? Thanks Peter Hoffman

Several times a day, we are getting this error message in our logs when our front-end webserver (i-08a436fb2d0811658) attempts to connect to our database server (i-0ddd3807ec3bac491): **The network path was not found**: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The configuration of these EC2 instances has not changed in years, and this issue just randomly started a few days ago. We tried stopping the web server and starting it again, to hopefully move it to new hardware, but the issue persists. There are no warnings or errors in the event logs on the web server or the database server indicating what might be the cause. TCP is the primary protocol to communicate with SQL Server. The reference to "named pipes" above indicates it was used a the secondary protocol after the TCP connection failed. Can someone please assist us in troubleshooting this issue. Thank you.

Where can I find the RDS SQL Server support 2012 End of support information. Also I would like to know where we can find all the RDS announcments on re:post. Thank you!

Hi team, i have configured the AWSSQLSERVER-DBCC automation on EC2 instance for SQL server 2019 and in automation 3rd step completed i.e. DBCC command completed but last step keep running and looks that command(InstallPowerShellOnLinux) will run on linux machine only but my machine is Windows 2019 OS. can some one helps me for understanding about this step. Error: A positional parameter cannot be found that accepts argument \u0027$null\u0027

Hi, I have a customer looking for a list of "system procedures for rds for sql". I could locate the one for mysql but not for sql. Can someone point me to a list.

I am trying to replicate a functionality from SQL Server into redshift where I have to ignore column if the column exists, otherwise add it into the table. I have come across these posts in stackoverflow, however couldn't find a proper solution from them: 1) https://stackoverflow.com/questions/65103448/redshift-alter-table-if-not-exists 2) https://stackoverflow.com/questions/42035068/redshift-add-column-if-not-exists 3) https://stackoverflow.com/questions/42669237/workaround-in-redshift-for-add-column-if-not-exists I am able to get a TRUE or FALSE for columns that I want to check. But I don't know how to ALTER the table to add or remove one. I am also a bit new to redshift, so I would appreciate a response! These are some of my attempts: ````` IF (SELECT EXISTS(SELECT * FROM pg_catalog.pg_table_def WHERE schemaname = 'my_schema' AND tablename = 'my_table' AND "column" = 'my_new_column' )) <> TRUE THEN ALTER TABLE my_table ADD COLUMN my_new_column varchar END IF; ````` ``````` CREATE OR REPLACE PROCEDURE if_else() LANGUAGE plpgsql AS $$ BEGIN IF (SELECT EXISTS(SELECT * FROM pg_catalog.pg_table_def WHERE schemaname = 'my_schema' AND tablename = 'my_table' AND "column" = 'my_new_column' )) <> TRUE THEN ALTER TABLE my_table ADD COLUMN my_new_column varchar END IF; END; $$ ; CALL if_else(); ``````` A few more failed attempts: ``````` CREATE OR REPLACE PROCEDURE alter_my_table() AS $$ BEGIN ALTER TABLE my_table ADD COLUMN my_new_column varchar END; $$ LANGUAGE plpgsql ; SELECT CASE WHEN COUNT(*) THEN 'warning: column exists already.' ELSE CALL alter_my_table(); END FROM pg_catalog.pg_table_def WHERE schemaname = 'my_schema' AND tablename = 'my_table' AND "column" = 'my_new_column' ``````` Thank you for your time.

I'm new to this cloud services and recently I'm trying to deploy my PHP project using Microsoft SQL Server on elastic beanstalk. When I run it, it says : **This extension requires the Microsoft ODBC Driver for SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x64: https://go.microsoft.com/fwlink/?LinkId=163712** I already tried to install ODBC with config that I put at `.ebextensions`. I've been searching and trying any command to install the ODBC, but still not working. Is there anyway to install ODBC for SQL Server on elastic beanstalk?

Hello, I'm looking for a recommendation for a Source control solution for a team of 5-10 developers that works well with RDS MS SQL Server. Thanks.

Being someone requiring additional learning curve with AWS RDS and have been trying learning from available AWS RDS documentation, I would appreciate any technical direction to clear some of my queries in regard to understand dynamics with AWS RDS Storage. The project I am working with uses SQL Server and has quite a limited users with less amount of data at the moment, but I have been observing my RDS storage gradually being consumed over time span of two weeks and I am not able to figure out what actually is consuming this storage as the DB seems to have very less volume of data(92 MBs as attached in screenshot below). [Database&Logs Size](https://imgur.com/9aBP8rc) Can anyone have a look at curve and guide which areas I might be missing looking into insights? Is it only DB or any other factor contributing reflecting the overall storage? [Free Storage Graph](https://imgur.com/IthpNAg) Thanks in advance

Hello, is the traffic between the transit gateway to the AWS encryption encrypted? I've been requested to open an unencrypted SQL port (1433) from on-prem to AWS. Traffic will be going through VPN which is encrypted from on-prem to the AWS transit gateway. My concern is the traffic from the transit gateway to the AWS instance. Thanks in advance.

When I connect to my RDS SQL Server instance through SSMS, queries execute nicely from a query window. But any operation that uses SSMS features to retrieve metadata takes very long, several minutes! For example, activating the drop-down list of databases on the instance, or retrieving the properties of a table, or even retrieving a list of tables on a database. Can someone explain why this happens? Thanks!

Hello: --- We have 18 DMS replicate tasks with origin endpoints from SQL server databases on premises to target ec2 SQL server endpoints. Same database versions. All tasks are replicating with this red status: "Running with errors". we have 2 x r5.2xlarge replication instances. cloudwatch logs are reporting always the same error. [DATA_STRUCTURE]E:SQLite general error. Code <19>, Message<UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage> What is causing this error? We dont know if the replication is going well

I have a database on my company's AWS server. We have hundreds but I am only concerned with two. Both are hosted on AWS. One is a MS SQL server and the other a MySQL. I need to periodically move an entire database from MSSQL *to* MySQL. They are large databases so MySQL workbench and SSIS often fail due to size. ~1-2 million records. Less than 10GB data. I have seen mentions of an Amazon service called DMS. Is there a service on Amazon that can do this? I do *not* need a permanent connection or sync between the two. Just 3-4 times a month to move the database. I do not want to mention this to my Workgroup until I know it can be done. Is Data Migration Service the tool I need? Are there any other AWS solutions ?

Hello, can I use my own SMTP server to send emails? I configured DB mail and created profile and account per https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.DBMail.html, but using my SMTP server (Amazon Linux instance, same VPC as RDS instance), but when I try sending an email I get 'profile name is not valid'. So am I forced to use Amazon Simple Email Service only? Thanks

I have been trying to find an answer in documentation, github, here and the old forums, but I fail to find the answer to my question. In my CDK v1 (python) I create a RDS instance of SQL Server and set credentials with aws_rds.Cerednetials.from_generated_secret(), but when I later on want to provide environment/sercrets values to the docker container I want to run in fargate I have the following environment variable that I need to be set: DB_CONNECTION_STRING, which has the following syntax: Server=<somehost.aws.com>,144;Database=<databasename>; User Id=<databaseuser>;Password=<databasepassword> All the examples I have seen uses multiple variables like DB_USER/DB_PASS/DB_HOST and then you can easily set those with help of secret_value, but there is no example on generating a connection string. How do you solve this? I took a look at aws glue, but it didn't feel like the right optionand I'm not too keen on making a dockerfile to try to pull the official docker image and then create a kind of a wrapper to have environment/sercret variables and then a script that builds up the connection string and sets it before calling the start script for the application (this has other downsides). The reason why I'm not using the CDK v2 is that the current version seems to be broken when you create a new project in WSL (seems to think it's pure ms-windows and fails to create a number of files needed). Thanks in advance for any reply.

I need to migrate an instance with a SQL license to an instance without one to save money on the monthly bill. I've made a copy of the DB to RDS in SQLExpress, and tested with the application successfully. I now need to figure out how to migrate the data to a smaller instance without SQL. Anyone know the best way to achieve this? The basic method of changing instance type fails as it wont let me choose an instance without SQL.

Dear team. I would like to understand how AWS Trusted Advisor optimization checks for Microsoft SQL Server on Amazon EC2 are collected from my EC2 instances. As far as I understand Trusted Advisor is a agentless offering and I am not sure how it is possible to collect these metrics from my arbitrary SQL Server deployment on EC2. Should I install an agent for this or it is done automatically by some routine on EC2 instance itself? Thank you in advance.

We have a database on a SQL Server Standard Edition version 2016 on an EC2 Instance and we have continuous replication for a small portion of the data over to an Aurora Postgres Database using DMS. Sometimes out of the blue, we notice that our data on Aurora has fallen behind, by as much as 90 mins, i.e. data was added in SQL Server but 90 mins later still has not been migrated over by DMS. This can happen times when there is not much a load on either database, i.e. there be only a few transactions to replicate over but nothing is happening. When looking at the logs of the DMS instance we do see this: "Reading from source is paused. Total storage used by swap files exceeded the limit 1048576000 bytes" but as I mentioned there is not much data to replicate and the DMS instance does not seem to be under much stress. Any ideas of what might be causing this would be most welcome. Thanks

I have install EC2 free tier and RDS free tier using SQL Server Express. I left the server for almost 24 hours but did not login to do anything. Now I check my billing, it was increased $20. Why does this happen? Can anyone explain? Better yet, can any AWS staff investigate this issue? Please help. THank you.

I have configured RDS for SQL Server Express (which I hopefully will not be charged). I understand that for free tier, I have 750 hours per month. How is 750 hours per month counted? If I have RDS available for 24 hours, will that be counted as 24 hours usage? Or I have to login to my EC2 server and run an application which connect to RDS database. If my application was connected for 2 hours and that will be counted as 2 hours out of 750 hours. Is this the right way to count the RDS hours?

In RDS I used Easy Create to create a SQL Server database - SQL Express version. I then modified the database to make it publicly available. * I used the default VPC Security Group and added an inbound rule for TCP over port 1433. * I copied the endpoint for the database (referred to below as <endpoint text>) * I opened up SSMS on my laptop and attempted to connect (using SQL Server security) to the db server as follows: <endpoint text>,1433 * I am unable to connect to the server this way - the error is "the wait operation timed out" - error 258. "The server was not found or is not accessible." * I've followed all the steps in the troubleshooting documentation but am not getting anywhere. Thanks!

Hi, I recently migrated a database to RDS MSSQL (i.e. - restored from *.bak file) and want to change the names of the physical files. When I executed "ALTER DATABASE [db_name] MODIFY FILE (Name=[logical_name], FileName=[full_file_path])" command I received this message from my RDS instance: "*Modifying physical file name is not supported on RDS Instances. Modify reverted*". Has anyone seen this message before and if so, what should I do so I can rename the files?

I am trying to use the AWS Schema Conversion Tool to convert a SQL Server instance running on RDS to Aurora MySQL. I have a large number of databases in the same SQL Server instance, and was able to successfully convert the schema for 5 of them. Then I attempted to use the "Refresh from Database" menu option for one of the databases and received an error stating > Metadata loading was interrupted because of data fetching issues. For details, see the log. On checking the logs, I see the following error that is causing the issue: > Error executing 'load-script-object-ext-properties' query: com.microsoft.sqlserver.jdbc.SQLServerException: Collection specified does not exist in metadata : 'sys' Now I am unable to see the schema of any database on that server, and they ALL fail with the same issue. I have tried creating a new SCT project, and using a different SQL login. I am completely blocked at the moment, and not sure how to progress my migration.

Hi, We had a database flag as "Suspect" using MSSQL on RDS. We were unable to recover this database on RDS as RDS does not allow you to set a DB in to emergency mode (the first step of the recovery process). In the end we had to create a MSSQL server on an EC2 instance as every restore would always result in the Suspect flag even older ones. When the DB flagged as "Suspect" on the EC2 instance I was able to recover with DBCC CHECKDB functions after setting the DB to emergency mode. Just wondering if anyone or AWS have a way of dealing with this on RDS should it ever happen again? I've found very little information online and even less on how to deal with Suspect DBs on RDS. Thanks in advance! Chris

Hi there, I am planning to migrate our on-prem SQL Server to RDS SQL Server. Unfortunately, this db was managed by several people and we are not sure that all of the tables have a primary key. I wish to keep the on-prem db and RDS in sync for a couple of days, so I have to be 100% sure that all tables have a primary key. Do you know an automatic way, where I can check if all the tables (1000+) have a primary key? Cheers, Mark

Hi - I am using an RDS SQL Server and have enabled SQL Audit so the audit files can be uploaded to s3 by following this documentation: [SQL Server Audit](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.Audit.html#Appendix.SQLServer.Options.Audit.S3bucket) This mentions that the > The audit log files are automatically uploaded from the DB instance to your S3 bucket. In my environment there is a policy in place however where uploads need to be encrypted with a kms key for uploads to s3 and at present the audit files are failing to be uploaded: ``` { "Effect": "Deny", "Action": "s3:PutObject", "Resource": "*", "Condition": { "StringNotEquals": { "s3:x-amz-server-side-encryption": "aws:kms" } } } ``` Is there a way to pass a kms key to the audit files before RDS automatically uploads the audit files to s3?

Is it possible to use AWS RDS SQL Server as an AAG target from on premise primary?

Customer asked in a RFP the ability to use `named instances` with RDS for Microsoft SQL Server. It probably sound obvious to specialists, is this the `DB Instance identifier` we pass at the RDS creation phase ?

I am using MS-SQL Server 2012 and 2016 on-prem. I want to capture CDC from this database and load it into Redshift using DMS. Since our tables have primary keys, MS-Replication seems to be the only option that will help facilitate this. Currently replication is not enabled and I want to understand the performance impact, any gotchas, possibles issues that can arise when turning replication on.

I have a customer who is planning a migration from on-prem SQL-Server to EC2 SQL Server using DMS. They want to know what will happen in the event the SQL Server AG fails over (on-prem to on-prem failover in event of HW failure for example) while DMS is running to sync data to AWS. Details on what will happen appreciated - customer DBA manager would like to know more than a "it'll be fine" type answer if possible.

While migrating from SQL Server or Sybase database to PostgreSQL, how do you convert a stored procedure with return status and check if procedure call is successful. Sample example: EXEC @return_status = sample_proc

Hi, What is the best practice for migrating an RDS instance from SQL Server Standard Edition to SQL Enterprise Edition? Thanks,

A customer is exploring RDS SQL server. The RDS SQL Server documentation (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html) states that “Native backups of RDS databases larger than 1 TB aren't supported.” The question came up asking what backup options are available if a database is larger than 1 TB? We know that restores support up to 16 TB (or presumably 64 TB if we get AWS support to stripe volumes), but curious what is driving the backup limitation?

For SQL Server running on EC2 and using FSx, when the FSx maintenance window happens, will there be an interruption to SQL Server ? Do the databases become disconnected?

1. Can I stop/resume replication for a RDS SQL Server Read Replica, like we do with on-prem SQL Server? 2. is there any recommended max db size for a replication to occur smoothly? 3. is there any recommended max source change rate for a replication to occur smoothly?

I'm testing Linked Server on RDS SQL Server to point to existing SQL Server (non RDS). Test connection throw error with the following message : ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Named Pipes Provider: Could not open a connection to SQL Server [53]. OLE DB provider "SQLNCLIThe test connection to the linked server failed. 11" for linked server "GFHSVRHRISDEV01.corp.xyz.co.id" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server "GFHSVRHRISDEV01.corp.xyz.co.id" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53) I have validated the network connectivity as well as domain name resolution using EC2, domain resolution works, telnet to port 1433 also open. As this RDS integrated with the Managed Active Directory, I have also checked that the Conditional Forwarding works to lookup target domain. The target database also already configured to accept remote connection. What would be the possible root cause for this? I'm looking for any hints to further troubleshoot this issue.

Is there a work around to scaling an RDS MS SQL Server instance type/size down (with no downtime) without building out a new RDS instance from a snapshot as that has down time? Thank you.

A customer is running SQL Server Standard 2012 on an i2.xlarge instance. He wants to upgrade to i3.xlarge and SQL Server Standard 2017. Are there any issues with SQL server and changing the instance type? Things like SQL expecting certain processor types or numbers? IS this the correct steps for the upgrade? 1. Stop the instance 2. change the instance type to i3.xlarge 3. start the instance 4. upgrade sql server to 2017

A customer is in the midst of RDS adoption and they have lots of questions. Here are the latest few: 1. Our product uses some custom error messages from sys.messages. Based on our installation results it appears as though this is not allowed on RDS. Can you confirm that it is not allowed and if so, is there any possibility that we could get an exception for our product? 2. As part of our regular maintenance on our DBs we require that our two most heavily used DBs be re-indexed and shrunk on a regular basis. Does RDS automatically perform these actions for us? 3. What is the uptime SLA for Single AZ instances? (Multi-AZ is published as 99.95% https://aws.amazon.com/rds/sla/)

A customer would like to know if he can use MSDN licenses for RDS SQL Server Enterprise Edition for a non-production Multi-AZ instance. If it matters, the version they want to use is 10.50.6000.34.v1. They are open to using db.r3 or db.m4 instance types (preferably db.r3). Is this possible? Thanks!