Are CNAME Aliases for Connection Strings a Good Idea?

connectionsdnsNetworksql server

To be very flexible and move database to different servers more easily, we came up with the idea of using an alias per database. So by simply changing a DNS entry, we can route all queries to a different database server without changing the connection string or re-deployment of the respective client application.

To clarify:

Per database we would have an alias. So if the database is moved to a different server, only its DNS alias has to be changed.

For example:

db1-alias.mydomain.com points to db1 on some random server.
db2-alias.mydomain.com points do db2 on some other random server.

So lets say db1 was moved from server1 to server2, there is no need to change the connection string. Only the DNS alias entry has to be changed, forwarding to server2 instead of server1.

Are there any known drawbacks to this?

Best Answer

One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections. This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application.

If you are using a separate alias for each application, you would need to generate and install a new certificate each time you add a new application to the server. The same might apply if you want to make sure there are no unused/invalid SANs in the certificate, in that you would need a new certificate each time you removed an application.

Related Solutions

Sql-server – Remove nulls from TSQL connection string

SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not think it is a database library issue--using different drivers won't change a thing. This is just the way SQL Server behaves.

If you want to convert NULLs to something else, you'll need to explicitly use the IsNull(Expression, ValueIfNull) function or the similar Coalesce() function which takes multiple parameters and returns the first non-null value.

SELECT O.OrderID, IsNull(CustomerName, '') CustomerName
FROM
   dbo.OrderTable O
   LEFT JOIN dbo.CustomerTable C
      ON O.OrderCustomerID = C.CustomerID

Though one comment... why would there be an order with no customer? Wouldn't a customer with no order be more likely?

Update

Can you put in a wrapper that, client side, converts the NULLs to what you want? What process or program are you using to convert the result sets to text?

Sql-server – Changing autogrow settings with database mirroring

I'm addressing the issue using Policy Based Management. I created a policy on the Data File facet that states all files must have @GrowthType = KB.

I also filed a connect item: https://connect.microsoft.com/SQLServer/feedback/details/796354/autogrow-is-percent-flag-set-on-principal-does-not-persist-to-mirror

UPDATE: This is fixed in SQL Server 2008 R2 SP2 CU8 and SP1 CU13.

Best Answer

Related Solutions

Sql-server – Remove nulls from TSQL connection string

Sql-server – Changing autogrow settings with database mirroring

Related Question