cancel
Showing results for 
Search instead for 
Did you mean: 

Datastore config to connect Readonly SQL Server 2012

Former Member
0 Kudos

We upgrade our source database server from SQL Server 2008 to SQL Server 2012 Always On with High Availability model. There is a read-only mirror db server in this availabilty group. 

We plan to use the read-only node for etl sources.  In a oledbb connection string, we can connect the read-only instance   by adding "ApplicationIntent=READONLY" string but i can not find any property in SAP BO Dataservices - data store object.


How can i connect to read-only instance with Data services 4.1. 

Sample atl of a datastore object:

#__AW_Repository_Version '14.1.1.0000';

#__AW_Product_Version '14.1.1.354';

#__AW_ATL_Locale 'tur_tr.utf-8';

AlGUIComment ("ActaName_1" = 'RSavedAfterCheckOut', "ActaName_2" = 'RDate_created', "ActaName_3" = 'RDate_modified', "ActaValue_1" = 'YES', "ActaValue_2" = 'Thu Mar 14 15:24:46 2013', "ActaValue_3" = 'Mon Feb 10 17:40:07 2014', "x" = '-1', "y" = '-1')

CREATE DATABASE DATASTORE DS_SIS_INTEGRATION::'550a10ab-4313-4d6b-969f-d3a0d25bde37' SET ("DBLiveLoad" = 'no', "application_type" = 'Custom', "cdc_enabled" = 'no', "datastore_repotype" = 'local', "ds_configuration_enabled" = 'yes', "ds_configurations" = '<?xml version="1.0" encoding="WINDOWS-1254" ?>

<DSConfigurations>

<DSConfiguration default="true" name="Test">

<case_sensitive>yes</case_sensitive>

<database_type>Microsoft_SQL_Server</database_type>

<loader_xact_size>1000</loader_xact_size>

<locale_codepage>iso-8859-9</locale_codepage>

<locale_language>tur</locale_language>

<locale_territory>&lt;default&gt;</locale_territory>

<mssql_windows_authentication>no</mssql_windows_authentication>

<password>lorem ipsum</password>

<server_codepage>iso-8859-9</server_codepage>

<sql_server_database>SIS_INTEGRATION_TEST</sql_server_database>

<sql_server_dataserver>Pandoradev</sql_server_dataserver>

<sql_server_version>Microsoft SQL Server 2008</sql_server_version>

<user>boETLTest</user>

</DSConfiguration>

<DSConfiguration default="false" name="Production2012">

<database_type>Microsoft_SQL_Server</database_type>

<loader_xact_size>1000</loader_xact_size>

<locale_codepage>iso-8859-9</locale_codepage>

<locale_language>tur</locale_language>

<locale_territory>&lt;default&gt;</locale_territory>

<mssql_windows_authentication>no</mssql_windows_authentication>

<password>lorem ipsum</password>

<server_codepage>iso-8859-9</server_codepage>

<sql_server_database>SIS_INTEGRATION</sql_server_database>

<sql_server_dataserver>drake</sql_server_dataserver>

<sql_server_version>Microsoft SQL Server 2012</sql_server_version>

<user>boETL</user>

</DSConfiguration>

</DSConfigurations>

', "enable_data_transfer" = 'yes', "import_unknown_type" = 'no', "name" = 'DS_SIS_INTEGRATION', "pcache_force_utf16" = 'no', "unknown_type_size" = '255');

Thanks for your help

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Solution is:

Export the datastore to atl file. Add  <db_addl_conn_info>ApplicationIntent=ReadOnly;</db_addl_conn_info> in DSConfiguration tag. And re-import the atl.

Now, Datastore goes the read-only intent of sql server


<DSConfiguration default="true" name="Prod_2012">

<database_type>Microsoft_SQL_Server</database_type>

<sql_server_version>Microsoft SQL Server 2012</sql_server_version>

<db_addl_conn_info>ApplicationIntent=ReadOnly;</db_addl_conn_info>

</DSConfiguration>

Answers (2)

Answers (2)

former_member186897
Contributor
0 Kudos

You can connect in the same way you connect for a normal db. You need to give BODS User read only permission on this database so that it won't be allowed to write any data (even by mistake). I think datastore has nothing to do with the permissions and it has to be defined at the DB Level.

Former Member
0 Kudos

This is not about the security. There are two replicated sql servers. One of them is working read / write and one of them is working in read-only mode. We plan to decrease read load by connecting the read-only instance.  But we do not know which server is read-only, sql server manages it.

If i can use oledb connection i can reach the read-only instance by adding ApplicationIntent=READONLY statement in connection string. 

I'd like to know that i can connect to read-only instance of sql server in Data services.

former_member200473
Contributor
0 Kudos

Hi Yazar,

Does  Read only mean no only view authority for JOBS to user  and no execution authority ??

Shiva Sahu