[AccessD] Linking to different instance of SQLEXPRESS

[Paul Wolstenholme]

In the cold light of morning I can see I wasn't thinking clearly last night
- apologies.
One node can have multiple SQL Server instances (I'm really not sure why)
and each instance can have multiple databases.

Paul Wolstenholme


On Mon, 4 Sept 2023 at 18:23, Gustav Brock via AccessD <
accessd at databaseadvisors.com> wrote:

> Hi Børge
>
> Yes, running two (or more) instances requires individual port numbers.
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På
> vegne af Borge Hansen
> Sendt: 4. september 2023 06:45
> Til: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Emne: [AccessD] Linking to different instance of SQLEXPRESS
>
> So, we've installed SQLEXPRESS 2022 (16.) on a MS Windows Server 2022 (in
> place update from MS Windows Server 2012)  as a separate instance to the
> previously installed SQLEXPRESS instance (SQLEXPRESS 2014 version 12.) On
> the Firewall setup we set SQLEXPRESS2022 instance to listen on port 1433
>
> From our MS Access frontend for the 2014 version we linked using DSN less
> connection using [ComputerName]\SQLEXPRESS, 1433 to point to the instance
> holding the database
>
> To access the restored database now living on the 2022 Express version we
> then for the DSN less connection used [ComputerName]\SQLEXPRESS2022, 1433
>
> Now on the linked tables on the frontend it says we are linked to
> SQLEXPRESS2022, 1433 but in actual fact we are still linking to the 2014
> version!!! ???
>
> On
>
> https://learn.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?view=sql-server-ver16
>
> I found the following:
> ++++++
> Connecting Using the SQL Server Browser Service The SQL Server Browser
> service listens for incoming requests for SQL Server resources and provides
> information about SQL Server instances installed on the computer. When the
> SQL Server Browser service is running, users can connect to named instances
> by providing the computer name and instance name, instead of the computer
> name and port number. Because SQL Server Browser receives unauthenticated
> UDP requests, it is not always turned on during setup. For a description of
> the service and an explanation of when it is turned on, see SQL Server
> Browser Service (Database Engine and SSAS).
> To use the SQL Server Browser, you must follow the same steps as before
> and open UDP port 1434 in the firewall.
> +++++
>
> INTERESTING IT SAYS:
> When the SQL Server Browser service is running, users can connect to named
> instances by providing the computer name and instance name, instead of the
> computer name and port number.
>
> So it appears that when using the SQLEXPRESS2022 I should use another port
> number, otherwise it will link to the first installed sql server instance
> that is listening to that port number??
> Am I correct in assuming that?
>
> As for the SQL Server Browser Service, I am not sure if this service is
> available by SQL Server Express.
>
> Any ideas??
> Thanks,
> /borge
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>