Start using SQL Server schema objects

,

When we use the word “schema”, we are usually referring to the overall design definitions of tables, views, stored procedures and other objects in a database. Within Microsoft SQL Server, however, there is an object called “schema” which permits other objects (such as tables, views and stored procedures) to be logically grouped together. Every table must belong to a schema, and by default, the default schema name for objects in SQL Server is “dbo.” 

When one is confronted with a list of tables in a database and all of the tables are in a single schema, it is easy to get frustrated when viewing a list of tables in the database. The list can contain several hundred table names, and searching to finding what one wants can be awkward.

In the past, designers of databases in Microsoft SQL Server almost always let all objects remain in the “dbo” schema, and according to the rules of T-SQL (Microsoft’s flavor of SQL), if, say, a table were referenced by its table name alone without including the schema name, it was assumed, even by the SQL Server query engine, to be in the dbo schema. Referring to the table Clients was the same as referring to the table dbo.Clients. There was no other schema than the dbo schema, and there was no other table named Clients.  This works well for databases with few objects, such as tables, in them.

In early versions of Microsoft SQL Server, the schema name could only be the name of a database user. Users could not be removed if there were objects in the schema having that user’s name, and this did not work out well in practice. If database designers used schemas other than dbo (not very common), imaginary users were created so that there was no undesirable linkage between users and schemas. So there were users named Mary and Joe, but also users name Purchasing. No one would ever log in as the Purchasing user, but the user/schema name was used to group objects together which primarily dealt with purchasing.

By creating schemas with meaningful names, it is possible to group tables in clusters, often representing the same clusters of tables that appear in database diagrams that show tables as they relate to each other through referential integrity. 

It is possible to grant security rights by schema, and often this turns out to be a logical classification on which to grant or deny such rights. 

Tearing apart and reconstructing an existing database to group tables into schemas is not, in my opinion, something that should necessarily be done; however, these concepts should be born in mind for future database development work. 

/0 Comments/by

HOWTO: Improve SQL Server in Third-Party App

, ,

SQL Server 

Description:

We have encountered the unfortunate situation where a company has a third-party application and the performance problems associated with this application cannot be solved despite many in-house efforts to do so and many attempts to get cooperation from the vendor in troubleshooting the root cause of the problem.

The vendor has often come back with demands for additional or more powerful hardware or greater network bandwidth where none of the suggestions fix the problem. The company using the software doesn’t know what to do next, especially when the software product is well-known in the industry or is from a major software vendor. Customers don’t know where to go next to seek solutions when the vendor will not cooperate.

When a problem is addressed without cooperation from the third-party vendor, the vendor will usually no longer support the application. This is not an acceptable solution even if solutions are found. The vendor must be persuaded to help in order to effect a real solution. The customer is held back by its own lack of confidence in dealing with the problem to demand a solution.

Many third-party vendors hide behind flimsy policies regarding customer information to refuse to share information they have gathered from other customers which would clearly show that the problem is with their software. They use excuses and tactics which can often be easily disproven without their cooperation. For example, third party vendors often assert that a customer is using their product in a way that is different than other customers; even if there is a problem, the vendor cannot fix the problem without adversely affecting other customers. I have often found that this is nonsense.

SQL Server Problem:

The problem starts when the vendor allocates resources for development of their product. The vendor has or acquires a great deal of knowledge of the business process necessary for the product and designs a great user interface to implement the required functionality.

Because there isn’t a lot to “see” from good data architecture work during the development phase, the vendor has never seriously considered the database as an important part of the application requiring expert resources for design or optimization.

This is true even with Microsoft, where the SharePoint product clearly has many continuing design and tuning issues. (Microsoft continually gets feedback from within the company on this product and in a mind-blowing use of compartmentalization and denial continues to refuse to address these issues.)

Some companies can be persuaded or embarrassed into taking these issues seriously when presented with undeniable evidence and customers who refuse to accept nonsense for excuses.

There are many things that can be done even after deployment. These remedial actions can be implemented as a result of experienced analysis and interpretation of performance monitor traces, results from dynamic management views and SQL Server Profiler traces. Acumen has the experience to do this, and to teach you to do this as well.

As a result of this an engagement where we analyze the use of SQL Server in a third party application, Acumen can provide the following, where appropriate.

Steps:

  1. A written report targeted to a particular application that proves that problems with the application are harmfully affecting performance, if such is the case.
  2. Knowledge transfer from Acumen to you so that you have confidence to pressure the vendor to provide a fix and evidence that the required fixes will not negatively affect performance.
  3. An Acumen consultant can participate in conference calls (or in-person visits) where Acumen becomes your advocate for receiving fixes to your legitimate issues.
  4. Occasionally, Acumen may have previously experiences with this software to defeat claims or implications from the vendor that the problem is yours alone.
/0 Comments/by

DB Editor for WP Forms

,

DB Editor 

DB Editor in WP

When developing WordPress websites we like to use the Formidable Pro plugin for creating forms. Its quite handy but we’ve had it stop working on two sites. We get a:

We’re sorry. It looks like you’ve already submitted that

Error message every time a form is submitted. There’s a fix but it took a while to figure out. It seems to happen when you have the free version of Formidable installed on your website with forms built and you try to install Formidable Pro.

Here is the solution that has worked for us two times now. Beware this solution deletes all existing Forms. You can export the old forms and try to import them back in but we haven’t had a lot of success with this.

  1. Formidable – Global Settings
    1. Uninstall Formidable
  2. Plugins – Formidable
    1. Deactivate
    2. Delete
  3. Using your database Editor navigate to your website
    1. Drop Tables
      1. wp_frm_fields
      2. wp_frm_forms
      3. wp_frm_items_metas
      4. wp_frm_items
  4. Plugins – New
  5. Upload Formidable
  6. Activate Formidable
  7. Enter Pro License
  8. Update Database option appears, click it

You should now be able to create and use forms normally.

Take a look at our WordPress Web Development page for more ways we can help you!

/0 Comments/by