information connecting a SQL Server DB to Data Warehouse Cloud

[claudiorossi_sap]

Hi

In our infrastracure ( internal network ) we have a Microsoft SQL server Database that we need to connect to SAP Data wharehouse Cloud to read and import some table from SQL DB to SAP Datawharehoues cloud.

Can you confirm that it is technically possible ? Can you tell me wich agent and connector I need to install on premise to be able to do this without expose SQL server on internet and to be able to read Microsoft SQL DB data and import in sap data wharehouse cloud ?

thank you

regards

[murllimaraati]

MS SQL Server 2016 is supported by SAP DWC and in the DP agent you need to register MssqlLogReaderAdapter & then create a connection in DWC using Microsoft SQL Server. Once the connection is established you can create data flows

[claudiorossi_sap]

is MSSQL Express edition also supported by the DP Agent ? ( not for realtime replciation )

[murllimaraati]

Yes. It will support. I have not tried.. but i am confident it will support.

[claudiorossi_sap]

We hope.

is there any step by step guide to configure DPA for an MSSQL Server ? It is possible to configure only access from DPA to MSSQL Server DB to test the connection ( i still not have DWC tenant ) ? I have installed DPA Agent on my pc but i can't find a configuration console to do the configuration to connect to sql and test it

thank you !

[murllimaraati]

Hi,

Please follow the below link

https://help.sap.com/docs/SAP_DATA_WAREHOUSE_CLOUD/9f804b8efa8043539289f42f372c4862/ea69328fb328449a...

Best Regards

Murlli Maraati

[XaviPolo]

As they said it is possible, but the configuration depends on whether you want: remote access to SQL Server or replicate data in real time.

In both cases you need the DPAgent installed on your network, but real-time replication requires some extra steps as it works through triggers.

Regards,

[xiaoming_wu]

Hi Claudio,

I think you already checked the PAM document and see "Enterprise Edition required for real-time replication."
https://support.sap.com/content/dam/launchpad/en_us/pam/pam-essentials/TIP/PAM_HANA_SDI_2_0.pdf

Yes, mssql is supported as source through DP agent in DWC.

Best regards
Helen

[claudiorossi_sap]

Hi Helen

thank you for your answer. Yes , i see this PAM and regard this I have another question;

Now i have connected two MS SQL databases ( that are MS SQL SERVER EXPRESS EDITION ) to our SAP BW on premise with DB connect in RSA1 tcode and it work fine.

My question is : does DP agent support SQL SERVER EXPRESS EDITION or only enterprise edition as source DB system ?

as you write "Enterprise Edition required for real-time replication." , so my question is if i don't want to use realtime replication but only simple table reading can i technically connect this MSSQL database express edition to SAP DWC as source systems using SDI 2.0 ?

thnak you

regards

[xiaoming_wu]

Hi Claudio,
Yes, you can use it for batch loading.

Best regards
Helen

[yogananda]

claudiorossi_sap

Yes, it is technically possible to connect your Microsoft SQL server database to SAP Data Warehouse Cloud. You will need to install a connector on your premises and configure it to allow access from SAP Data Warehouse Cloud. The exact connector and agent will depend on the type of SQL server you are using, however some of the most popular options include SAP Data Services, SAP Replication Server, and Microsoft SQL Server Integration Services. You will also need to ensure that you have the appropriate security measures in place to ensure that your SQL server is not exposed to the internet.

[claudiorossi_sap]

I use MS SQL Server 2016

Sorry but I don't know the option you write,

I see in SAP documentation and seems that the connector is "Data provisioning agent" linked to this documentation ---> https://help.sap.com/docs/HANA_SMART_DATA_INTEGRATION/7952ef28a6914997abc01745fef1b607/d52c01c93e674...

Is it Correct ?

[yogananda]

claudiorossi_sap

[claudiorossi_sap]

This Blog is to connect to an on premise Hana... I must connect to an on premise SQL Server.

Can you confirm that this right agent to implement ?

regards

[yogananda]

claudiorossi_sap

yes that's correct.. DP agent is the right to connect and bring the data from MSSQL to DWC and since its 3rd party SQL server, we don't have any blogs written. If incase someone writes it, I will post it to this question again.

[claudiorossi_sap]

Hi Thank you for your answer

Another question: does DP agent support SQL SERVER EXPRESS EDITION as source DB system ? in my searches i found that only real-time replication probably is not supported, but the other way of read data from SQL SERVER EXPRESS EDITION for import in SAP DWC seems are possible because not denied.

Can you confirm this for sure is technically possible ?

regards