SQL Server - Gilham Consulting Microsoft Notepad

2/28/2010

Understanding SQL Server Joins Basics (Query and Internals)

Great post by Biplab Paul on SQL joins and por/cons of each one on his blog.

I love to explain things in much simpler way but SQL Joins are one of those concepts which are not so simple to explain and I really spend a lot of time in scribbling on White Board for this. But finally I found an article from Jeff Atwood where he did an incredible job by simply using Venn Diagrams to explain the whole concept. I will take few extract from his article but rest you can read from his blog.

INNER JOIN:

SELECT * FROM TableA

INNER JOIN TableB

ON TableA.name = TableB.name

OUTER JOIN (1):

SELECT * FROM TableA

FULL OUTER JOIN TableB

ON TableA.name = TableB.name

OUTER JOIN (2):

SELECT * FROM TableA

LEFT OUTER JOIN TableB

ON TableA.name = TableB.name

OUTER JOIN (3):

SELECT * FROM TableA

LEFT OUTER JOIN TableB

ON TableA.name = TableB.name

WHERE TableB.id IS null

OUTER JOIN (4):

SELECT * FROM TableA

RIGHT OUTER JOIN TableB

ON TableA.name = TableB.name

OUTER JOIN (5):

SELECT * FROM TableA

FULL OUTER JOIN TableB

ON TableA.name = TableB.name

WHERE TableA.id IS null

OR TableB.id IS null

CROSS APPLY (1): without Where Clause

SELECT p.SalesPersonID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

ORDER BY p.SalesPersonID

Note: This can’t be represented by Venn Diagrams but above would multiply all the contents of First Table with all the contents of the second table.

CROSS APPLY (2): with Where Clause

SELECT p.SalesPersonID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

WHERE p.TerritoryID = t.TerritoryID

ORDER BY p.SalesPersonID

Now when we understood these different types of Joins, let’s talk about some of the internal on How these joins would work internally within SQL Server.

<snip>

Read the rest @> Few things which you may find helpful!! : Simplifying SQL Server Joins (Query and Internals)

Posted at 10:55 PM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

10/12/2009

SQL Server 2008 Hardening, Security, Compliance & Auditing

Microsoft has a great site on configuring your SQL server for high security or regulatory compliance.

Microsoft SQL Server 2008 is a secure and reliable platform for protecting data and for building compliance solutions. Conforming to regulatory legislation often impacts how data is stored and how it is accessed. SQL Server® 2008 provides the capabilities to address organizational needs around regulations such as HIPAA, SOX and PCI.
Whether dealing with separation of duties, key management or auditing and reporting, SQL Server 2008 provides the support needed to meet these demands.
Top New Features

Increased ability to protect encryption keys

Granular auditing capabilities

Define, deploy and validate configuration policies destined for your databases

PCI Audit Results

SQL Server can help you comply with PCI compliance needs. Certified audit firm, Parente Randolph, evaluated SQL Server for PCI compliance and provides guidance to customers on passing PCI audits in a detailed whitepaper and informative webcast.

Download the whitepaper

View the webcast

<snip>

Read the rest @> SQL Server 2008 Compliance | Microsoft

Posted at 7:32 PM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

5/14/2009

Microsoft Master Data Services in SQL 2008 R2

Kirk Haselden, product unit manager for Master Data Services at Microsoft explains why MDS is important:

As corporate information ecosystems become more complex, so goes the management of company lists. Master lists are used, accessed, managed, changed and unfortunately corrupted in myriad ways throughout the company in various divisions and in various locations. For example, customer data may be used in the front office for direct contact with customers or in the back office in marketing, billing and other business processes. ERP and CRM systems do a great job of providing the functionality around how you use say, the product master or customer master. That’s what they were designed to do. However, they don’t do a great job of actually managing the forces that impact the master data itself. Four of the most important of those forces are decay, conflict, corruption and inconsistency.

Decay is when the world changes around the data. The data was accurate at one time, but then the world changed but the data wasn’t updated to reflect that change. The classic example is when a customer moves.
Conflict is when duplicate data is introduced into the system. The data may exactly duplicate or differ substantially from established and validated data. In either case, it is usually although not always undesirable. A good example is when two identical parts manufactured in different locations have different part numbers.
Corruption is when established, accurate and validated data is changed in some way that invalidated it. For example, if the price of an PVC elbow joint is misquoted as $550 instead of $5.50, it won’t sell much.
Inconsistency is when the data is formed in unpredictable, erratic or incompatible ways. For example, a company may have a different naming convention for their product codes depending on the division or country where the product is generated. Not using the same canonical address format is another typical example.

Traditional applications manage master data and these forces insufficiently. What is needed to solve these problems is an application that focuses on managing these forces with a set of tools that is specifically designed to ensure that the master data remains authoritative, is available in a secure way across the enterprise and is integrated with the applications that use it. Often the term “One source of the truth” is used to describe what master data management applications deliver. I prefer the term “authoritative source of master data”. Having one source isn’t always desirable nor is it always realistic. Master data isn’t truth. It’s simply data that represents the world at a given time slice. The trick and it’s a difficult one, is to keep the master data authoritative. Authoritative data is reliable, represents the state of the world at the time it’s referenced and is delivered in a secure, integrated and performant way.
<snip>

Read the complete article @> Data Platform Insider : Master Data Services – What’s the big deal?

Posted at 5:26 AM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

4/18/2009

Troubleshooting Performance Problems in SQL Server 2008

It’s not uncommon to experience the occasional slowdown of a database running the Microsoft SQL Server database software. The reasons can range from a poorly designed database to a system that is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems; if they occur, you want to diagnose the cause and take corrective actions to fix the problem whenever possible. This white paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as:

SQL Server Profiler
System Monitor (in the Windows Server 2003 operating system) or Performance Monitor (in the Windows Vista operating system and Windows Server 2008), also known as Perfmon
Dynamic management views (sometimes referred to as DMVs)
SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.

We have limited the scope of this white paper to the problems commonly seen by Microsoft Customer Service and Support (CSS), because an exhaustive analysis of all possible problems is not feasible.
Because this paper is very long (102 pages), reading it in an online format such as the Library becomes impractical, so we offer it as a downloadable Microsoft Word document.
- Download “Troubleshooting Performance Problems in SQL Server 2008” from the Microsoft Download Center

Posted at 1:58 AM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

2/24/2009

SQL Server Log Shipping Best Practices

Log shipping in SQL Server has been introduced since SQL Server 4.21a. But it is still good to used in replicating database in either another location or same server box. It is best used when you upgrading your database, implementing disaster recovery scenario, doing database maintenance while keeping database window as minimum as possible. I list things you need to consider while preparing log shipping for successful implementation.

Make sure database recovery model is full recovery to support transaction log chain recovery.

Before you can restore subsequent transaction logs on secondary server (for log shipping target), you need to restore database full back into secondary server with option either WITH NORECOVERY or WITH STANDBY option. If you don’t specify those options, the default will be WITH RECOVERY so that you couldn’t restore subsequent transaction logs.

If you plan to implement log shipping into multiple secondary servers, it is better to have backup and restore time same for all of them.

Make sure your transaction log backup window period is prudent enough so that avoiding big transaction log applied to secondary server. In this case, size does matter. And make sure no long running transaction such as inserting mass amount of record that would take transaction log recovery longer.

It’s better to consider separating network for copying transaction log from other network activities to ensure copy process doesn’t get distracted. And always copy transaction log backup from primary to intermediate location to avoid single point of failure in primary server.

Avoid log shipping different SQL Server version between primary and secondary. The good point is that log shipping can tolerate with different hardware specification and for different edition of SQL Servers along with their x64/x86 build.

Make sure you have same database collation so that your application will have consistent behaviour such as sorting and searching.

Make sure you allow access to folder share for account that is doing backup and copy task and restrict users to access the share log shipping folder. It is security matter.

Make sure you have your logins identical between primary and secondary.

Script all objects that reside outside database such as SQL Server Agent job, SSIS packages. Test these objects on testing server (act as secondary) to make sure it works fine when secondary server is expected to take over role from primary server.

Never change database recovery model to simple/bulk logged and change it back to full recovery for any good reason. Doing so will break transaction log chain to stop log shipping from running.

Read the complete article @> Things to Consider for Successful Log Shipping - SQL Server knowledge center

Posted at 10:00 AM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

12/1/2008

Installing SQL Server 2005 Reporting Services on Windows Server 2008

I found this great article while installing SQL 2005 on Hyper-V Guest of System Center Operations Manager 2007 (OpsMan 2007). Operations Manager 2007 (last at SP1) does not currently no support for SQL 2008.

Install SQL Server 2005 with Reporting Services

The following steps are required to install SQL Server 2005 with Reporting Services. It is recommended that you use the slipstreamed version of SQL Server 2005 with Service Pack 2 instead of installing the RTM version first, and then applying the Service Pack 2 update.

Notes:

n When you install the release version of SQL Server 2005, you receive a warning message. The warning message explains that this program has known compatibility issues. Click Run program to continue the installation.

n In the Features Section dialog box, make sure that Reporting Services is selected.

n In the Report Server Installation Options dialog box, select one of the following options:

n Install the default configuration

n Install but do not configure the server

Note: If you select the Install but do not configure the server option, you can manually configure the report server after the installation has finished. However, you may experience problems when you manually configure the report server. For troubleshooting help, please see Knowledge Base Article 938245.

Once you have successfully installed SQL Server 2005 SP2 with Reporting Services, verify the SQL Server services are started before moving onto the next section.

Configure IIS

The following steps are required to ensure Operations Manager Reporting Server operates correctly.

Report Server Application Pool

1. Open IIS Manager.

2. Expand the tree until you can select Application Pools.

3. Right-click the ReportServer$Instance Pool and select Advanced Settings.

4. Select Identity under Process Model, click the browse (…) button.

5. In the Application Pool Identity window, select LocalSystem from the Built-in account list box. Press OK twice.

Enable Script Execution

1. Click Start, click Run, type inetmgr, and then click OK.

2. In the Internet Information Services (IIS) Manager, expand Web Sites, expand Default Web Site, and then click the virtual directory for the report server (by default this is ReportServer$Instance1)

3. Under the Features View, double-click Handler Mappings.

4. Highlight ISAPI-dll, right-click and select Edit Feature Permissions

5. Click to select the Scripts check box, and then click OK.

Verify Reporting Services

1. Click Start, point to All Programs, right-click Internet Explorer, and then click Run as administrator.

2. In the User Account Control dialog box, click Continue.

3. Open Report Manager. By default, the URL of Report Manager is the following:

http://ServerName/reports

If you used a named instance of Reporting Services, the URL of the Report Manager is the following:

http://ServerName/reports$InstanceName

When the page renders, you should see the Home page appear empty without any error messages. After you have verified Report Manager is running correctly, proceed with the normal steps of installing Operations Manager 2007 with Service Pack 1 Reporting feature. For more information on the steps required, please see How to Deploy Reporting in Operations Manager 2007 in the Operations Manager 2007 Online Help.

If the page does not render correctly and you are receiving errors, please review Knowledge Base Article 938245 for additional details.

Reference:

http://www.opsmanjam.com/OpsManJam Library/Featured Articles/OM2007_Install_Reporting_WindowsServer2008.doc - 182KB - Chris Fox - 10/22/2008

Posted at 11:36 AM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

11/13/2008

Fixing Invalid SKU Errors When Installing SQL Server 2008 on a Windows 2008 Cluster

A weird issue happened to me when installing SQL 2008 on a 4-Node cluster Windows 2008 x64 cluster.

After having issues installing from an extracted ISO, I had to manually install .Net framework 3.5 and Windows Installer 4.5 hotfixes from the redist directory of the SQL install. Thought I was in the clear, when 2 of the 4 nodes wouldn't let me install SQL 2008 failing with an "Invalid SKU" error.

The solution found on Microsoft's site was:

The solution to adding second time is add the additional node(s) with command line from the SQL 2008 source files:

setup.exe /q /ACTION=AddNode /INSTANCENAME="<Insert Instance Name>" /SQLSVCACCOUNT="<SQL account used on other nodes>" /SQLSVCPASSWORD="<password for SQL account>" /AGTSVCACCOUNT="<SQL Server Agent account used on other nodes>", /AGTSVCPASSWORD="<SQL Server Agent account password>"

Reference:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036

Posted at 3:52 PM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (3)

11/4/2008

SQL Server 2008 Mirroring in Standard Edition

Andrew Fryer posts on SQL Server 2008 Mirroring

I opened the data management track at TechEd EMEA yesterday, to a full room and as ever if I am unsure of a question I research it and put it on this post the next day.
I was pretty sure of the differences in the way database mirroring in standard and enterprise edition of SQL Server 2008 which are:
Standard Edition you can only use mirroring in full safety mode i.e. synchronous mirroring optionally with a witness to enable automatic failover. btw the witness can be express, but the mirror must be standard as well.
Enterprise Edition.

Mirroring can be run in high performance (asynchronous mode) which can be useful in a geo-mirror configuration over a slow network, where synchronous mirroring would create unacceptable delays for the user.
Torn page detection allows the principal to discover and automatically copy a damaged page from the mirror to the principal.
log compression dramatically improves the performance of mirroring in several ways, the send queue is reduced the transaction per/sec transmitted to the mirror are up by 2.5x on a 10mbs network (lees on faster ones). There is some CPU overhead for this

There are no restrictions on how many mirrored databases can be setup on the mirror in standard or enterprise (which could come from different principal servers), however there are some things to remember:

An instance can only have one endpoint which could be a bottleneck if there are lots of databases in the one instance.
It is not recommended to have more than ten databases mirrored to one server, I have not seen that change for SQL Server 2008 but the safe limit should be higher for Enterprise edition given the compression feature.

If you are TechEd I have now got the shuttle working like this

… so that the database in the cluster is mirrored to another server which in turn is log shipped to another instance, so come and say hello on the SQL Server Ask the Experts stand

Andrew Fryer's Blog : SQL Server 2008 Mirroring in Standard Edition

Posted at 1:37 PM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (3)

11/2/2008

SQL Server 2005 Multi-Site Clustering with Windows Server 2008

Great post from Rob's SQL Server Blog

I was working recently with a customer who was looking to deploy a SQL Server 2005 cluster across 2 geographically dispersed sites using Windows Server 2008. They were looking to utilise the new clustering improvements in Windows Server 2008 to build a highly available SQL Server solution. The customer required automatic failover between the sites in the event of a disaster, but their current solution required manual intervention by an administrator in order to failover to the disaster recovery site. Automatic failover would increase application availability, and reduce the complexity of the solution. Each site has it's own SAN storage and the customer planned to replicate data between each site using SRDF replication.
This led me to do further research into clustering SQL Server in this type of environment. Windows Server 2008 introduces greater flexibility in the choice of Quorum configuration. The concept of quorum moves away from the requirement of a shared storage resource, but now refers to the number of votes needed to establish a majority. All nodes and a witness resource can get a vote, which removes the disk as the single point of failure as in previous clustering models. The 4 Quorum Models available are:

Node and Disk Majority
Disk Only
Node Majority
Node and File Share Majority

As there is no shared storage between the nodes in a multi-site cluster, 2 of these Quorum models are suitable for multi-site clustering: Node Majority and Node and File Share Majority. Node and Disk Majority and Disk Only should only be used in a multi-site cluster if specifically directed by your storage vendor as your disk replication software needs to support these configurations.
Node and File Share Majority:
This allows the creation of up to 16 nodes with no shared disks. A file share acts as a witness, meaning that a 2 node cluster will have have 3 votes, so connectivity can be lost by either one of the nodes or the witness and the cluster can still continue to function.
A cluster quorum configured to use a node-and-file-share majority is a great solution for multi-site clusters. The file share witness can reside at a third site independent of either site hosting a cluster node for high disaster resilience. A single file server can serve as a witness to multiple clusters (with each cluster using a separate file share witness on the file server).

This configuration gives the highest resilience as the cluster can automatically recover from the loss of any one site without manual intervention.
The File Share Witness (FSW) needs to be in the sane forest as the nodes and be running Windows Server 2003 or Windows Server 2008. For maximum resilience, it is best to locate the FSW at a 3rd site separate from the cluster nodes. The FSW does not need to be attached to shared storage and should NOT be a node in the same cluster.

Node Majority:
A node-majority cluster consists of 3 or more nodes without shared storage. Each of the nodes has a vote and there is no shared disk vote. A majority of votes are necessary to operate the cluster if 2 nodes fail in a 3 node cluster, then the remaining node drops out of the cluster. An administrator can manually over-ride this and force the remaining node to start. When the other nodes come back, majority quorum is achieved again and the cluster comes back online seamlessly.
This configuration works best with an odd number of cluster nodes as it is not enough to have half the cluster nodes functioning in this model. If four nodes were set up in a node-majority configuration, the cluster would continue to operate with the loss of one node but not with the loss of two nodes. You should use an odd number of nodes with Node Majority as 4 nodes can only survive 1 failure, which is the same as 3 nodes.

The node-majority quorum configuration can work when there is more than one cluster node at each site. Consider a multi-site cluster consisting of five nodes, three of which reside at Site A and the remaining two at Site B. With a break in connectivity between the two sites, Site A can still communicate with three nodes (which is greater than 50 percent of the total), so all of the nodes at Site A stay up. The nodes in Site B are able to communicate with each other, but no one else. Since the two nodes at Site B cannot communicate with the majority, they drop out of cluster membership. (Were Site A is to go down in this case, in order to bring up the cluster at Site B, it would require manual intervention to override the non-majority.)

As a result, the Node Majority configuration does not give automatic failover between sites as nodes 4 and 5 cannot achieve quorum. In this situation, you would need to manually force a failover.
SQL Server Networking Considerations:
Windows Server 2008 now allows nodes in the same cluster to reside in different network subnets and communicate across network routers. However, be aware that SQL Server 2005 and 2008 still require all cluster nodes to reside in the same network subnet, so you will still need to set up virtual local area networks (VLANs) to connect geographically separated cluster nodes. This can have some benefits with regard to client response times though, as DNS replication may impact client re-connection times in the event of a failover from one site to another. VLAN's allow DNS names to stay the same, so can increase availability.
Storage Considerations:
As there is no shared storage between the nodes in a multi-site cluster, the main consideration is how to keep the data replicated between the sites. The choice of 3rd-party replication solution is important and can have a major effect on how you deploy your cluster. As such, you should work closely with your storage vendor from an early stage in the design process.
Synchronous replication results in no data loss, but requires shorter distances between nodes and higher bandwidth to avoid write latency from impacting performance. Asynchronous allows you to stretch cluster nodes across longer distances, however there is a potential for data loss in the event of a failure. Asynchronous data replication also assumes a large enough network bandwidth to keep up with data changes and does not significantly impact application performance.
Conclusion:
As data replication is key in a multi-site SQL Server cluster, work with your storage vendor from an early stage to ensure they support your cluster configuration. Multi-site clustering allows you to achieve high availability and disaster recovery, however it can be a costly and complex solution. You should evaluate your business requirements first and then decide on the best technology to meet these. It could be the case that Database Mirroring, for example, could be used to give you the required level of resilience across geographical sites.
In this case, the customer chose to implement a 2-node, 3-site solution using the Node and File Share Majority quorum model, with a File Share Witness located in the 3rd site. This gives site-level resilience in the event of a disaster and also allows automatic failover between the cluster nodes without having to re-write client applications, meeting the business requirements.
Additional Resources:
Windows Server 2008 Multi-Site Clustering Whitepaper
TechNet Webcast: Geographically Dispersed Failover Clustering in Windows Server 2008
TechNet Webcast: Failover Clustering and Quorum in Windows Server 2008 Enterprise
Support Webcast: Microsoft SQL Server 2005 Failover Clustering on Windows Server 2008
How to: Create a New SQL Server 2005 Failover Cluster (Setup)

Read the whole article @> Rob's SQL Server Blog : SQL Server 2005 Multi-Site Clustering with Windows Server 2008

Posted at 12:57 PM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

6/28/2008

Microsoft offers tools for fighting SQL injection attacks

Recent Security Tools Detailed at:

http://arstechnica.com/journals/microsoft.ars/2008/06/26/microsoft-offers-tools-for-fighting-sql-injection-attacks

An entry on the Windows Server Division WebLog notes that "SQL injection attacks target Web application code, not Web server code, so they can only be avoided by making sure that any Web application that accepts user input, which is then used to query a database, follows best practices to ensure that the input does not contain malicious code or syntax that might compromise the database, Web site, or even the whole server." For this reason, the newly released Microsoft Security Advisory 954462 is not a security bulletin that includes download links to patches. Instead, it helps developers and Web administrators mitigate and prevent SQL injection attacks by offering the following three tools:

Microsoft Source Code Analyzer for SQL Injection (MSCASI): this is a static code analysis tool that identifies SQL Injection vulnerabilities in ASP code, showing the user the root cause of first- and second-order SQL Injection vulnerabilities. Since it requires source code access, MSCASI is meant to be used by web developers.
Scrawlr: Developed by Microsoft and the HP Web Security Research group, this tool will crawl a website, simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities. It is meant to be used by either IT administrators, DB administrators, or web developers.
UrlScan 3.0 Beta (x86 and x64): this tool allows for the deployment of SQL filters that restrict the types of HTTP requests that IIS processes. This will hopefully prevent potentially harmful requests from being executed on the server. It is meant to be used by IT administrators for preventing a problem, but does not truly fix an SQL flaw.

It's great to see that Microsoft is taking the time to offer these tools instead of simply laying the blame on web developers and their code. The software giant has done its part, and now it's up to the rest of us to minimize the number of vulnerabilities on our web servers.

Posted at 4:34 PM by John Gilham | Category: SQL Server | Permalink | Email this Post | Comments (0)

1 - 10

Gilham Consulting Microsoft Notepad