Active - Active clustering -Second instance

Trying to install a second named instance of SQL Server 2005 on a
Windows Server 2003 Enterprise two node cluster. I am installing on the
node which the cluster group is currently on. The first named instance I
installed went in like a champ - I have a Cluster Resource group, a SQL
Server(Instance) Group (contains disks, services, ip address, net name, etc)
and a SQL Server (NewInstance) resource group which contains only the shared
disks for the second instance.
Run through setup - everything goes as planned, until the actual
installation is to take place, and then it fails on the 'Remote' server,
because the Scheduled task can't be run - I get this in the Scheduled Task
log file:
"SQL Server Remote Setup .job" (setup.exe) 10/4/2006 3:27:10 PM ** ERROR **
Unable to start task.
The specific error is:
0x80070005: Access is denied.
Try using the Task page Browse button to locate the application.
This seems to indicate that the remote machine cannot access the install
files on the local machine - Has anyone experienced this, and how did you
solve it ?
thanks !
RJ wrote:
> Trying to install a second named instance of SQL Server 2005 on a
> Windows Server 2003 Enterprise two node cluster. I am installing on the
> node which the cluster group is currently on. The first named instance I
> installed went in like a champ - I have a Cluster Resource group, a SQL
> Server(Instance) Group (contains disks, services, ip address, net name, etc)
> and a SQL Server (NewInstance) resource group which contains only the shared
> disks for the second instance.
> Run through setup - everything goes as planned, until the actual
> installation is to take place, and then it fails on the 'Remote' server,
> because the Scheduled task can't be run - I get this in the Scheduled Task
> log file:
> "SQL Server Remote Setup .job" (setup.exe) 10/4/2006 3:27:10 PM ** ERROR **
> Unable to start task.
> The specific error is:
> 0x80070005: Access is denied.
> Try using the Task page Browse button to locate the application.
> This seems to indicate that the remote machine cannot access the install
> files on the local machine - Has anyone experienced this, and how did you
> solve it ?
> thanks !
I am not sure about this specific problem, however from my experience
it is quite easier to first install SQL Server on both machines, then
create the cluster.
Other suggestions while you are in the setup stage:
-Make sure you have different ports and names for your instances if you
want to have them both running on the same server in a failover
scenario.
-If possible, try using the default instance on one of your servers.
Some software can't connect to named instances, like Microsoft CRM.
-Be careful about SSIS, which can be painful in an active-active
cluster. The easier way to go it is to use Filesystem packages, and
store the packages on a cluster resource.
Regards,
lucm
|||Actually, this was mysteriously resolved -
I started looking through the installation log files, and decided I wanted a
'clean' set of files for analysis. I deleted ALL the files in the c:\program
files\Microsoft SQL Server\90\Setup Bootstrap\LOG folder, and proceeded to
try another doomed installation. To my complete amazement, it succeeded.
So - I don't know that I learned anything here... but it got me working.
"lucm@.iqato.com" wrote:

> RJ wrote:
> I am not sure about this specific problem, however from my experience
> it is quite easier to first install SQL Server on both machines, then
> create the cluster.
> Other suggestions while you are in the setup stage:
> -Make sure you have different ports and names for your instances if you
> want to have them both running on the same server in a failover
> scenario.
> -If possible, try using the default instance on one of your servers.
> Some software can't connect to named instances, like Microsoft CRM.
> -Be careful about SSIS, which can be painful in an active-active
> cluster. The easier way to go it is to use Filesystem packages, and
> store the packages on a cluster resource.
> Regards,
> lucm
>
|||Take a look at this KB article see whether it's relevant:
http://support.microsoft.com/?id=910851
Linchi
"RJ" wrote:

> Trying to install a second named instance of SQL Server 2005 on a
> Windows Server 2003 Enterprise two node cluster. I am installing on the
> node which the cluster group is currently on. The first named instance I
> installed went in like a champ - I have a Cluster Resource group, a SQL
> Server(Instance) Group (contains disks, services, ip address, net name, etc)
> and a SQL Server (NewInstance) resource group which contains only the shared
> disks for the second instance.
> Run through setup - everything goes as planned, until the actual
> installation is to take place, and then it fails on the 'Remote' server,
> because the Scheduled task can't be run - I get this in the Scheduled Task
> log file:
> "SQL Server Remote Setup .job" (setup.exe) 10/4/2006 3:27:10 PM ** ERROR **
> Unable to start task.
> The specific error is:
> 0x80070005: Access is denied.
> Try using the Task page Browse button to locate the application.
> This seems to indicate that the remote machine cannot access the install
> files on the local machine - Has anyone experienced this, and how did you
> solve it ?
> thanks !

Activating a Second Server in an NLB Cluster

Hi,
I'm having problems activating a second server that is participating in
an NLB cluster. The common response I see here is to run rsactivate
from the first node using the -m switch to activate the second node.
The problem with this is that when in an NLB cluster you cannot even
ping the second server so there is no way to do it this way. Is there
an alternative way of activating it. I have tried doing the rskeymgt
-d on the servers but it seems like whenever I do that only one server
will pickup (or perhaps create) the key so the other server will go
back into the "not activated" mode. Is there a way to perhaps
"extract" the key from one server and "install" it on the second
server?
Thanks in advance!
BillOK...I saw how I could extract a key to a file. I then applied the
key to the second server, but that didn't really seem to change
anything. I am still getting a "not activated" message when I bring up
report manager. Any thoughts? Am I going to have to uninstall NLB,
then do the install and then redo NLB?
Thanks,
Bill

accumulating the result of a snapshot replication filtered by date

hi all,

Im running a snapshot replication on table.

first, i gonna copy the entire table with a regional filter other than date

second i'm going to replicate a days worth of data by using both the regional filter and date filter (getdate) to capture a days wordth of data.

the publication are working fine.

my question is how am i going to accumulated the result of

the filtered snaphot replication in a single table in my db.

many thanks,

joey

Hi Joey,

You can configure your article to use 'delete' as the pre-creation command which corresponds to the 'Action if name is in use - Delete data. If article has a row filter delete only data that matches the filter' option on the article properties page. As the UI description implies, this will likely give you what you need although you need to watch out for the following:

1)

Using getdate() (and dateadd(getdate(),) most likely) to define a time range in your article filter may (or will) produce surprising result as the filter will be evaluated at different point in time as well as on different machines that don't have perfectly synchronized clocks. In the context of snapshot replication, the filter will be first evaluated at the publisher for the date range of data to be bcp'd out and then it is evaluated at a later time on the subscriber for the range of data that needs to be removed before the new data comes in. Since there is inevitably a time delay between when the snapshot is generated and when it gets delivered to the subscriber, the two date ranges will not coincide perfectly as you would hope.

Now, since time goes in only one direction, the worst that can happen to you is that the distribution agent will try to bcp in data that you already have at the subscriber (but not removed by the pre-creation command) at the lower end of your date range. This is unfortunately something that is not very well handled today as the distribution agent will likely fail with a primary key violation error while giving you very few options for recovery other than manually delete the overlapping data. However, if there are well-defined date gaps (say, off business hours) in your data, you can make the low end of your date range coincide with the gap. Note thta this can be tricky to maintain as the snapshot agent schedule and the date gap may change in the future.

From my perspective, the only robust way to achieve what you want is to keep track of the latest timestamp of the last snapshot iteration and use that to define an explicit date range you need in your filter before starting the next snapshot iteration. Here is a rough outline on how you may achieve this:

i. Call getdate() to get the upper bound of new date range

ii. Use the upper bound of the previous snapshot iteration and the new upper bound to define a new filter clause with the date values hardcoded and pass that into sp_articleview to generate a new synchronization view that the snapshot agent will use to bcp out data.

iii. Wait until the new snapshot is distributed to the subscriber and then update latest timestamp of the last snapshot at the publisher.

The last step is actually important because you may end up with a gap at the subscriber if your lower bound is defined to be too far ahead of what the subscriber has actually received. As such, it may be worthwhile to generalize the procedure above by keeping track of the upper bounds of the last couple of snapshot iterations and choose a more conservative lower bound to be further back in time (the pre-creation command should take care of removing any overlapping data).

At this point, you are probably wondering why we make what is arguably a common scenario so difficult, and I would say that you are absolutely right in thinking so. But given that things are what they are today, I can only encourage you to log a DCR at http://lab.msdn.microsoft.com/productfeedback/Default.aspx so we can allocate resources to make data archival scenarios simpler using snapshot replication.

2) (Well, I am not quite done yet :)

Since the subscriber data is most likely not empty when the distribution agent bcp data in, you will most likely not get the performance benefits of minimally-logged bulk-load. Because of this, you may need to be careful that you are not moving too much data in each snapshot iteration.

Hope that helps.

-Raymond

Accessing SQL2005 as a Second Instance to SQL2000

I'm having trouble accessing SQL2005 Standard Edition as a second
instance of SQL Server where the first instance is SQL 2000 Enterprise
Edition. I installed SQL 2005 as a named instance "SQL2005". The
server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in
Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)
services there. I went into SQL Server Configuration manager and
disabled the named pipes protocol and only have TCP/IP and by default
Shared Memory enabled to rule out that named pipes is causing the
problem. In SQL Server Configuration Manager I created an alias
"computername$SQL2005" and was able to connect to database engine
"computername\SQL2005" through SQL Server Management Studio WHILE ON
THAT BOX.
But when trying to access the 2005 instance from another server
(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine I
disabled the named pipes protocol for both "SQL Native Client
Configuration (32-bit)" and "SQL Native Client Configuration". Under
32-bit I created the alias "machinename$SQL2005" to point to the IP
address of the 32-bit machine. In SQL Server Management Studio when
trying to connect to the database engine "machinename$SQL2005" it
defaults to the SQL 2000 instance. How do I access the SQL2005
instance from a remote server?

Any help would be appreciated.

MikeThis question seems to crop up a lot; I'm surprised that Microsoft
didn't make a bigger deal about it. By default, SQL Server 2005 does
not allow remote access; you have to turn it on.

Use the Surface Area Configuration tool for SQL Server 2005; under
database engine, select Remote Connections and make sure that local and
remote connections are selected.

HTH,
Stu

Mike wrote:

Quote:

Originally Posted by

I'm having trouble accessing SQL2005 Standard Edition as a second
instance of SQL Server where the first instance is SQL 2000 Enterprise
Edition. I installed SQL 2005 as a named instance "SQL2005". The
server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in
Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)
services there. I went into SQL Server Configuration manager and
disabled the named pipes protocol and only have TCP/IP and by default
Shared Memory enabled to rule out that named pipes is causing the
problem. In SQL Server Configuration Manager I created an alias
"computername$SQL2005" and was able to connect to database engine
"computername\SQL2005" through SQL Server Management Studio WHILE ON
THAT BOX.
But when trying to access the 2005 instance from another server
(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine I
disabled the named pipes protocol for both "SQL Native Client
Configuration (32-bit)" and "SQL Native Client Configuration". Under
32-bit I created the alias "machinename$SQL2005" to point to the IP
address of the 32-bit machine. In SQL Server Management Studio when
trying to connect to the database engine "machinename$SQL2005" it
defaults to the SQL 2000 instance. How do I access the SQL2005
instance from a remote server?
>
Any help would be appreciated.
>
Mike

|||Thanks for your response Stu, After checking SQL Server 2005 Surface
Area Configuration it looks like the option for "Local and Remote
Connections" was already selected. I checked under "Database Engine"
under both "View by Instance" and "View by Component" and it seems the
same. I even clicked on "Local connections only" then back to "Local
and Remote Connections" just to make sure the setting was recognized
when hitting "Apply". Then restarted SQL Server (2005) service. Still
unable to access this instance from the other server. Do I need to use
another tcp/ip port for the second instance? I remember reading this
somewhere, but when I tried to use port 1435 because UDP port 1434 is
supposedly disabled in SQL 2005, I still cannot connect.

When I try connecting remotely I'm still getting the SQL 2000 instance.
Any other ideas?

Thanks,

Mike

Stu wrote:

Quote:

Originally Posted by

This question seems to crop up a lot; I'm surprised that Microsoft
didn't make a bigger deal about it. By default, SQL Server 2005 does
not allow remote access; you have to turn it on.
>
Use the Surface Area Configuration tool for SQL Server 2005; under
database engine, select Remote Connections and make sure that local and
remote connections are selected.
>
HTH,
Stu
>
Mike wrote:

Quote:

Originally Posted by

I'm having trouble accessing SQL2005 Standard Edition as a second
instance of SQL Server where the first instance is SQL 2000 Enterprise
Edition. I installed SQL 2005 as a named instance "SQL2005". The
server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in
Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)
services there. I went into SQL Server Configuration manager and
disabled the named pipes protocol and only have TCP/IP and by default
Shared Memory enabled to rule out that named pipes is causing the
problem. In SQL Server Configuration Manager I created an alias
"computername$SQL2005" and was able to connect to database engine
"computername\SQL2005" through SQL Server Management Studio WHILE ON
THAT BOX.
But when trying to access the 2005 instance from another server
(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine I
disabled the named pipes protocol for both "SQL Native Client
Configuration (32-bit)" and "SQL Native Client Configuration". Under
32-bit I created the alias "machinename$SQL2005" to point to the IP
address of the 32-bit machine. In SQL Server Management Studio when
trying to connect to the database engine "machinename$SQL2005" it
defaults to the SQL 2000 instance. How do I access the SQL2005
instance from a remote server?

Any help would be appreciated.

Mike

|||Mike (mjorlando@.campsystems.com) writes:

Quote:

Originally Posted by

Thanks for your response Stu, After checking SQL Server 2005 Surface
Area Configuration it looks like the option for "Local and Remote
Connections" was already selected. I checked under "Database Engine"
under both "View by Instance" and "View by Component" and it seems the
same. I even clicked on "Local connections only" then back to "Local
and Remote Connections" just to make sure the setting was recognized
when hitting "Apply". Then restarted SQL Server (2005) service. Still
unable to access this instance from the other server. Do I need to use
another tcp/ip port for the second instance? I remember reading this
somewhere, but when I tried to use port 1435 because UDP port 1434 is
supposedly disabled in SQL 2005, I still cannot connect.

Have you checked that the SQL Browser service is running? You control it
from the SQL Configuration Management tool.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, the SQL Browser service is running. I restarted it just to make
sure. Has there been updates to BOL since May 2006? That is the date
of my SQL 2005 installation. I will download the latest, thanks,
Mike
Erland Sommarskog wrote:

Quote:

Originally Posted by

Mike (mjorlando@.campsystems.com) writes:

Quote:

Originally Posted by

Thanks for your response Stu, After checking SQL Server 2005 Surface
Area Configuration it looks like the option for "Local and Remote
Connections" was already selected. I checked under "Database Engine"
under both "View by Instance" and "View by Component" and it seems the
same. I even clicked on "Local connections only" then back to "Local
and Remote Connections" just to make sure the setting was recognized
when hitting "Apply". Then restarted SQL Server (2005) service. Still
unable to access this instance from the other server. Do I need to use
another tcp/ip port for the second instance? I remember reading this
somewhere, but when I tried to use port 1435 because UDP port 1434 is
supposedly disabled in SQL 2005, I still cannot connect.

>
Have you checked that the SQL Browser service is running? You control it
from the SQL Configuration Management tool.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mike (mjorlando@.campsystems.com) writes:

Quote:

Originally Posted by

Yes, the SQL Browser service is running. I restarted it just to make
sure. Has there been updates to BOL since May 2006? That is the date
of my SQL 2005 installation. I will download the latest, thanks,

Yes, there is a new version. (They come out once a quarter.) URL below.

I went back to your original post, and it appears that you have defined
alias which points to the IP Address on the machine with SQL 2000 and
SQL 2005 on. But it is not clear that you specifiec the instance name
in the alias definition. If you only use the IP address you will end up
at the default instance, which I understand is running SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Accessing Second Record Within Query

Hello,
I'm attempting to use a query to add up the charges in a record from a
table and two corresponding records in another table and then return a
result if there is a difference between the known total and these
calculations. My query below works fine for adding up charges from a
record in the H_MAIN table and one record from the H_Main_Extra table.
What would I add into this query to access the second corresponding
record in
the H_Main_Extra table?
Thanks in advance for any advice.
Jon
SELECT [TotalSub].Tif, [TotalSub].c1, [TotalSub].c2, [TotalSub].c3,
[TotalSub].c4, [TotalSub].CalcTotal, [TotalSub].ExtractedTotal,
[TotalSub].AmtDiff FROM (SELECT H_MAIN.Tif, H_MAIN.Changes,
IIf(IsNull(H_MAIN.[24f_Charges1]),"0",H_MAIN.[24f_Charges1]) AS c1,
IIf(IsNull(H_MAIN.[24f_Charges2]),"0",H_MAIN.[24f_Charges2]) AS c2,
IIf(IsNull(H_MAIN_Extra.[24f_Charges1]),"0",H_MAIN_Extra.[24f_Charges1])
AS c3,
IIf(IsNull(H_MAIN_Extra.[24f_Charges2]),"0",H_MAIN_Extra.[24f_Charges2])
AS c4,Val([c1])+Val([c2])+Val([c3])+Val([c4
]) AS CalcTotal,
IIf(IsNull(H_MAIN.[28_Total_Charges]),"0",Val(H_MAIN.[28_Total_Charges]))
AS ExtractedTotal, Abs([CalcTotal]-[ExtractedTotal]) AS AmtDiff
FROM H_MAIN LEFT JOIN H_Main_Extra ON H_MAIN.Id =
H_Main_Extra.ParentId) AS TotalSub WHERE ((([TotalSub].[AmtDiff])>0));On 5 Dec 2005 12:12:51 -0800, jon.tjemsland@.gmail.com wrote:

>Hello,
>I'm attempting to use a query to add up the charges in a record from a
>table and two corresponding records in another table and then return a
>result if there is a difference between the known total and these
>calculations. My query below works fine for adding up charges from a
>record in the H_MAIN table and one record from the H_Main_Extra table.
>What would I add into this query to access the second corresponding
>record in
>the H_Main_Extra table?
>Thanks in advance for any advice.
Hi Jon,
The code you posted uses the IIf function. This function does not exist
in SQL Server. I suspect that you're using Access. If I'm right, then
you'll probably get better help in an Access group. (This group is for
SQL Server).
Your question is also not completely clear. You say you want to add a
"second corresponding record", but you don't indicate how I would know
which rows are "corresponding". And if there are more than 2, then how
to determine which are first and seceond?
If the IIf's were an error and this question does concern SQL Server,
then please check www.aspfaq.com/5006 to get more information on the
details you need to include in your post to enable us to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accessing Second Record Set

hello all,
i had two stored procedures,where the 1st SP is calling 2nd SP(which returns 2 record sets).
how can i access the second recordset from the 1st SP
it would a great help if anyone help me out!!
thanx in advance,
ravi.WHY? I mean, you could rig up some hackneyed process that loads the data into a temp table and parses out the recordset you want, or whatever, but there is just never a good reason for building applications this way. One stored procedure should return one recordset. One function should return one recordset.|||I agree with Blindman 100% on this issue. Stored procedures can return more than one result set. This is to allow for nested procedures that each return their own result sets (like sp_help does). I can't think of any reason that a procedure should be designed to intentionally return more than one result set, other than as a user convenience "wrapper" for a group of other procedures.

-PatP