Post reply

Quick Question. Could be silly

  • February 10, 2010 at 12:37 pm

    #142165

    Hi,

    Is there a unique number associated with each Sql Server 2005 Installation?

    Ex:

    When I do SELECT @@SERVERNAME, I get the server name. Similarly, I want something like SELECT @@Installation_ID that gives me a unique (may be GUID) for each sql server installation.

    This is not something I configure, it must come with the Sql Server software, jut like the built-in-functions.

    Also, Is there a way, if I want to, assign a unique ID for each Sql server 2005 Installation? These installations could be disconnected/connected.

    thanks,

    _Uday

    SqlTimes Blog on Sql Server

  • February 10, 2010 at 1:41 pm

    #1117179

    If you assume that you will not have 2 SQL server instances with the same servername, you could maybe use either the checksum or hasbytes function applied to the servername like for example:

    SELECT @@servername, hashbytes('SHA1',@@servername), checksum(@@servername)

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • February 10, 2010 at 1:41 pm

    #1117180

    You *might* find something in the registry.. Here is a little taste of where to look:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\Setup]

    "ProductCode"="{B5153233-9AEE-4CD4-9D2C-4FAAC870DBE2}"

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\Setup\SQL_Engine_Core_Inst]

    "ProductCode"="{B5153233-9AEE-4CD4-9D2C-4FAAC870DBE2}"

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\Setup\SQL_Engine_Core_Inst\1033]

    "ProductCode"="{58721EC3-8D4E-4B79-BC51-1054E2DDCD10}"

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\Setup\SQL_FullText_Adv]

    "ProductCode"="{06A7EA72-0F00-4D53-A81C-A5D925711141}"

    CEWII

  • February 10, 2010 at 1:49 pm

    #1117184

    Not sure if product codes would be unique for same product installs on different servers... although reading a value from registry sounds interesting as well...

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • February 10, 2010 at 2:11 pm

    #1117197

    I agree, *might* work.. Not promising, but it is a place to start and if he has a couple different servers he can find out for himself.

    CEWII

  • February 10, 2010 at 2:27 pm

    #1117204

    There isn't a built-in function for that, so far as I know.

    However, does this give you enough for what you need?

    select serverproperty('machinename'), serverproperty('instancename');

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 11, 2010 at 7:01 am

    #1117546

    Thanks for the response, but the server name and instance name could be the same, as they'll in in different networks.

    Richard M. (2/10/2010)

    If you assume that you will not have 2 SQL server instances with the same servername, you could maybe use either the checksum or hasbytes function applied to the servername like for example:

    SELECT @@servername, hashbytes('SHA1',@@servername), checksum(@@servername)

    SqlTimes Blog on Sql Server

  • February 11, 2010 at 7:03 am

    #1117547

    Thanks for the response.

    Looking in the registry sounds like a good idea.... I'll look further into this to see if this helps.

    Elliott W (2/10/2010)

    I agree, *might* work.. Not promising, but it is a place to start and if he has a couple different servers he can find out for himself.

    CEWII

    SqlTimes Blog on Sql Server

  • February 11, 2010 at 7:09 am

    #1117548

    Thanks GSquared.

    I'll try to see, if I can explain our situation:

    Our software will be installed at various locations in US. We need a way to uniquely identify each installation. Since these installations could be connected to a network (to internet) or just standalone laptops we could not go by License Key

    Hope this helps,

    _Uday

    GSquared (2/10/2010)

    There isn't a built-in function for that, so far as I know.

    However, does this give you enough for what you need?

    select serverproperty('machinename'), serverproperty('instancename');

    SqlTimes Blog on Sql Server

  • February 11, 2010 at 8:15 am

    #1117587

    create a table in master with a guid? ?

    create table dbo.whoami (

    sqlserverid uniqueidentifier

    ,location varchar(100)

    ,servername varchar(128)

    ,instancename varchar(128)

    )

    insert dbo.whoami

    select newid()

    ,'boston'

    ,cast(serverproperty('machinename') as varchar(128))

    ,COALESCE(cast(serverproperty('instancename')as varchar(128)),'Default')

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • February 11, 2010 at 8:42 am

    #1117606

    _UB (2/11/2010)

    Thanks GSquared.

    I'll try to see, if I can explain our situation:

    Our software will be installed at various locations in US. We need a way to uniquely identify each installation. Since these installations could be connected to a network (to internet) or just standalone laptops we could not go by License Key

    Hope this helps,

    _Uday

    GSquared (2/10/2010)

    There isn't a built-in function for that, so far as I know.

    However, does this give you enough for what you need?

    select serverproperty('machinename'), serverproperty('instancename');

    In that case, I'd be inclined towards creating a CLR function that would work off of the MAC address of the machine the database is installed on, and the instance name.

    Even that might have problems with VMs. I'm not sure about that, but it would need to be investigated.

    Which brings up the question of how you'll deal with VMs that can move from server to server, or exist on multiple servers at the same time, etc. Will that matter? Do you change the "installation ID" if someone copies a VM to another server? How will you even know if that happens?

    Since I don't know your business-case for this, all I can do is suggest questions on these points. You'll have to work out if they matter or not.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 11, 2010 at 1:22 pm

    #1117738

    Thanks for the response.

    I've considered this idea before and while this comes the closest to the solution, I am not sure if this fits for all the senarios. There are some installations out there already, that we do not have control over, so there is no easy way to assign GUID for them, without sending an database upgrade patch.

    Thanks SQLBOT for offering this idea.

    thanks,

    _UB

    SQLBOT (2/11/2010)

    create a table in master with a guid? ?

    create table dbo.whoami (

    sqlserverid uniqueidentifier

    ,location varchar(100)

    ,servername varchar(128)

    ,instancename varchar(128)

    )

    insert dbo.whoami

    select newid()

    ,'boston'

    ,cast(serverproperty('machinename') as varchar(128))

    ,COALESCE(cast(serverproperty('instancename')as varchar(128)),'Default')

    SqlTimes Blog on Sql Server

  • February 11, 2010 at 1:22 pm

    #1117739

    Edit: Sorry this is a duplicate post.

    SqlTimes Blog on Sql Server

  • February 11, 2010 at 1:24 pm

    #1117743

    Edit: Sorry again, this is duplicate too. I got an error when I first submitted my response. So I submitted more than once, until I got a success.

    SqlTimes Blog on Sql Server

  • February 11, 2010 at 1:29 pm

    #1117749

    Thanks GSquared. I'll keep those questions in mind, while I think about a solution.

    Be it VM or not, the ID must be unique. Even if the database is transferred from one machine to the other the ID must not change. Something like a GUID that is stored in the user database. Like what SQLBOT suggested.

    thanks,

    _Uday

    In that case, I'd be inclined towards creating a CLR function that would work off of the MAC address of the machine the database is installed on, and the instance name.

    Even that might have problems with VMs. I'm not sure about that, but it would need to be investigated.

    Which brings up the question of how you'll deal with VMs that can move from server to server, or exist on multiple servers at the same time, etc. Will that matter? Do you change the "installation ID" if someone copies a VM to another server? How will you even know if that happens?

    Since I don't know your business-case for this, all I can do is suggest questions on these points. You'll have to work out if they matter or not.

    SqlTimes Blog on Sql Server

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply