on 04-22-2014 7:55 AM
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><default></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><default></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
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>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Hi Yazar,
Does Read only mean no only view authority for JOBS to user and no execution authority ??
Shiva Sahu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.