search

marcingminski / sqlwatch

SQL Server Performance Monitor
https://docs.sqlwatch.io
Other
436 stars 171 forks source link

SQL Instance and Hostname are too short to allow FQDN #301

Open pagerwho opened 3 years ago

pagerwho commented 3 years ago

Adding a server using the command below will result in an error.

SqlWatchImport.exe --add -s MyserverName.MyDomainReallyReallyLongDomain.Com -d SQLWatch

String or binary data would be truncated in table 'SQLWatch.dbo.sqlwatch_config_sql_instance', column 'hostname'. Truncated value: 'MyserverName.MyDomainReallyReall'.

marcingminski commented 3 years ago

This should be the equivalent of the @@SERVERNAME and should not include the domain name, if you need to connect using the domain name, there is another value for that: [physical_name] which can accomodate 128 chars

marcingminski commented 3 years ago

in fact, you may be right, the hostname is 32 chars but it probably should be 128 or even better 271 (255 for domain, 15 for server name and 1 for the dot)

marcingminski commented 3 years ago

The part of making SQLWATCH Azure friendly also requires accommodating longer hostnames. Azure SQL name can be as long as 255 chars (URL) This is something I have thought of for a long time. I do not want to be changing primary key from the existing 32 chars to 255 chars as it would impact performance and storage. Some time ago I have implemented the below function. It currently simply returns the @@SERVERNAME but its ultimate purpose will be to amalgamate long names into a shortened name. I do not know how to do it yet to make it PK reliable. Fixing this would also make it more Azure SQL friendly

CREATE FUNCTION [dbo].[ufn_sqlwatch_get_servername]()

RETURNS varchar(32) with schemabinding
AS
BEGIN
    -- this has two purposes.
    -- first we do explicit conversion in a single place
    -- second, we can manipulate the @@SERVERNAME, handy on the managed instances where server names can be 255 char long.
    RETURN convert(varchar(32),@@SERVERNAME)
END