How to change SQL Server parameters in a clustered environment when SQL Server is not online

INTRODUCTION

When you use Microsoft SQL Server 2008 Configuration Manager, SQL Server 2005 Configuration Manager, SQL Server 2000 Enterprise Manager, or SQL Server 2000 Setup to change SQL Server parameters in a clustered environment, you have to make changes on the active node while the SQL Server cluster resource is online. If SQL Server is not online, you have to bring SQL Server online first. However, in some circumstances, you may be unable to bring SQL Server online.

This article describes how to change SQL Server parameters in a clustered environment when SQL Server is not online or when you cannot bring SQL Server online.

Back to the top

MORE INFORMATION

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756Â (http://kbalertz.com/Feedback.aspx?kbNumber=322756/ ) How to back up and restore the registry in Windows

To change SQL Server parameters in a clustered environment when SQL Server is not online, use one of the following methods.

Back to the top

Method 1

Note We recommend that you try to use this method first.

1. Click Start, click Run, type regedit, and then click OK.
2. Locate the quorum disk. To do this, follow these steps:

1. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Quorum

2. The Path entry contains the path of the quorum disk. For example, the Path entry contains the following path:

<QuorumDrive>:\MSCS

3. Locate the GUID of the SQL Server cluster resource. To do this, follow these steps:

. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources

a. Examine the Name column of the registry entries.

Note Several registry entries include "GUID" in the name of the entry.

2. For the default instance, locate the SQL Server cluster resource that includes "SQL Server" in the Name column.
   
   For named instances, locate the SQL Server cluster resources that include "SQL Server (<InstanceName>)" in the Name column.
3. Locate the checkpoint file name. To do this, follow these steps:

. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources\{GUID}\RegSync

a. In the details pane, view the checkpoint registry hives and the corresponding numbers that resemble the following:

For the default instance
00000004 SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLSERVER

For a named instance
00000004 SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLSERVER

Note For a named instance, X corresponds to the instance ID.

The number is the checkpoint file name. In this example, the checkpoint file name is 00000004.cpt.

5. In Registry Editor, click HKEY_LOCAL_MACHINE.
6. On the File menu, click Load Hive.
7. In the <QuorumDrive>:\<GUID> folder, locate the checkpoint file that you found in step 4.
8. In the Key Name box, type 1, and then click OK.
9. Locate the following registry key to correct the invalid checkpoint registry key value:

HKEY_LOCAL_MACHINE\1\<YourRegistryKey>

Note The following examples correct the MSSQLSERVER checkpoint registry key:

• Example 1
  To correct the invalid path of the Master.mdf file, follow these steps:

a. Locate the following registry key:

HKEY_LOCAL_MACHINE\1\Parameters

b. Correct the SQLArg0 key.

• Example 2
  To disable the incorrectly enabled VIA protocol, follow these steps:

0. Locate the following registry key:

HKEY_LOCAL_MACHINE\1\SuperSocketNetLib\Via

a. Change the value of the Enabled entry from 1 to 0.

10. After you correct the registry key, click HKEY_LOCAL_MACHINE\1, click the File menu, and then click Unload Hive.

Note After you follow these steps, this checkpoint is fixed and is replicated to the specific node automatically during failover. You can bring the instance of SQL Server online.

Back to the top

Method 2

Note Do not perform SQL cluster group failover between step 1 and step 3.

1. At a command prompt, run one of the following commands to disable the cluster checkpoint for the specific registry subkey:
• For an instance of SQL Server 2008, run the following command:

cluster . resource "SQL Server (<InstanceName>)" /removecheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

Note In this command, MSSQL.x is a placeholder for the instance ID of the instance of SQL Server. You can determine the corresponding value for the system from the value of the MSSQLSERVER registry entry in the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Names>\SQL\

• For an instance of SQL Server 2005, run the following command:

cluster res "SQL Server (<InstanceName>)" /removecheck: "Software\Microsoft\Microsoft SQL Server\ MSSQL.x \MSSQLSERVER"

• For the default instance of SQL Server 2000, run the following commands:

cluster res "SQL Server" /removecheck: "Software\Microsoft\MSSQLServer\MSSQLSERVER"

cluster res "SQL Server" /removecheck: "Software\Microsoft\MSSQLServer\Cluster"

Note You have to run the second command only when you add or remove one or more IP addresses on which SQL Server 2000 listens.

• For a named instance of SQL Server 2000, run the following commands:

cluster res "SQL Server (<InstanceName>)" /removecheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\MSSQLSERVER"

cluster res "SQL Server (<InstanceName>)" /removecheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\Cluster"

Note In these commands, Instance_Name is a placeholder for the name of the instance of SQL Server 2000. Additionally, you have to run the second command only when you add or remove one or more IP addresses on which SQL Server 2000 listens.

2. Change the parameter for the clustered instance of SQL Server on all nodes.
3. At a command prompt, run one of the following commands to enable the cluster checkpoint for the specific registry subkey:
• For an instance of SQL Server 2008, run the following command:

cluster . resource "SQL Server (<InstanceName>)" /addcheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

• For an instance of SQL Server 2005, run the following command:

cluster res "SQL Server (<InstanceName>)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

• For the default instance of SQL Server 2000, run the following commands:

cluster res "SQL Server" /addcheck: "Software\Microsoft\MSSQLServer\MSSQLSERVER"

cluster res "SQL Server" /addcheck: "Software\Microsoft\MSSQLServer\Cluster"

• For a named instance of SQL Server 2000, run the following commands:

cluster res "SQL Server (<InstanceName>)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\MSSQLSERVER"

cluster res "SQL Server (<InstanceName>)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\Cluster"

4. Note The resource name "SQL Server (<InstanceName>)" may be different in your case. To confirm the resource name, start Cluster Administrator, click SQL Group, locate the SQL Server resource properties, and then determine the exact name of the resource.
5. Bring the instance of SQL Server online.

Back to the top

REFERENCES

For more information about how to configure server startup options in SQL Server Configuration Manager, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms345416.aspx (http://msdn2.microsoft.com/en-us/library/ms345416.aspx)

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

912397Â (http://kbalertz.com/Feedback.aspx?kbNumber=912397/ ) The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

244980Â (http://kbalertz.com/Feedback.aspx?kbNumber=244980/ ) How to change the network IP addresses of SQL Server failover cluster instances

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

Things I Wish I Had Known

Things I Wish I Had Known
By Tim Mitchell in Tim Mitchell | 02-11-2009 10:46 PM | Categories: Filed under: Career

It's OK to make mistakes. It's also OK to admit it.
There are two types of people in this world: those who make mistakes and fess up, and those that make mistakes and try to cover it up. Everyone - everyone - makes mistakes in their jobs, and in life in general. While there are some high-profile mistakes that are notoriously noteworthy (airline disasters, medical errors), the vast majority of us are permitted, and even expected, to make a few errors here an there. As long as you can learn from your errors and not continue to make the same mistakes, it can be filed under the "Valuable Lessons Learned" heading. Further, those whom you work with and for will have more respect for you if you admit your errors up front, especially when you deliver a plan for resolution.
Technical skills are not enough
When I started out, I had my mind set on simply learning a craft, fine-tuning my skills to become one of the best in the field, and keeping my head down and working hard for the next 40 years. While it is important to grow and learn hard skills, today's economy is not friendly to the stereotypical technical geek working in a dark basement and slinging code (or monitoring logs, or building desktops, or whatever). To be successful, you've got to break out of your technical world every now and again and interface with nontechnical people. Spend half a day shadowing one of your end users to see how the systems you build/support help them to do their jobs. Spend time with senior management and executives to find out what big-picture goals they have, and how you help get the organization there. Have coffee with someone unfamiliar with technology and learn how you can ease them into the digital age. Most importantly, get to know the overall goals of your organization/clients/customers - you'll be far more successful in the long run.
You are responsible for your own career development
Technical careers require constant learning. Training, college, and conferences/trade shows are great ways to learn and network, but many companies can't or won't fork over the cash for these career development events. The bottom line is, it's up to you to take charge of your career. A few thousand dollars for conference fees can be painful, but it could be argued that you'll easily recoup that investment over the course of your career. For those who truly are budget strapped, there are tons of free career resources; user group events, online references (even videos!), libraries, and volunteer opportunities are all cost conscious ways to build your network and skillset.
Don't try to be an expert in everything
There are generalists, and there are specialists; nobody can be an expert in all things technical. Find something that you enjoy doing (that statement alone should be a bullet point), and become an expert in that thing. You don't have to specialize to the point that you're a niche player, but you can limit your scope such that you can be known as an authority in your chosen area.
Don't take things too seriously
I almost didn't type this last one for fear that I would portray myself as having a lackadaisical attitude toward my career; nothing could be further from the truth. We're all human (see #1 above) and are limited by a number of factors, including emotions, limited energy, family commitments, and natural abilities. Don't be a stickler for absolute perfection; accept that some things are part of live and unchangeable. When obstacles block your path, don't freak out or become the voice of negativity; take a breath, smile, and know that, if it was easy, everybody would be doing it and our skills would be far less valuable. Address problems or deadlines with a sense of urgency, but don't let your commitments consume you to the point that you spend all of your time working.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

Java MSN Live Messenger

The Java MSN Messenger Library is really something that quite interesting, I discovered it while I was trying to get a java based messenger client.
I managed to get a client sample and ran it and turned out to be quite powerful...
With JML, you can do things like creating your own msn robot, implementing group chat etc.

http://jml.blathersource.org/

MOM 2005 & SCCM 2007

We are testing MOM 2005 at this time and hopefully will move to SCCM in the following months, the MOM 2005 server had been in place for a long time but haven’t been properly configured.

It took me almost 2 days to go through the documentation and eventually got it sort of working.

I have to say through, as part of Microsoft’s MOF model and closely correlated to ITIL, MOM’s use in daily operation will definitely be more and more important.

 

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

SQLSafe -Arithmetic overflow error converting numeric to data type numeric

SQLSafe -Arithmetic overflow error converting numeric to data type numeric

While I have been using SQLSafe for database backup for a while, today there is a new issue turned up.

When I tried to access the console, one of the policy showed the above error message and can’t display relevant job history.

I then removed the policy, re-created it but still no luck to get it back.

After doing some investigation, finally I found out this was caused by the fact that one of our database size is so big that one stored procedure of SQLSafe actually can’t handle it.

The stored procedure name is “ms_get_server_status” and the fix is to look at line 81 and look for numeric(10,2) and change it to numeric(20,2).

After made the change, sqlsafe console is finally back

Ownership Chaining

Ownership chaining is the classical way of giving users access to objects through stored procedures in SQL Server. And while SQL Server provides two new methods, ownership chaining is what you will use 95% of the time. Certificates and impersonation is something you only have reason to use when ownership chaining does not do the job.

How does ownership chaining work? Say that you have a procedure sp1 owned by user A. sp1 performs a SELECT from tbl1 and tbl2. tbl1 is owned by A, whereas tbl2 is owned B. User C has permission to execute sp1. To be able run this procedure successfully, C needs SELECT permission on tbl2 but not on tbl1. This is because sp1 and tbl1 have the same owner, and this is ownership chaining. Ownership chaining can also be achieved through triggers, user-defined functions and views.

Now, this may seem a little complex to grasp, but in real life it is often a lot simpler. In my experience, having several object owners in a database is not very common. In very many cases, dbo, the database owner, owns all objects in a database. A common way to implement security in a database application is to perform all access through stored procedures that validates input parameters, enforces business rules etc. When dbo owns all procedures and tables, users only need permissions to execute the stored procedures. Thanks to ownership chaining, they do not need any direct permissions on the tables.

The permissions on views also follow the same rules.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

ERP, CRM evaluation

Trying to evaluate some ERP systems, namely Dynamics AX, Dynamics CRM as well as some open source ERP systems, I found a few open source ERP systems that looks promising, such as Compiere (JBOSS+Oracle), Openbravo (Tomcat+Postgres).
will see how this goes.