4

I´m looking for some recommendations or best practice for the following scenario:

  • I need to merge several SQL Server 2014 instances into one (we are switching from many instances to a single clustered instance).

  • The scope of this merge is of about 10 servers, and 400 databases.

  • The source instances are very tightly coupled. There are many linked servers, and a lot of stored procedures, views, and functions that use them.

  • Some SPs also perform operations in the file systems of other servers in the scope of this migration.

  • As far as I know, we may be forced to change some DB names because we would have name collisions between databases on the destination instance.

I have scripted all DB objects so I can do some text search to find all occurrences of queries that use a given linked server.

So far I believe my options are:

1- The long one. Change the queries that use linked servers so it will query to the same instance (just to another DB in the instance).

2- A short one. We believe we can create DNS aliases that would make the old machine names point to the new clustered instance.

Then we would create several linked servers in the clustered instance pointing to the same instance. The linked servers would have the same names as in the source servers. This should make the old SPs, functions, and views work without any change (except when we need to change a DB name).

Does anyone know a better option or have any recommendations?

  • 1
    DNA aliases --> DNS or CNAME that points to the new home. – Kin Shah Dec 19 '16 at 22:48
  • Yes, DNS. Thanks for pointing out the typo. – Christian Clark Dec 20 '16 at 1:46
1

I faced an analogous problem where we had different environments using their own linked servers so we could never guarantee that dev was like integration was like test was like production.

The "doh" moment was when we worked out that you can name a linked server whatever you want irrespective of its physical name.

I've not got access to a SQL Server instance to test whether you can create a linked server to itself but if possible then entire redirection would be self contained within your consolidated instance until such time as you were able to weed out the procs that rely on linked servers.

|improve this answer|||||
  • Thanks Dave. I will test the self linked server today and will update this post. – Christian Clark Dec 20 '16 at 12:09
  • I just tested the self linked server and seems to work OK. – Christian Clark Dec 21 '16 at 15:58
1

If performance is important, Option1 has the clear edge.

Every time a query goes across a linked server, performance takes a hit as what you expect to behave like set-based operations become serialised by OLEDB. They go row-by-row.

It helps if you follow best practices for linked server queries (OPENROWSET for example) and ensure all remote processing is done on the other side, but the results will still come across serialised. (Look for Conor Cunningham's presentation on Distributed Queries).

If all your remote tables are small lookup tables this may not be a serious issue, but if millions of rows have to come across linked servers then performance will suffer. There used to be an extra layer of problems with invisible statistics (for data readers) over linked servers, but as you're running SQL 2014 this will not affect you.

If you can afford the Dev cost of eliminating the linked servers, Just Do It!

|improve this answer|||||
0

An alternative would be to use virtualisation and maintain separate instances on the same physical host. The benefit in doing this is each instance maintains its own dedicated allocation of CPU and memory that can be scaled up or down as necessary. If you merge all instances as you describe, then all databases will be competing for the same CPU and memory. In addition, a shared plan cache will now mean plans from one database can age out plans from another.

If you maintain separate instances in VM, then you can retain the same linked servers and not have to make any database or software changes.

Performance monitoring is also far easier with multiple instances. It is simple to see which VM are contributing to peaks in resource usage.

In order to use maximum virtualisation described, each core of the physical host must be licensed for SQL Server enterprise edition. Valid software assurance is also a condition of this licensing model.

|improve this answer|||||
  • Thanks for the recommendation. In our case the decision of using a single clustered instance is definitive, but I understand the benefits of many virtualised environments that you mention. – Christian Clark Dec 21 '16 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.