Forum Widgets
Latest Discussions
Best Practices for Connecting Internal SQL Server Financial Systems to Online Payment Platforms
I currently have an internal enterprise system used for purchasing, payments, and finance operations. The system runs on an on-premises database using Microsoft SQL Server and stores all financial transactions and internal workflow data. We now have a new requirement to enable online payment services for customers. These services will be exposed externally (likely in the cloud) and must interact with the same financial system so that transactions are reflected in our internal database. My main concerns are related to architecture, security, and data synchronization. Key points about the current setup: The core system and database are hosted internally (on-premises). The database contains sensitive finance and payment data. Internal processes depend on the current database structure and workflows. The new requirements: Develop an online payment service accessible over the internet. Ensure transactions from the online service update the internal system. Maintain data integrity and security. Avoid performance issues for the internal system. I’m evaluating a few possible approaches but I’m unsure which is best practice: Allow the cloud payment service to connect directly to the internal SQL Server database through secure networking. Maintain a replicated or read/write copy of the database in the cloud. Use SQL Server replication (transactional or snapshot) between on-prem and cloud. Introduce an API or middleware layer that handles all transactions and updates the internal database. Maintain separate databases and synchronize transactions asynchronously. My main questions: Is it recommended to expose the internal SQL Server database directly to cloud services? Should I use replication, a secondary database, or a service/API layer? What architecture pattern is commonly used for integrating on-prem financial systems with online payment platforms? How can we ensure consistency between internal transactions and online payments? Are there recommended SQL Server features or patterns for this scenario (replication, service broker, CDC, etc.)? Any advice on best practices, architecture patterns, or real-world implementations would be greatly appreciated.
Best architecture to integrate internal SQL Server system with cloud-based online payment services
I currently have an internal enterprise ERP system also need to be integrated with online payments, and finance operations. The system runs on an on-premises database using Microsoft SQL Server 2022 and stores all financial transactions and internal workflow data. We now have a new requirement to enable online payment services for customers. These services will be exposed externally (likely in the cloud) and must interact with the same financial system so that transactions are reflected in our internal database. My main concerns are related to architecture, security, and data synchronization. Key points about the current setup: The core system and database are hosted internally (on-premises). The database contains sensitive finance and payment data. Internal processes depend on the current database structure and workflows. The new requirements: Develop an online payment service accessible over the internet. Ensure transactions from the online service update the internal system. Maintain data integrity and security. Avoid performance issues for the internal system. I’m evaluating a few possible approaches but I’m unsure which is best practice: Allow the cloud payment service to connect directly to the internal SQL Server database through secure networking. Maintain a replicated or read/write copy of the database in the cloud. Use SQL Server replication (transactional or snapshot) between on-prem and cloud. Introduce an API or middleware layer that handles all transactions and updates the internal database. Maintain separate databases and synchronize transactions asynchronously. My main questions: Is it recommended to expose the internal SQL Server database directly to cloud services? Should I use replication, a secondary database, or a service/API layer? What architecture pattern is commonly used for integrating on-prem financial systems with online payment platforms? How can we ensure consistency between internal transactions and online payments? Are there recommended SQL Server features or patterns for this scenario (replication, service broker, CDC, etc.)? Any advice on best practices, architecture patterns, or real-world implementations would be greatly appreciated.
SSRS 2016 Browsing/API very slow
Since moving an existing SSRS server to an Azure VM we get extremely slow performance when opening to browse reports. Devtools shows 2 calls to the API are taking 25s+ to respond - ServiceState and Me. When we call the API direct for those 2 (e.g. <servername>/reports/api/v1.0/ServiceState) they are both the same - very slow. They return a 200 response, so are working ok, and once you've logged in browsing around is fine. The reports themselves run well, and if we skip the API and just browse to ReportServer it's fast. The only thing that has changed on the server is the IP when it moved to Azure - there are no other new firewall rules/ACLs/etc in place. Has anyone had this issue or can shed some light on this?
SQL Server issue but don't know what - please help!
Hi, I'm facing a SQL Server focused issue that I don't understand why it's occurring and would like your help to identify and resolve, please. I will provide an in-depth breakdown of the scenario. Two years ago, I created a Azure Data Factory (ADF) Pipeline to take data from Azure Synapse to Azure SQL Server, and two other Pipelines to take data from an On-Premises Sage server to the same Azure SQL Server. These Pipelines were working perfectly up until two days ago (11/03/26) when the Pipelines still always complete successfully but the duration have increased greatly. The below screenshot shows the log of these three Pipeline for the past week. Note how prior to the 12/03/26 the Synapse Pipeline took about 6 minutes to complete and the two Sage Pipelines took around 25 seconds. Also note how on the 12/03/26 the Synapse Pipeline took over 3 hours but the two Sage Pipelines continued with their normal 25 seconds. Notice today (13/03/26) the Synapse Pipeline was still slow but now the two Sage Pipelines are taking over an hour. I'll note here that the Pipelines still complete successfully (so the config must be correct as it has been for two years). Each Pipeline contains a single (for Sage) or several (for Synapse) 'Copy data' objects. The objects have their source configuration to simply extract from the source (so either Synapse or Sage) and then the sink configuration has a 'Pre-Copy script' which simply Truncates the target SQL Server table, before loading to the SQL Server table. The screenshot below is one example - each Pre-Copy script is populated the same but just with the tables being different. When I look at the log for the Pipelines, I see a common theme for each and that is the 'Pre-Copy script' (the Truncate) is consuming 99% of the time. The screenshot below shows this common theme. So at this point, I ask the question why, after two years of all working well and completing very quickly, are the Pipelines taking so long to complete? This also seems to be an intermittent problem as I have performed several manual executions which will take a long while, then revert back to the quick several minutes again (so good), then revert once more to taking a long while again. It is intermittent. See the screenshot below. Notice how the same Pipeline have different durations. It's intermittent. The reason why I think it is a SQL Server focused issue for the following reasons: 1) Synapse - Performing simple commands (Select Top) returns data within seconds. 2) SQL Server - Performing simple commands (Select Top) usually takes seconds but during the past two days is often taking half hour. 3) SQL Server - When amending a SQL View it would take a split second to complete. During the past two days, it goes over ten minutes and still doesn't complete (I was amending the View for testing purposes). 4) SQL Server - When looking at the Views node under a database, during the past two days, it intermittently doesn't show the View. Sometimes, it will work if I log out and then back into SQL Server. 5) Power BI - Refreshing a Power BI report, whether from the Power BI Service or Desktop, call SQL Server Views. These SQL Server Views read from several SQL Server tables. These report refreshes are failing due to an IDbCommand interface error. These Power BI report data refreshes simply read from a SQL Server table via a SQL View. They don't reference or consider at all the any ADF Pipelines. The Pipelines execute between 2am and 3am each morning. The Power BI report refresh their data around 12pm to 5pm. As SQL Server seems to be problematic at the point of the 'Pre-Copy script', which is one of the end to end process, and then problematic at the very other end where Power BI reports consumes SQL Views - leads me to believe the issue is with SQL Server. I am a report developer and not an ADF expert. I've built the ADF process on the side. I haven't changed any development/configuration/etc... between this all working and failing. Our I.T. department have advised they have made no changes to contribute to this issue. Please can someone advise on what's happening here and why this issue has arose when for two years all was fine? Thanks.
SSMS 22 - Not seeing the Database name information in the Status Bar and Title bar is gone.
I installed SSMS 22 yesterday and am trying to get used to the features. I am not a fan of the new layout. I might be in the future, but at the moment, not so much. Status Bar settings are all set to [True] But nothing is showing in the Status Bar. There is no Title Bar anymore. WHY Would they take away the Title Bar? That is insane. So, my question to the Forum is this. How do we get the Database Name information in the Status Bar? As stated, everything is set to TRUE in settings. Also, will the Status Bar show the title of the Editor file you are working on? (I miss the old design, to be honest. )
SSMS 21/22 Error Upload BACPAC file to Azure Storage
Hello All In my SSMS 20, I can use "Export Data-tier Application" to export an BACPAC file of Azure SQL database and upload to Azure storage in the same machine, the SSMS 21 gives error message when doing the same export, it created the BACPAC files but failed on the last step, "Uploading BACPAC file to Microsoft Azure Storage", The error message is "Could not load file or assembly 'System.IO.Hashing, Version=6.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. (Azure.Storage.Blobs)" I tried the fresh installation of SSMS 21 in a brand-new machine (Windows 11), same issue, Can anyone advice? ThanksAccess to SQL Jobs
Hi, I have several jobs in SQL Server Agent. In the department there are 3 people who need administration access to the jobs. What kind of security access must they have? They already have SQL Agent Operator, SQL Agent Reader and SQL Agent User. As far as I know you can only edit jobs where you are the Owner. But, all 3 people must be able to edit all jobs. How to solve that issue?
Synchronize SQL database between two servers
Questions (to make right the synchronize SQL database between two servers) 1. Is required to have SQL Enterprise in both servers, for to be able the synchronization? Or will be enough to have SQL Enterprise in server1. And in server2 with only SQL Express, please? 2. Which requirements are required to fulfil or prepare for to be able synchronize SQL database between two servers, please? 3. Which possibilities (when are more possible solutions) have existed for synchronize SQL database (totally awaiting between four servers), please? 4. Exist a manual for reading an synchronization of SQL server - settings and steps? Thanks in advance.
SQL Server AG Failover - Automatic Failover
Hello, I am looking for a straight and definitive answer that I was hoping someone could answer for me. I want to trust what Copilot says, but I really need to hear it from Microsoft and I can't find any documentation from Microsoft confirming my question. My Environment: 2 replicas in datacenter 1 1 replica in datacenter 2 All three (3) replicas are set to synchronous-commit mode with automatic failover. I tested the failover manually between all three (3) replicas without issue. When I test the automatic failover - I take down both replicas in datacenter 1 at the same time to simulate a datacenter outage. I look at the replica in datacenter 2 and it is just says (Resolving...) next to the replica name. The replica does not come online and the DB is not moved. When I was searching I couldn't find out why. So I turned to Copilot not solve the issue, but to see if it could point me in the right direction. I tell Copilot my setup and what happened. Copilot responded stating that by design from Microsoft you cannot have more than two (2) replicas set to synchronous-commit mode with automatic failover in a SQL Server AG instance. That if more than two (2) are set for automatic failover. The SQL Server AG will use the first two (2) replicas it sees in its metadata and ignore the rest. Copilot went into detail about why this is designed this way, but the amount of information would make this post longer than it already is. If this is true - then when I took down both replicas in datacenter 1, SQL Server AG only saw those two (2) replicas in datacenter 1 as the available replicas to use for an automatic failover and thus why the replica in datacenter 2 did not come online and the DB not being moved So let's do a test. I brought back up the two (2) replicas in datacenter 1. Then I made a change in the AG proprieties. I set the 2nd replica in datacenter 1 to manual. So 1 replica is set to automatic failover and 1 replica is set to manual failover in datacenter 1. The replica in datacenter 2 is set to automatic failover I then take down both replicas in datacenter 1 again to simulate the "outage" and the replica in datacenter 2 comes online and the DB is moved. So is Copilot right? Can there only be two (2) replicas allowed to have/use automatic failover? I cannot find a definitive answer confirming this. Or is my configuration wrong/missing something and if it is, could you please point me in the right direction on how to get this resolved?
