Repository: denisenkom/go-mssqldb
Pull request #525 (2019-09-30 20:49:37)

Errors out when applicationintent is specified without database
Addressing #310 

codecov[bot]: # [Codecov](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=h1) Report
> Merging [#525](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=desc) into [master](https://codecov.io/gh/denisenkom/go-mssqldb/commit/208c0a49853806704ec339319ab3a50fbe323643?src=pr&el=desc) will **increase** coverage by `0.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525/graphs/tree.svg?width=650&token=MoMB2u5avp&height=150&src=pr)](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master     #525      +/-   ##
==========================================
+ Coverage   68.84%   68.86%   +0.01%     
==========================================
  Files          22       22              
  Lines        5056     5058       +2     
==========================================
+ Hits         3481     3483       +2     
  Misses       1369     1369              
  Partials      206      206
```


| [Impacted Files](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [conn\_str.go](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525/diff?src=pr&el=tree#diff-Y29ubl9zdHIuZ28=) | `100% <100%> (ø)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=footer). Last update [208c0a4...3bb5bee](https://codecov.io/gh/denisenkom/go-mssqldb/pull/525?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).


Patch:
diff --git a/README.md b/README.md
index 669b0fe..b655176 100644
--- a/README.md
+++ b/README.md
@@ -56,7 +56,7 @@ Other supported formats are listed below.
 * `hostNameInCertificate` - Specifies the Common Name (CN) in the server certificate. Default value is the server host.
 * `ServerSPN` - The kerberos SPN (Service Principal Name) for the server. Default is MSSQLSvc/host:port.
 * `Workstation ID` - The workstation name (default is the host name)
-* `ApplicationIntent` - Can be given the value `ReadOnly` to initiate a read-only connection to an Availability Group listener.
+* `ApplicationIntent` - Can be given the value `ReadOnly` to initiate a read-only connection to an Availability Group listener. The `database` must be specified when connecting with `Application Intent` set to `ReadOnly`. 
 
 ### The connection string can be specified in one of three formats:
 
diff --git a/conn_str.go b/conn_str.go
index 412a871..0742755 100644
--- a/conn_str.go
+++ b/conn_str.go
@@ -205,6 +205,9 @@ func parseConnectParams(dsn string) (connectParams, error) {
 	appintent, ok := params["applicationintent"]
 	if ok {
 		if appintent == "ReadOnly" {
+			if p.database == "" {
+				return p, fmt.Errorf("Database must be specified when ApplicationIntent is ReadOnly")
+			}
 			p.typeFlags |= fReadOnlyIntent
 		}
 	}
diff --git a/doc/how-to-use-applicatinintent-connection-property.md b/doc/how-to-use-applicatinintent-connection-property.md
new file mode 100644
index 0000000..00c552d
--- /dev/null
+++ b/doc/how-to-use-applicatinintent-connection-property.md
@@ -0,0 +1,26 @@
+# How to use the ApplicationIntent Connection Property
+
+In an Always On Availability Group, support for read-only routing in SQL Server can be configured. Read-only routing refers to the ability of SQL Server to route read-only connection requests to an available Always On secondary replica that is configured to allow read-only workloads when running under the secondary role. To support read-only routing, the availability group must possess an availability group listener. For more information on configuring read-only routing, see [Configure read-only routing for an Always on availability troup](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017).
+
+For an ease of understanding, let's assume you have the following set up:
+- An availability group with primary replica `SQL1` and one secondary replica `SQL2`
+- An availability database `CUSTOMER` is added to the availability group
+- An availability group listener `AGListener:16333` is added to the availability group
+- Read-only routing is configured. The `READ_ONLY_ROUTING_LIST` for `SQL1` is `'SQL2','SQL1'` and the `READ_ONLY_ROUTING_LIST` for `SQL2` is `'SQL1','SQL2'`
+
+An availability group listener is a virtual network name (VNN) to which clients can connect to in order to access database in a primary or secondary replica in an Always On availability group. In this case, the availability group listener `AGListener:16333` can be used as the server name in the connection string. If your intent of the connection is only to read from the database, you can specify the connection property `ApplicationIntent=ReadOnly`. The availability group listener will direct you to the secondary database `SQL2`. If your intent to to write to the database as well, then do not specify the `ApplicationIntent`, and the availability group listener will direct you to the primary database `SQL1`. Furthermore, since replication in an availability group is configured at the level of the database, when the connection property `ApplicationIntent=ReadOnly` is specified, the `database` must also be specified, otherwise connection fails.
+
+Connection string that fails when using `ApplicationIntent=Readonly`:
+```
+connString := "sqlserver://username:password@AGListener:16333?ApplicationIntent=ReadOnly"
+```
+
+Connection string that directs to the secondary replica `SQL2`:
+```
+connString := "sqlserver://username:password@AGListener:16333?database=CUSTOMER&ApplicationIntent=ReadOnly"
+```
+
+Connection string that directs to the primary replica `SQL1`:
+```
+connString := "sqlserver://username:password@AGListener:16333?database=CUSTOMER"
+```


Test patch:
diff --git a/conn_str_test.go b/conn_str_test.go
index 3e89744..06f3808 100644
--- a/conn_str_test.go
+++ b/conn_str_test.go
@@ -16,6 +16,7 @@ func TestInvalidConnectionString(t *testing.T) {
 		"encrypt=invalid",
 		"trustservercertificate=invalid",
 		"failoverport=invalid",
+		"applicationintent=ReadOnly",
 
 		// ODBC mode
 		"odbc:password={",
@@ -54,7 +55,7 @@ func TestValidConnectionString(t *testing.T) {
 		{"server=(local)", func(p connectParams) bool { return p.host == "localhost" }},
 		{"ServerSPN=serverspn;Workstation ID=workstid", func(p connectParams) bool { return p.serverSPN == "serverspn" && p.workstation == "workstid" }},
 		{"failoverpartner=fopartner;failoverport=2000", func(p connectParams) bool { return p.failOverPartner == "fopartner" && p.failOverPort == 2000 }},
-		{"app name=appname;applicationintent=ReadOnly", func(p connectParams) bool { return p.appname == "appname" && (p.typeFlags&fReadOnlyIntent != 0) }},
+		{"app name=appname;applicationintent=ReadOnly;database=testdb", func(p connectParams) bool { return p.appname == "appname" && (p.typeFlags&fReadOnlyIntent != 0) }},
 		{"encrypt=disable", func(p connectParams) bool { return p.disableEncryption }},
 		{"encrypt=true", func(p connectParams) bool { return p.encrypt && !p.disableEncryption }},
 		{"encrypt=false", func(p connectParams) bool { return !p.encrypt && !p.disableEncryption }},