proper shutdown order for MSCS SQL cluster?

Hi,
We're having a SAN outage & I've noticed some **flakiness with regard to a
'clean' shutdown of our SQL 2000(active/active) & 2005(active/passive)
clusters.
**In the past when we've disconnected storage and had to cold boot all the
cluster nodes, we've had trouble starting the cluster back up. ( network
name not found in the cluster resource group and/or cluster services
wouldn't start)
Is there a recommended order to shut down & restart cluster resource groups
(including SQL+DTC) when system maintenanence need to occur?
Thanks,
Mike Bonvie
Shutdown:
Take the SQL resources offline and set the startup type to DISABLED on all
nodes
Stop the Cluster Service on all nodes and set the startup type to disabled.
Perform hardwarework as necessary.
Only boot one node at a time. If all nodes are down, us a staggered startup
sequence.
Enable and start cluster services on each node. Test moving the cluster
group.
Set SQL Service startup type to MANUAL.
Using the cluster admin tool, bring the diskm IP address, and network name
resources online. Test moving them across nodes.
Bring the SQL Services online using the cluster tool. Test moving groups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike Bonvie" <m@.d.net> wrote in message
news:e7WcCzMYHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We're having a SAN outage & I've noticed some **flakiness with regard to a
> 'clean' shutdown of our SQL 2000(active/active) & 2005(active/passive)
> clusters.
> **In the past when we've disconnected storage and had to cold boot all the
> cluster nodes, we've had trouble starting the cluster back up. ( network
> name not found in the cluster resource group and/or cluster services
> wouldn't start)
> Is there a recommended order to shut down & restart cluster resource
> groups (including SQL+DTC) when system maintenanence need to occur?
> Thanks,
> Mike Bonvie
|||Thank You Geoff,
Great information. Is this based on Microsoft documentation, or a combo of
experience/documentation?
If there's a KB or other resource, where can I get it?
Thanks again,
Mike Bonvie
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:u$vRT9MYHHA.4440@.TK2MSFTNGP03.phx.gbl...
> Shutdown:
> Take the SQL resources offline and set the startup type to DISABLED on all
> nodes
> Stop the Cluster Service on all nodes and set the startup type to
> disabled.
> Perform hardwarework as necessary.
> Only boot one node at a time. If all nodes are down, us a staggered
> startup sequence.
> Enable and start cluster services on each node. Test moving the cluster
> group.
> Set SQL Service startup type to MANUAL.
> Using the cluster admin tool, bring the diskm IP address, and network name
> resources online. Test moving them across nodes.
> Bring the SQL Services online using the cluster tool. Test moving groups.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Mike Bonvie" <m@.d.net> wrote in message
> news:e7WcCzMYHHA.1388@.TK2MSFTNGP05.phx.gbl...
>
|||This is a mix of best practices guidelines and experience. The staggered
startup is documented somewhere, but the other steps are stuff I have worked
out over the years. The DISABLED service ettings just makes sure the
resource won't attempt to come online accidentally.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike Bonvie" <m@.d.net> wrote in message
news:Oqu9AHNYHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Thank You Geoff,
> Great information. Is this based on Microsoft documentation, or a combo of
> experience/documentation?
> If there's a KB or other resource, where can I get it?
> Thanks again,
> Mike Bonvie
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:u$vRT9MYHHA.4440@.TK2MSFTNGP03.phx.gbl...
>
sql

promblems connecting to db across a network

Hello,

I've built an application using c#, visual studio 2005 and sql server 2000. The database is sitting on another machine on the network.

When I test the application from visual studio, it all works fine - no problems connecting to the database etc.

However, If I publish the website and move it to the same server as the database and set up the site and users through IIS etc, I get the following error

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

my connection string is

<connectionStrings>

<addname="cashinsConnectionString"connectionString="Data Source=Londevs;Initial Catalog=cashins;User ID=Cash; Password=Cash123;"

providerName="System.Data.SqlClient" />

</connectionStrings>

Anyone have any ideas why this is happening?

Thanks

fogofogo:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE

You are trying to connect to the database with the above account which doesn't have the necessary permissions to connect.

|||

Thanks for your reply.

So do I need to set these permissions on the database or on the server where the database is sitting?

|||

actually - this is strange. I've just put together a smaller application with the EXACT same connection string as above, and it worked fine.

how could that be??

|||

It's most likely a problem with your web.config file (possibly it's doing some impersonation or windows authentication) rather than just the connection string. Check both web.config files to see what you are doing differently.

|||

Thanks.

I compared them both using winmerge and they are both the same.

here my config file

12<!--3 Note: As an alternative to hand editing this file you can use the4 web admin tool to configure settings for your application. Use5 the Website->Asp.Net Configuration option in Visual Studio.6 A full list of settings and comments can be found in7 machine.config.comments usually located in8 \Windows\Microsoft.Net\Framework\v2.x\Config9-->10<configuration>11<appSettings/>12<connectionStrings>1314 <add name="cashinsConnectionString" connectionString="Data Source=servername;Initial Catalog=databasename;User ID=Cash; Password=Cash123;" providerName="System.Data.SqlClient"/>15 </connectionStrings>16 <system.web>17<!--18 Set compilation debug="true" to insert debugging19 symbols into the compiled page. Because this20 affects performance, set this value to true only21 during development.22 -->23<compilation debug="true"/>24<!--25 The <authentication> section enables configuration26 of the security authentication mode used by27 ASP.NET to identify an incoming user.28 -->29<authentication mode="Forms"/>3031<!--32 The <customErrors> section enables configuration33 of what to do if/when an unhandled error occurs34 during the execution of a request. Specifically,35 it enables developers to configure html error pages36 to be displayed in place of a error stack trace.3738 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">39 <error statusCode="403" redirect="NoAccess.htm" />40 <error statusCode="404" redirect="FileNotFound.htm" />41 </customErrors>42 -->43</system.web>44</configuration>

all looks ok right?

I also pass the connection through...

1private static String connstring = ConfigurationManager.ConnectionStrings["cashinsConnectionString"].ConnectionString;2private static SqlConnection conn =new SqlConnection(connstring);

so I can access the connection from a .cs file that holds my classes and methods etc. Could that be the source of the problem?

Thanks

|||

Hi,

From your description, it seems that you application can connect to your database while lunched from Visual Studio but failed when lunched in IIS, right?

You can try the following steps to check if "AUTHORITY\NETWORK SERVICE" has been permitted to visit the database.

1. Open your Enterprise Manager and open the security node in your local sql server.
2. Click on "Logins", try to see if "AUTHORITY\NETWORK SERVICE" appeared in that list, if not, create the user and assign the corresponding server roles.

Thanks.

|||

Cool! thanks

Programmatically Accessing an SQLDataSource with a "SELECT COUNT(*)" query.

I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be

Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView)

Dim row As DataRow = datastuff.Table.Rows(0)
Dim installtype As Integer = row("InstallMethod")
Dim install As String = row("Install").ToString
Dim notes As String = row("Notes").ToString

The above only works on a single row, of course. If I needed more, I know I can loop it.

The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"

My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query.

Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim count As Integer = row("rowcnt")

The query here is "SELECT COUNT(*) as rowcnt FROM Software"

The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value.

Where am I going wrong here?

The following should work.

Dim datastuffAs System.Data.DataView =CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim drAs System.Data.DataRow = datastuff .Table.Rows(0)

Dim mycountAsString = Convert.ToInt32(dr("rowcnt")).ToString()

'Label1.Text = mycount

|||

Hi there,

Aren't you getting the row count from your SELECT COUNT query (1 row obviously)? Instead of getting the result value from that query?

gonzzas

|||

It's very similar to what I've tested out, but that exact code will show that mycount = "1" instead of the actual value.

What is interesting is the GridView control I set up on the test page is outputting the correct result.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="srcSoftwareUsage" Width="527px">
<Columns>
<asp:BoundField DataField="rowcnt" HeaderText="rowcnt" ReadOnly="True" SortExpression="rowcnt" />
</Columns>
</asp:GridView>

Now, it's obviously accessing the column named rowcnt. I can debug my code and manually look at the column values in DataView.Table.Rows(0) and it shows the value 1 and nothing more.

|||

Yes, I can use either code to get the correct count from my query. What is your SqlDataSource code?

Here is what I tested:

Dim dvAs System.Data.DataView =CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim rowAs System.Data.DataRow = dv.Table.Rows(0)

' For Each row As System.Data.DataRow In dv.Table.Rows

Dim mycountAsString = Convert.ToInt32(row("rowcnt")).ToString()

Label2.Text = mycount

' Next

|||

Murphy's Law probably applies as I didn't give you thecomplete story - I naively thought this part shouldn't matter as it'sthe table output is identical.

It appears to be my sql query.

I'm not actually looking for the count of rows in the Software table, but I'm looking for the count of times a particular row in Software is referenced by 2 other tables - RoleSoft and TeamSoft

With my simple query above, both the code and GridView worked. With this one - the GridView works, the code doesn't.

SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = @.Id) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = @.Id)) AS derivedtbl_1

I thought it was a moot point as the table output appears identical from each query. Obviously I'm wrong. I'm imagining the derivedtbl_1 is probably where I'm getting bogus data in the code.

1> SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = 2) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = 2))
AS derivedtbl_1
2> go
rowcnt
----
3

(1 rows affected)
1> SELECT COUNT(*) as rowcnt FROM Software
2> go
rowcnt
----
8

(1 rows affected)

|||

Bah. I figured it out. It wasn't even the SQL statement. I had updated the @.Id parameter in the srcSoftwareUsage_Selecting event handler and I misused a global variable. It kept setting @.Id to 1 and the count for that Id was always 1.

Now I feel stupid for wasting your time and mine on this. Thanks for the help, though.

Program icons in "Start menu"

Hello,

I′ve made a mistake on my part. I have deleted my complete program icons for "Microsoft SQL Server" in my start menu.

Is there a simple way to just renew all the icons in my start menu without reinstalling the complete sql server.

Thank you very much for any tips.

Hi,

do you have a colleague with SQL Server installed? You might copy his structure of the SQL Server start menu over to your machine and check if the pathes of the links are correct.

Otherwise you will have to browse the Microsoft SQL Server directory for the executables and create short cuts in the start menu manually.

You might also try if a repair of your installation brings the icons back.

--
SvenC