I regularly do sql server installations (mainly sql server 2016), in virtual machines or otherwise, and I have a bunch of things like permissions, and linked servers and endpoints that I have mostly all scripted so that in the new installs I can create all these objects.
On a brand new install that I am working on however,
it was supposed to be an exact copy of a specific server let's call it
serverX had a couple of endpoints not specified on my creation of sql server server level objects script
therefore it caused me troble - port 4022 was not listed, and not listening and the powershell connectivity test was not working
declare @sql varchar(1008) set @sql = 'powershell.exe -command Test-NetConnection my_sql_server -port 4022' EXEC xp_cmdshell @sql
although all the firewall was already set up.
to avoid this in the future, is there a way to script all the endpoints? or better would be script all server level objects as per the picture above?
obviously to script this specific endpoint, you just need to right-click and script it:
USE [master] GO /****** Object: Endpoint [ServiceBrokerEndpoint] Script Date: 20/03/2021 16:04:05 ******/ CREATE ENDPOINT [ServiceBrokerEndpoint] STATE=STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL) FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED , MESSAGE_FORWARD_SIZE = 10 , AUTHENTICATION = WINDOWS NEGOTIATE , ENCRYPTION = DISABLED) GO
and after I did that the netstat command started to list port
4022 - the service broker default.
but just to
automate future installs, is there a way to via code or powershell, script all or some server level objects?