What happens if I Google, “google?”

As I was playing with Google searching the other day, I stumbled across some ways to search that might be helpful to you in specific situations.  Check these out.

Search Terms

You can use the minus sign to exclude certain words from your search.

Enter this search: SQL -Oracle

To find this: A list of all pages that refer to SQL but do not have the word Oracle on them.

You can search all pages that link to your site.

Enter this search: www.centriq.com:link

To find this:  A list of all pages on the web that have a clickable link to your site.

You can search a specific web site.

Enter this search: sql site:www.centriq.com

To find this: A list of pages on Centriq's site that have the word "SQL" on them.

You can search the contents of a url.

Enter this search: inurl:msdn SQL

To find this: All pages that have a URL that contains "MSDN" and have the term "SQL" somewhere on the page.

Of course you can mix and match these queries with AND, NOT and OR, to find combinations of results.

You can also either directly type the following query stings, OR dynamically generate then through asp code for some creative useful searching to users on your site.

Search Parameters

Enter this address:  http://www.google.com/search?q=sql

To find this:  All pages that have "SQL" on them.

Enter this address: http://www.google.com/search?q=sql&as_eq=oracle

To find this: A list of all pages that refer to SQL but do not have the word Oracle on them.  (notice, same as above)

Enter this address:  http://www.google.com/search?as_sitesearch=www.centriq.com

To find this:  Every page at www.centriq.com.

Enter this address:  http://www.google.com/search?q=techsmartkc&as_sitesearch=www.centriq.com

To find this:  Only pages at www.centriq.com that have the term "techsmartkc" on them.

You will find a list of how to use all search terms and search parameters at https://developers.google.com/search-appliance/documentation/50/xml_reference

Happy searching, I hope this article sparks some creativity and encourages you to use Google in an innovative way.

What's New With SQL Server 2012 Training?

I wanted to share with everyone what to expect in the classroom from SQL Server 2012 courses.  As many of you know, for a long time SQL Server classes have been arranged in three core courses, while the content has changed over the years to support the various versions, the structure of these three courses has remained the same.  This structure being: query writing, "traditional" db administration, and database development.  With 2012, these three core facets remain in place in three new 2012 courses.  The query writing course which used to be a jammed packed three day course is now a jammed packed 5 day course (thankfully).  The administration and development courses remain unchanged in length and strong in focusing on the core fundamentals of both administration and development, like backup, restore, indexing, tuning, automating, auditing, and error handling.

These courses are now in beta and we at Centriq are evaluating and providing feedback to Microsoft to ensure the courses offer students what they need.  We plan on offering them to the public as soon as possible and right now that looks like second quarter.

I feel very good about the 2012 SQL training present offerings and I hope to see you in class!

Please use the following URL to get detailed course information on these core course and additional courses as Microsoft makes them available.

http://learning.microsoft.com/manager/catalog.aspx?qry=SQL+2012&nav=trainingtype%3aClassroom+Training&btn=1

Part I - Threats and Vulnerabilities, SMB - Security Is At The Forefront

SMB's share a lot of similarities with large enterprises when it comes to IT Security.  Those similarities include confidentiality of data, preventing unauthorized access and ensuring availability of data.  However, enterprises have the resources such as security personnel to ensure these objectives are achieved.  SMB's have the same threats and vulnerabilities except they may or may not have the the personnel or knowledge that these threats and vulnerabilities may even exist or that they may even be effected by them.  

In Part I of of my "SMB - Security Is At The Forefront" series I am going to explore the unique challenges presented by threats and vulnerabilities for SMB's. 

I. Security Policies  - The Key

Whenever I do a security audit for a SMB, the first thing I find is the lack of a specific security policy.  I either find no policy or only a statement in the employee handbook about the AUP (Acceptable User Policy).  This is not enough in this day and age to ensure a secure environment.  First, the owner or owners need to understand that security is important and that their acceptance and support of a security policy is first and foremost.  A written policy explaining the policies, baselines, standards, procedures and who is responsible for security should be created so everyone understands what is to be expected with regards to security. 

II. Patching Control 

Most SMB's do not have a managed patching system.  By this I mean a centralized method of controlling when and what patches are applied to OS's and applications.  Most SMB's rely on Windows Update to individually update the OS, however it is up to the individual to ensure the updates are applied.  This can mean a workstation might not be updated and have a serious vulnerability.  A major component to a secure system is ensuring that all systems are up to date with the latest patches.  This includes a process that ensures that patches and updates are tested and rolled in a timely fashion.  This can be done easily and effectively by WSUS (Windows Server Update Service).  Also, ensuring a set of procedures to audit to ensure all systems are up to date is very important.

Weak Passwords

This is one of the biggest vulnerabilities for a SMB.  A lot of SMB's have weak password policies or none at all.  Employees are allowed to create passwords on their own for their workstations without any guidelines nor are they made to change them.  Also, in some cases there is no password on the system at all.   A strong password policy is crucial to securing a system.  All employees should be required to have passwords that are at least 8 characters, have a number and a character and should be changed at least every 45 days. 

Default Accounts

The use of default accounts is also sometimes an issue.  By this I mean workstations have just a default account on them such as administrator or guest with no password.  This allows anyone to use the system with minimal or no controls creating a vulnerability whereas a hacker or employee could exploit the machine.

Physical Controls

Since most SMB's only have a few offices, there may not be a great need for locks and door security since this is usually done.  However security to the IT closet or where the servers are located needs to be addressed.  Normally having only a few devices does not negate the fact that all servers, routers, switches and firewalls need to be in a secure place and have limited access. 

Wireless - Rouge Access Points, Weak Wireless Security

Sometimes SMB's will employ wireless solutions just as they would as if they were installing one at home.  This can be a serious concern because business wireless should not be treated like home wireless.  Business wireless should be concerned with getting connectivity with secure protocols and most importantly controlling access to the wired network.  Basic installation and lack of controls on the use of the wireless usually lead to a security breach.

Lack of Security Awareness

Owner and employees need to be aware of secure practices when doing their job.  All employees should understand the impact on the company if they are working on a computer regardless if it is connected to a network.  Having a good understanding of secure practices will help protect the company from most security breaches.

Next month in part 2, I will be discussing most specifics on how to create a SMB security policy.

Thank you and if you have any questions during the series please feel free to email me at wpruett@centriq.com 

Tom Pruett

Network Security Engineer/Senior Technical Instructor

An Analysis of Normalization Through the Eyes of the Developer - Part III

View Part II

Paraphrasing E.F. Codd’s First Normal Form, it states that there should be, “No repeating groups”.  The language column is the group in this example, below you will find the proposed design implements this rule, while the original design does not.

Original design

(Products table)

ProductID	Name	English	Spanish	French	Italian
1	Ceramic tile	1	0	0	0

Proposed design

(Products table)

ProductID	Name	LanguageID
1	Ceramic tile	1

(Language table)

LanguageID	Name
1	English
2	Spanish
3	French
4	Italian

Consider the original design and the proposed design above.  The proposed design removes all of the hindrances that the original design put into place.  Normalization frees the developer from handling issues, because those issues are simply not present.

Problem 1 solved:  Suppose the business expands into a German speaking market, in the original design a column must be added and populated with a “0” for all current and subsequent records.  In the proposed design, simply adding a fifth record to the language table is the only task that needs to be done.

Problem 2 solved:  Keeping data clean is much simpler in the proposed design.  If a product needs a different language, simply change the LanguageID field in the Product table.  In the original design, this would, as best, involve altering data in two different columns.

Problem 3 solved:  In the proposed design, there is no need to store data that is essentially keeps track of the language that the product is not.  In the proposed design, if a product is not associated with a language, a record simply does not exist.

I hope this blog has shown the benefits of simple normalization.  Making the decision to architect even your simplest of applications with normalization can guard against unforeseen complications, while keeping the focus on the business need.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

An Analysis of Normalization Through the Eyes of the Developer - Part II

View Part I
 
This design is also inefficient because it puts a task in the middle tier developer’s hand that a normalized table would already be designed to handle, more to the point, a normalized table would have avoided the problem all together.  I am speaking here of adherence to the first normal form.  The current design would require the developer to manage all data going into all four language columns, per product.  This may include building a subroutine to ensure that only one of the columns in the language family of columns is a “1” and the rest are all “0”s; or manually auditing the data to ensure this; or even worse doing nothing to audit this data and running the risk of having dirty data spread across multiple columns.  A normalized solution allows SQL to naturally avoid this expensive, unnecessary development work, and all future data cleansing work.  Having multiple columns for each language also brings forth the issue of query performance to get the data out.  If the previous inefficiencies are acceptable, the development effort and speed of all queries built against this non-normalized table is guaranteed to become an issue in this design.  There is no quick way to return all records for one specific language in the current non-normalized design.  More importantly, any query that is designed to return the needed data with always be a distance second, performance wise, to a query for the same data built against a properly normalized table.  You are doomed to always have inferior query performance.


One last major issue with this design is the excess space this non-normalized design leads to using.  With one column per language, and four languages, you are essentially saving 3 pieces of data that each say, “I am irrelevant.”.  What a waste!  I am reminded of a former manager who told me once during the architecture of a notification system, “I don’t want to get an email that tells me there is not a problem.”.  The more columns that get added, the more you are guaranteeing wasted space.  As for the argument that states, “Well it’s not that much wasted space, it’s only a bit column.”, how do you think it might be perceived if the accounting department made this argument, “We know we are wasting money, but we’re only wasting a little money.”?  If that is not compelling enough look at it for the ratio of wasted space, in this case we are increasing the necessary data space requirements for this piece of data by 300%!

In Parts I and II, I have pointed out a common development scenario, and have highlighted three shortcomings or on implemented, non-normalized, solution.  In part III, I will propose a normalized solution that eliminates these shortcomings, and saves the developer time, increases application performance and reduces data storage requirements.

View Part III

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

Centriq Announces Cloud Intelligence Conference

We at Centriq are pleased to announce, along with Microsoft,  it will host the 2012 Cloud Intelligence Conference to be held at the Overland Park Convention Center on April 12, 2012. 

This one day conference will cover a variety of powerful network computing information management strategies for businesses of all levels. Some topics will include Public Cloud, Private Cloud, Hybrid Strategies, Cloud Security, Virtualization, Migration and Information Architecture.

Attendees will have the choice of four tracks, with 4 sessions each, in the 2012 Cloud Intelligence Conference including:

• Cloud Roadmaps and Overviews for Executives
• Infrastructure as a Service (IAAS)
• Software as a Service (SAAS)
• Platform as a Service (PAAS)

This event is ideal for IT professionals, business decision makers and executives from all types of technology backgrounds. Seating is limited. To learn more or register for this event, please visit www.centriq.com/CloudIntelligence.

For questions or more information on the event contact your Account Executive at 913-322-7000.

- Jessica Oliver, Director of Operations LinkIn with Jessica

An Analysis of Normalization Through the Eyes of the Developer - Part I

I was reminded a few days ago of how important it is to have a solid understanding of normalization and why it is so important to implement.  I would like to take some time to share that with everyone, especially developers. 
 
Twice now, recently, I have been pinged by frustrated people asking me to help solve their SQL problems.  I asked both what the exact problem was and how it was that they thought I could help.  The root of both problems was bad normalization.  That is, functional normalization had not been done.  What follows is a case study of one of the situations including analysis of what went wrong as well as implementing basic normalization to address the current issue and avoid future issues.
 
One of the frustrated developers was put in a position to bolt on functionality that had never been planned for in the original application.  This is certainly not a new phenomenon, but is evidence why developers need to understand functional normalization (maybe even more so than DBAs). 
 
So what was the issue and how could it be avoided?  The application needed to add language support for each one of its products.  So now products would have a language associated with them, presumably this need arose from the company spreading into global markets.  The developer decided to add a separate column for each supported language, in this case four. If the product was a "Spanish" product, the Spanish column would be set to 1 and the other language columns would be set to 0.  Some of us see the problem inherent in this design already, but for those who don't, read on.
 
Before I continue, let us be clear, this proposed design change to support the new function will work!  Function can be achieved on a broad spectrum of efficiency.  The purpose of this blog is not to discuss the viability of inefficient design; it is to highlight what can be gained by efficient design.
 
First, let’s see how this design is in fact inefficient.  Anytime a design choice guarantees a need for redesign in the future, it is by definition inefficient.  Having a column for each language almost ensures that redesign will occur.  What happens when support for a new language is adopted?  Redesign, albeit simple, never-the-less, redesign.  “Oh, no, we are only going to support four languages.”, I don’t buy it.   I’m willing to bet something like this was said during the initial design, “Oh no, we will not need to support other languages.”.  Function will change, plan for it.
 
There are two other main inefficiencies as well as their resolutions that I want to discuss.  I will add those points in future additions to this blog post.  Check back for the follow-ups and conclusion to this, basic but highly impactful discussion.  Until then, stay thoughtful.

View Part II

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

Managed Code Inside of SQL Server

What roles do you perform as the DBA for your organization?  Many times we find ourselves doing data related tasks that are thrust upon us because of software acquisitions, executive level business requirements, or a myriad of other reason.  Whichever the case, we end up working on some common repetitive tasks and some very specific unique ones.  One of those specific tasks that you may find yourself needing to implement is using managed code in your SQL environment. 
 

This article is not for everyone, that is to say managed code integration is not a common task like backup or index management, rather it is for administrators who find themselves in one of the following two situations; a) you have applications in your environment that were developed and supported in house, and/or b) you work closely with those who develop code to enhance or modify 3rd party applications.
 

In a typical n-tier application, we have front end code, business logic, and the data repository.  In fact we could have a data repository that supports many different front/middle tiers.  In such a case, we could develop stored procedures, triggers, or functions, on the data layer for shared use at the application layer.  We have heard the benefits of centralized administration of networks from Microsoft for years, we can think of the aforementioned approach as centralized development.
 

One problem that could arise from this approach is that an application may (and likely will) already have code written in it for its own use.  Therefore developing it again at the data layer, is time consuming, costly, and may have scaled down functionality due to the limitations of T-SQL.  SQL Server 2008 has the ability to create, what I will term as function “proxies” that are created inside of the SQL Server environment, but are defined in managed code.  Now, if your development team has developed some function that their application needs and placed it into a DLL, you as a SQL professional can centralize that function by simply calling a SQL Server function that calls out to that DLL for its implementation. 
 

Whether the reason for using this feature, is applying centralized coding, or combating the limitation of the data developer not knowing the same business logic specifics that the application developer knows, or that managed code is more capable of handling the business logic tasks than T-SQL, or leveraging the skills of each area of your business to minimize the development cycle – accessing managed code is a great option to explore.
 

Assume your business line is the building of bridges and you frequently need to calculate the density of steel at various elevations.  You may have this function defined in one of your applications on the middle tier, and you may find that you now need to access that function from different applications and possibly inside SQL Server.  Without managed code integration, you would have to recreate the body of the function inside of SQL server or inside of the other application.  You now have the ability to create the function inside of the SQL environment, point the body of the SQL Server function to the DLL function, and simply consume the SQL function, as you normally would.  Of course, there is a little bit of “wire-up” code that you need to provide, as well as some re-architecting from the application standpoint.  You may find the benefit of the code existing in one place makes implementing managed code worth it.  However, a cost analysis to determine if this change would be of benefit would have to be conducted.  However, designing code in the first place to be able to benefit from this capability is a great way to move forward without the cost of retooling any current code.
 

The specifics of how you chose to implement this feature are as varied as the infrastructure of your company, but the benefits of now being able to reuse .NET developed code inside of SQL server is a great way to reduce total cost of ownership in a measurable way.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

Goldielocks and the 3 Database Restore Plans

For those of you who have had me in class before, you have heard me talk about the benefits of applying the SIMPLE recovery model. And you have heard me urge its use if you can get away with it. You have also heard Microsoft write about the benefits of complex multi steps DR plans for huge enterprises. Recently I ran into a situation where an approach right in the middle was called for, and I’d like to share that with you.

Back up and restore scenarios are all about 2 things. “Mean time to recovery” and “Acceptable data loss”. Weaved into the art of leveraging these components are idiosyncrasies to your own environment. Recently I ran into this environment...

ENVIRONMENT:

12 gig Mission critical database

Restore operations MUST be quick and simple

24 hour data loss is acceptable

Disk space for retention of backups is limited (about 60GB available)

Daily transactions touch about 2-10% of the total size of the database (through inserts or updates, deletes almost never occur)

A traditional backup up schedule of a complete every night will not be sufficient, it will provide simple restores and handle the 24 hour data loss requirement, we simply will not have enough space for sufficient retention of backups.

On the other side of the spectrum , we could change to FULL recovery model and run a complete weekly, run transaction logs backups every 15 minutes, then run differentials every night. This option is unacceptable because the restore process is not simple enough, yet it does handle the disk space issue.

Consider this setup. Stay with the SIMPLE recovery model. Set up weekly complete backups and set up daily differential backups. This keeps restores relatively easy, it will always be a two step process – restore the complete then restore the most recent differential (no transaction logs). It also conserves disk space as we only have one 12 GB backup (the complete) per week, and the nightly backup is a differential of no more than several hundred MB (though this will grow throughout the week).

If you have a relatively large database that is mission critical but has few inserts into it daily, and quick/easy restorations is of utmost importance, consider the approach I just went over.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

Concerning NoSQL

Last night, at the KC .NET User group meeting, George Westwater gave a terrific presentation on NoSQL. The best way to understand NoSQL is to see George’s explanation—which he says he will and make it available through his blog. But in short, NoSQL databases are ones that do not use relational technology.

George’s point is that non-relational databases have been used for over 50 years in the business, this is true – I have been around awhile. He points out that Wall Street firms have been using non-relational technology ever since they started using computers. IBM still fully supports IMS, now in version 11 (12 is in beta), because these firms are still using this product and will continue to do so for a long time.

Of course, like a lot of computer business technology, there are a lot of new NoSQL products available these days, simply as a reaction to the problems of scaling relational databases for internet use. As a result, it almost looks as though NoSQL is something new. And there are a lot, I mean a L-O-T , of new products out there for this technology. The best resource to cover all of these products is http://nosql-database.org/, which has a huge listing of what is available.

My interest in the subject is primarily due to my interest in Windows Azure and the fact that Windows Azure storage is all non-relational, even the table storage. It is very fascinating and most of all, far cheaper than using SQL Azure for storage in “the cloud”.

If you are interested in learning more feel free to contact me at lkoorhan@centriq.com and for more on Windows Azure, take a look at our MS-50466 class or contact your Centriq Account Executive.

- Leslie Koorhan, .NET Expert; MCT, MCSD (.NET), MCTS, MCDBA, MCPD LinkIn with Leslie

Managed Code Inside of SQL Server

What roles do you perform as the DBA for your organization?  Many times we find ourselves doing data related tasks that are thrust upon us because of software acquisitions, executive level business requirements, or a myriad of other reason.  Whichever the case, we end up working on some common repetitive tasks and some very specific unique ones.  One of those specific tasks that you may find yourself needing to implement is using managed code in your SQL environment. 
 

This article is not for everyone, that is to say managed code integration is not a common task like backup or index management, rather it is for administrators who find themselves in one of the following two situations; a) you have applications in your environment that were developed and supported in house, and/or b) you work closely with those who develop code to enhance or modify 3rd party applications.
 

In a typical n-tier application, we have front end code, business logic, and the data repository.  In fact we could have a data repository that supports many different front/middle tiers.  In such a case, we could develop stored procedures, triggers, or functions, on the data layer for shared use at the application layer.  We have heard the benefits of centralized administration of networks from Microsoft for years, we can think of the aforementioned approach as centralized development.
 

One problem that could arise from this approach is that an application may (and likely will) already have code written in it for its own use.  Therefore developing it again at the data layer, is time consuming, costly, and may have scaled down functionality due to the limitations of T-SQL.  SQL Server 2008 has the ability to create, what I will term as function “proxies” that are created inside of the SQL Server environment, but are defined in managed code.  Now, if your development team has developed some function that their application needs and placed it into a DLL, you as a SQL professional can centralize that function by simply calling a SQL Server function that calls out to that DLL for its implementation. 
 

Whether the reason for using this feature, is applying centralized coding, or combating the limitation of the data developer not knowing the same business logic specifics that the application developer knows, or that managed code is more capable of handling the business logic tasks than T-SQL, or leveraging the skills of each area of your business to minimize the development cycle – accessing managed code is a great option to explore.
 

Assume your business line is the building of bridges and you frequently need to calculate the density of steel at various elevations.  You may have this function defined in one of your applications on the middle tier, and you may find that you now need to access that function from different applications and possibly inside SQL Server.  Without managed code integration, you would have to recreate the body of the function inside of SQL server or inside of the other application.  You now have the ability to create the function inside of the SQL environment, point the body of the SQL Server function to the DLL function, and simply consume the SQL function, as you normally would.  Of course, there is a little bit of “wire-up” code that you need to provide, as well as some re-architecting from the application standpoint.  You may find the benefit of the code existing in one place makes implementing managed code worth it.  However, a cost analysis to determine if this change would be of benefit would have to be conducted.  However, designing code in the first place to be able to benefit from this capability is a great way to move forward without the cost of retooling any current code.
 

The specifics of how you chose to implement this feature are as varied as the infrastructure of your company, but the benefits of now being able to reuse .NET developed code inside of SQL server is a great way to reduce total cost of ownership in a measurable way.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

How To: Query Spreadsheet Data in a SQL Table [VIDEO]

Centriq's SQL Expert, Ed Dressler demonstrates a quick solution to a very common SQL problem. 




- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

ASP.NET Tips

Looking for more robust and dynamic controls for your ASP.NET application? Check out the new DexExpress “ASPxperience” suite of controls ( http://bit.ly/cOPYz9 ) or the suite from Obout ( http://www.obout.com ). Both suites include great AJAX (and non-AJAX) controls for displaying data, managing navigation, and providing a richer experience for your site users.

What about a great hosting company for your ASP.NET applications or  SqlServer databases? Discount ASP.Net ( http://www.discountasp.net ) is one of the best ASP.Net and SQL Server hosts on the planet. I  have been using DiscountAsp.net to host applications since 2003, and I could not be happier with their reliability and the options available.

Have questions on ASP.NET? Send them my way!

-Neill Horton, ASP.NET Expert; MCTS, MCP, CTT, Sun Certified Java Programmer LinkIn with Neill

She's a little runaway (query)

Stop that runaway query!

Have you ever noticed how SQL Server likes to just take over the CPU and memory of the box it is installed on? It just says, in it’s little SQL brain, “Gee, I think 90% of the CPU should be sufficient to run this query, and maybe I will just keep this memory in case I need it for something else.” Meanwhile, all other queries and/or applications start turning blue from lack of resources and die on the way to the query optimizer.

Are you ready to take back control of the CPU and memory and force applications or users to only use their fair share of the server’s resources? Well, another great, new feature of SQL Server 2008 is called the Resource Governor. You can set limits on how much CPU and memory can be used by creating functions that define an application name, a user name, a host name, a server role name, and so on.

You start by creating Resource Pools and assign minimum and maximum CPU and memory percentages to each pool. For example, you create a pool called Pool1 and assign it a minimum of 20% and a maximum of 30% CPU. This means that anything assigned to the pool will always have some CPU available (20%) but will never exceed 30%.

You also create Workload Groups which are assigned to specific pools. These workload groups can be assigned a high, medium or low priority within that pool. At this point, it would be fair to mention that all unclassified work is dumped into the Default Workload Group. This pool has UNLIMITED access to system resources, the reason that so many of us have seen runaway applications.

The third part is to create Classifier Functions. This defines which users, applications, roles, etc. (workloads), go into which workload groups. Now, we have control!

Just so you know, SQL Server 2008 reserves some CPU and memory for itself that is never given to any user or application. Didn’t you ever wonder how you could access a locked up server using the “SQLCMD –A” utility? There is always CPU and memory on reserve just to keep the motor running like the well oiled machine that SQL Server is. 

And if you like, I can show you how to build one of these Resource Governor contraptions right here in my MS 6231 class which runs from October 18-22 (it’s actually covered on Friday, usually). And if all else fails…

“Just hit it with a hammer!”

- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter