Global temp table in a cluster

Question

Global temp table in a cluster

Murray Spokes

SSC Journeyman

Points: 75
More actions
May 16, 2006 at 10:33 pm

#93914

I need to implement some user counting in an n-tier app using sql as the back end, I considered using a permanent table however the end user could set their own trigger to adjust the user count . Does a global temp table persist over all instances of an sql server in a cluster?

Viewing 4 posts - 1 through 4 (of 4 total)

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

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Each instance of SQL Server has its own tempdb database, which is where temp tables are created.

If you use the single '#' in creating your temp table, then it's context is with the process that created it. Other processes will not be able to access this temp table. And when the process ends, the temp table is also dropped from tempdb.

When you use the '##' to create your temp table, then it's context is said to be 'global'. That is, it can be referenced by all processes created on that particular server. The table will also persist until such times as it is cleared from tempdb. If you try to reference this temp table from another instance of SQL Server by qualifying the temp table name, you will find that a message is displayed.

Murray Spokes SSC Journeyman Points: 75 More actions · Answer 2

Thanks for that Paul however I am a bit confused, probably due to my lack of understanding of clustering. If you have a cluster to allow load balancing between sql server instances then (assumption here) is it not upto the cluster to syncronsise all the databases in the cluster, would the temp_db be included in the sync and therefore my ##temp table? Have I got this right or is my understanding of load balancing incorrect.

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

This article from Brian Knight talks a little more about clustering:

http://www.sqlservercentral.com/columnists/bknight/clustering_a_sql_server_machine__2.asp

If you are installing a 2-node cluster, you would install a default instance of SQL Server on the first node and an INSTANCE of SQL Server (that is a separate SQL Server is installed) on the second node. Therefore, the behaviour of tempdb should be no different to what I've already said, irrespective of whether it's an active-active or active-passive cluster setup.