Quick Question. Could be silly

Question

Post reply

Quick Question. Could be silly

_UB

SSCrazy

Points: 2987
More actions
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

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

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

Richard M. SSCertifiable Points: 7288 More actions · Answer 1

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]

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 2

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

Richard M. SSCertifiable Points: 7288 More actions · Answer 3

Richard M.

SSCertifiable

Points: 7288

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]

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 4

Elliott Whitlow

SSC Guru

Points: 102296

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

GSquared SSC Guru Points: 260824 More actions · Answer 5

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

_UB SSCrazy Points: 2987 More actions · Answer 6

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

_UB SSCrazy Points: 2987 More actions · Answer 7

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

_UB SSCrazy Points: 2987 More actions · Answer 8

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

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 9

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

GSquared SSC Guru Points: 260824 More actions · Answer 10

_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

_UB SSCrazy Points: 2987 More actions · Answer 11

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

_UB SSCrazy Points: 2987 More actions · Answer 12

_UB

SSCrazy

Points: 2987

February 11, 2010 at 1:22 pm

#1117739

Edit: Sorry this is a duplicate post.

SqlTimes Blog on Sql Server

_UB SSCrazy Points: 2987 More actions · Answer 13

_UB

SSCrazy

Points: 2987

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

_UB SSCrazy Points: 2987 More actions · Answer 14

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