Free Online Computer Books

Four priorities in securing the extended enterprise

2012-01-31T16:12:00.000-08:00

Today's enterprises must constantly create new products and services, expand their geographic presence, streamline operations, and deliver top-notch customer services. To do this, they increasingly will use third-party and cloud services to reduce costs and increase speed to market. Enterprises will unleash the creativity of their employees and customers with mobile, social and rich media technology. More and more, devices -- meaning cameras,cars, home electronics and even musical instruments -- come equipped with microprocessors. All of these will become conduits for delivering services and engaging customers. To stay relevant, enterprises must extend themselves to include new peripheral devices and meet new business scenarios. Forrester Research Inc. uses the term extended enterprise to define this vision, one where a business function is rarely, if ever, a self-contained workflow within the confines of an enterprise's infrastructure.

Securing the extended enterprise will require strategies that encompass architecture and infrastructure. In an extended enterprise, controlling the entire stack is neither practical nor economical; IT executives must seek to provide assurance wherever their data is and wherever their users are, regardless of location, type of asset, or infrastructure ownership. Forrester recommends that CIOs adopt the high-water-mark principle, a model that defines the movement of a mix of interacting objects toward a desired security level. That security level is the "high-water mark."

Everything that applications and data touch -- including unmanaged customer endpoints, servers living in the cloud and third-party applications that are integrated at runtime -- will need to adopt a high-water mark. If a transaction or interaction can't achieve that level of assurance for any reason, it can't be allowed to occur. The high-water-mark principle has two immediate implications: It enables enterprises to extend security assurance downstream to their consumers and business customers and upstream to their service providers and supply chains.

To apply the high-water-mark principle and secure an extended enterprise, enterprises need to tackle four immediate strategic and tactical priorities.

1: Think zero trust for the extended enterprise

Forrester's Zero Trust Model recommends that enterprises eliminate the idea of a "trusted network" (usually the internal network) versus an "un-trusted" network (usually any external network). The high-water mark must be enforced wherever data goes, regardless of whether that data is inside or outside the company network.

The Zero Trust Model requires that you verify but never trust. Implicit trust assumptions are dangerous because they become obsolete when the environment or technology changes. In an extended enterprise, this is distinctly the case: Workloads might move to the cloud, third-party Web services could replace traditional in-house functions and endpoints could move outside the corporate firewall.

2: Rethink endpoint security

Endpoints will be ubiquitous and mobile, and they will have ever-greater CPU speed and memory capacity. They will store users' personal data and applications and perform personal Internet transactions. At the same time, however, they might access your business applications, process customer credentials and store sensitive data. The high-water-mark principle states that you must ensure the integrity of transactions and the authenticity of credentials that originate from endpoints or are processed by them. These assurances must remain in place even if an endpoint is compromised or the rightful owner of the device no longer controls it (for example, if the device is lost or stolen).

Forrester expects that user endpoints ultimately will become lightweight terminals and rendering devices equipped with modern sensors and ubiquitous connectivity. You don't have to trust (and hence secure) the entire endpoint stack to attain transaction integrity and security, because the device now is just a conduit, not necessarily a trusted computing base.

3: Join the API economy

An extended enterprise forces you to make many changes to your IT infrastructure and applications. One such change manifests in the adoption of application programming interfaces (APIs) for enterprise applications. The direction is clear: Monolithic enterprise applications will disappear and be replaced by a rich, API-driven application environment to expedite time to market and achieve a highly efficient ecosystem (Google Maps and Amazon Web Services, for example). If we extrapolate from this trend, it is distinctly possible that in the future, an enterprise might manage only a user directory and internal databases -- nothing else.

With enterprise application models getting overhauled, a new IT security model emerges as well -- one where security functions can be developed and tested independently, and come together only with business functions at runtime to provide protection.

4: Embrace pervasive identity federation

In an extended enterprise, identity information needs to travel in and out to enable a variety of business interactions, regardless of whether the enterprise owns that identity information. For example, your employees may need to interact with your partners' extranet applications, cloud applications and third-party service APIs. Similarly, other enterprises' consumers and employees could need to interact with your externally available applications, services and APIs. In addition, you might offer identity services to your customers to allow them to use third-party applications.

These interactions necessitate some fundamental changes to what we know today asidentity and access management, or IAM. To keep up with changes, enterprises will need to make concrete changes to their identity and access infrastructure.

SQL Server stored procedures supercharged in recent and upcoming versions

2012-01-31T15:58:00.000-08:00

SQL Server stored procedures are Transact-SQL (T-SQL) statements or references to the common language runtime in Microsoft’s .NET framework that are designed to make database querying easier. They accept input parameters, return output parameters and perform operations including SELECT, INSERT, UPDATE and DELETE in the database. Stored procedures provide a number of benefits, from reducing network traffic to reusing cached execution plans and providing increased security and improved ease of maintenance.

SQL Server 2008 introduced a slew of features that offer a significant boost in stored-procedure performance over its earlier counterpart, SQL Server 2005. Almost three years later, 2008 R2 was rolled out with just a few minor tweaks to stored procedures. SQL Server 2012, which will be released sometime this year and is now available in a release candidate version, packs a number of its own performance-boosting additions.

Stored procedures today

One of the biggest stored-procedure improvements in SQL Server 2008 and 2008 R2 is table-valued parameters. This parameter type groups multiple rows of data into sets and then sends them all at once to the database server, reducing round trips between client and server, and it doesn’t create temporary tables or numerous parameters. Table-valued parameters don’t require any locking to initially populate client data. They also enable the client to specify sort order.

The table-valued structure is easy to understand and use, yet it can accommodate complex business logic. You use table-valued parameters the same way you use classic stored-procedure parameters. First, declare a user-defined table type and create a stored procedure with the input parameter of that table type. Next, declare a variable of the table type and reference it. Use an INSERT statement to populate the table variable instead of a SET or SELECT statement. Finally, plug the filled table variable into the stored procedure as an input parameter.

SQL Server 2008 also introduced a MERGE statement that allows for multiple data manipulation language (DML) operations in a single T-SQL statement. With the appropriate indexes on joined tables, you’ll gain increased query performance. That’s because a single MERGE statement reduces the number of times the data in source and target tables is processed. In SQL Server 2005 and earlier versions, each INSERT, DELETE, and UPDATE statement had to process the data one time. MERGE is useful, say, when you are synchronizing two tables (see “Sidebar title TK”).

In SQL Server 2008 and 2008 R2 stored procedures, grouping operations have been enhanced by a new GROUPING SETS syntax. A simple GROUP BY clause returns one row for each combination of all column values, whereas GROUPING SETS returns one row for each unique value in each column. Without this new feature, the latter would take multiple GROUP BY statements for each column combined in a UNION structure. That would inevitably require more resources, like disk I/O operations, memory and runtime.

SQL Server 2008 and 2008 R2 also use a new row constructor to insert multiple rows in a single INSERT statement in stored-procedure programming. Database administrators may already be familiar with this syntax: “INSERT INTO TABLE (COLa, COLb, COLc) … VALUES (V1a, V1b, V1c …), (V2a, V2b, V2c …), … (V1000a, V1000b, V1000c …)”. Here the number 1000 indicates the maximum allowed rows in one INSERT statement. The new INSERT syntax is cleaner because it removes duplicate strings. It also allows for faster round trips to and from the server by reducing network traffic.

In my triple-trial test, I ran 10 batches of 1,000 new-style inserts per batch and 10,000 individual inserts in parallel. Each test counted as one round trip, but compared with the old-school INSERTs, the simplified row constructor cut down the number of transactions and bytes sent from the server 1,000-fold and reduced the amount of client-sent data by a factor of three and client processing time by a factor of 300. Total execution time was reduced by more than 50%.

What’s in Store in 2012

SQL Server stored procedures get some additional tweaks in the upcoming release. In SQL Server 2012, stored-procedure performance in data warehouses can be sped up by 10, 100 or 1,000 times with the help of column-store indexes, a popular scalability feature in the third community technology preview, which was released this summer. Unlike traditional, row-based index structures (also called “row-store indexes”), column-store indexes group and store index data one column at a time, thus significantly reducing query execution time.

SQL Server 2012 also lets developers rebuild indexes on large-object data types online. In SQL Server 2008 R2 and earlier versions, users can’t perform online rebuild operations on, say, varchar(max), nvarchar(max) or varbinary(max) columns or extensible markup language (XML) data types. Stored procedures retrieving data from these data types do not have to wait for the database maintenance window to defragment indexes, hence increasing stored-procedure performance.

Another new feature in the upcoming release is the query optimizer hint called FORCESCAN. When the SQL Server query optimizer underestimates a returned number of rows and mistakenly chooses an index-seek operation, SQL Server developers can make the query scan the index instead. That way, the query process takes less time to complete.

I have only skimmed the surface of the features related to stored-procedure performance in SQL Server 2008, 2008 R2 and 2012. There are numerous subtle changes in these SQL Server versions that contribute indirectly to stored-procedure performance improvements as well, such as new functions and clauses, filtered indexes and new execution plan guides. But the features listed here -- regardless of the version you’re on -- are sure to give your queries a bigger-than-ever performance boost.

Get going with SQL Server 2012 stored-procedure features

Applying any new features without established guidelines is guaranteed to result in frustration. To that end, recommendations are in order for some of the more novel stored-procedure features like MERGE statements in SQL Server 2008 R2 and column-store indexes in SQL Server 2012.

MERGE statements:

Index columns in joins for source and target tables.
Specify search conditions in the ON clause by including columns in source and target tables only. Do not include extra comparisons, like constant values. If you use database objects like common table expressions or views to hold filtered rows, evaluate the results thoroughly. These are temporary result sets and may produce incorrect results.
If you use a join hint, which forces the optimizer to use a certain join at some point in a query, use OPTION (LOOP JOIN) instead of a hash join, which bypasses indexes.
Parameterize all literal values in ON and WHEN clauses in stored procedures. If you can’t, create a type TEMPLATE plan guide and specify the PARAMETERIZATION FORCE query hint. Make the setting carefully, because it will affect the entire database.

Column-store indexes:

Choose columns that provide the best compression, such as character or numeric columns.
Follow a normal update procedure on a table with a column-store index by dropping the index, and then perform data modification operations to the table. Finally, rebuild the column-store index.
Partition the table and switch the partitions. First, switch a partition out of the main table and into a staging table and drop in the column-store index. Update the data and then re-create the index in the staging table. Finally, switch the staging table back into the main table.
Tables with column-store indexes cannot be updated; they become read-only once you create them. But do not use a column store index as a way to make a table read-only. Microsoft could very well remove the update restriction in future releases of SQL Server. If you need a read-only table, create a read-only file group and move the table to that file group.

Reason's for SQL Server Denali

2012-01-31T15:46:00.000-08:00

The last three releases of SQL Server had unofficial labels, each based on the type of user that the most new features were geared toward. For example, SQL Server 2005 was considered a developer release, while SQL Server 2008 was viewed as a release for database administrators. SQL Server 2008 R2, with Microsoft’s PowerPivot data analysis tool and enhancements to both SQL Server Analysis Services and Reporting Services, was widely known as a business intelligence (BI) release.

Microsoft released the first community technology preview (CTP) of the next version of SQL Server, code-named Denali, at November’s Professional Association for SQL Server (PASS) Summit 2010 in Seattle. It appears that this release won’t continue the labeling tradition; instead, SQL Server Denali has something for everybody. And several of the new features make Denali compelling enough to start evaluating and testing it now, so you’ll be ready to upgrade once the commercial release becomes available later this year or next year.

Column-Store Indexes

First, there’s Project Apollo, which harnesses the same column-store indexes that power PowerPivot in the database engine. Fueled by the database architecture called VertiPaq, column-store indexes are designed to provide significant performance gains over regular indexes.

In a regular index, indexed data from each row is kept together on a single page, and data in each column is spread across all of the pages. A column-store index packs data from each column together, so each page contains data from a single column. Plus, the index data for each column is compressed, and since columns often contain highly repetitive values, you’re looking at a high compression ratio. All this means fewer pages in the index and fewer pages that need to be scanned if you’re selecting a small number of columns.

Since the nature of the data stored in SQL Server varies from instance to instance, it’s difficult to know how much faster data retrieval will be in real-world applications, but Microsoft says it could run from several times to several hundred times faster.

From a business standpoint, this feature could change the way users work with your data warehouse. Traditionally, data warehouse tables are too big to query in real time, so they aren’t suitable for ad hoc queries: The data in those tables needs to be aggregated, cubes need to be built or reports need to be generated and cached overnight in order to provide users with timely responses.

Let’s say you have a $1,000 in your checking account and write $500 in checks. But look online soon afterward, and your balance still shows that $1,000. Banks sometimes take a while to refresh account balances because with large datasets, it’s either impractical or nearly impossible to do real-time updates. Similarly, data warehouses often have to run extract, transform and load (ETL) processes at night to update and aggregate data.

But a column-store index can potentially run a multi-hour query in minutes and a multi-minute query in seconds, so your business could switch to real-time queries. That means no more waiting for days for the data to be collected and aggregated. The bottom line? Your business users and executive decision makers will have the data they need much more readily.

AlwaysOn

Each version of SQL Server has either added new high-availability features or improved existing ones. But while there are several viable options, such as database mirroring, log shipping or SQL Server clustering, each has its own disadvantages; you need to be ready for compromise when choosing one technology over the other.

SQL Server Denali introduces a new technology called AlwaysOn, or High Availability Disaster Recovery (HADR). According to Microsoft, developing it was a significant engineering project, exceeding the effort required to rewrite the SQL Server engine for version 7.0 in the late 1990s, after Microsoft and original development partner Sybase Inc. went their own ways on the database. Look at AlwaysOn as a combination of what worked best in database mirroring and clustering, with new capabilities added based on user feedback.

For example, database mirroring works on a single database, but often a database accesses objects in other databases on the same server. So, let’s say Database A accesses objects in Database B using views or synonyms; those queries work only if both databases are on the same server. But if Database A fails over to the mirror server, the references to the objects in Database B become invalid, and your application starts generating errors.

To enable cross-database dependency in a mirroring environment, AlwaysOn introduces something called an availability group. You can define a group of databases as an availability unit; then they all fail over as a unit, and your cross-database references will remain functional. AlwaysOn also lets you configure several replicas of the primary database. These replicas can be made read-only for reporting purposes. If your primary database fails, one of the replicas can take over in the primary role.

For many businesses, these improved HADR features will be the most attractive reason to upgrade to SQL Server Denali. These days, more e-commerce databases need to be available 24/7, and even a short outage could cost you revenue as well as customers. Therefore, a high-availability option that provides seamless failover with minimal downtime and makes it easier to bring up primary databases on secondary servers can provide significant return on investment.

Data Visualization, aka Project Crescent

This feature is probably the most exciting new BI offering in SQL Server Denali, and more specifically, in the Microsoft self-service BI mix. Crescent continues data visualization enhancements delivered in SQL Server Reporting Services 2008 R2, and later in PowerPivot. It is a Web-based report designer that utilizes Microsoft’s Silverlight application framework and provides ad hoc reporting to end users with an easy-to-use presentation tool and powerful visualization components.

Behind the scenes, Crescent utilizes Microsoft’s Business Intelligence Semantic Model (BISM), a new technology that makes building BI models easier than by using traditional cubes. And that’s made it easier for less-technical users to create BI applications. BISM uses the same VertiPaq technology that’s behind column-store indexes, allowing fast response times when querying large sets of data. When SQL Server Denali was previewed at the PASS Summit, it was Crescent that caused the biggest stir. Audiences were impressed with the demonstrations of the new tool, especially its visual presentation of data, which goes way beyond traditional charts or key performance indicators.

Project Crescent will provide value to companies that want to enable less-technical staff -- managers, executives, marketing departments, researchers -- to browse data, run ad hoc reporting and present data in a visually striking format. It will also be very useful to companies that specialize in collecting, processing and selling data in report formats.

So not only will Crescent make reports look a whole lot cooler, it will also increase perception of the value of the reports and the work that went into them. Think of it as visiting a fancy French restaurant -- the creative presentation of food on your platter not only enhances your enjoyment of the meal, it increases your perception of its value.

Upgrading to SQL Server Denali when it’s released is bound to have a significant impact. Its column-store index technology will give business users quicker, timelier access to the data stored in data warehouses. With Project Crescent, you‘ll be able to put together flashier reports. And if all works as promised, AlwaysOn will reduce the downtime of your SQL Server installations.

Some words of caution, though: The initial previews of Denali are impressive, but we still haven’t seen the “small print” on features -- things like restrictions, limitations and licensing requirements. In early 2011, column-store indexes and Project Crescent weren’t even available for evaluation, since they weren’t included in the first CTP. AlwaysOn was, but in a limited feature set. Once all of these features are fully available in a public CTP, SQL Server users will get a better picture of how they can best use them.

Making the most of server performance monitoring

2012-01-20T07:49:00.000-08:00

It’s just not enough to install new servers, set them up, install applications and then walk away. Servers need regular performance monitoring to ensure that your hardware investment will deliver the service you expect – and provide ample early warning of impending trouble, such as resource shortages or hardware issues.

Performance monitoring tools can provide a wealth of useful information, but only when those tools are set up and running properly. Fortunately, a few important insights will help any administrator get the best results from performance monitoring.

Achieving accuracy in performance monitoring

Monitoring is useless if it delivers erroneous information, so ensuring accurate data should be one of your first considerations. There are several aspects to accuracy, including interoperability, sampling window, tool architecture, virtualization awareness and calibration.

Interoperability. For this discussion, interoperability is basically the ability of a performance monitoring tool to access and read data points from the various pieces of hardware within your data center environment. Homogeneous environments focused on a single vendor’s product line can take advantage of performance monitoring tools that use hooks deliberately integrated into the hardware. These hooks can deliver detailed information to the tool.

The situation can be far more challenging for heterogeneous environments, where tools and hardware don’t mesh. A vendor’s tool may look for data that certain pieces of hardware simply cannot provide with the required level of consistency (if at all). It’s a similar problem for third-party performance monitoring tools that often cannot detect every sensor or hardware nuance on every possible device, and instead rely more on operating system-level data, which usually lacks granularity. In either case, the result is missing data or inaccurate data points that reduce the insight gained from performance monitoring.

This unavoidable correlation between tools and hardware requires comprehensive testing. For example, run the tools before you buy them, and verify compatibility with a long-term proof-of-principle project that will take the tool from a lab setting into a production environment. But the problem also extends beyond the initial purchase to future upgrades and technology refresh cycles. When you change hardware or update the tools, you’ll need to test interoperability to ensure the continued integrity of your performance monitoring system.

Sampling. Accuracy will also depend on the sampling window used to gather data. This is particularly important when workloads or operating parameters can change radically over time. Ideally, performance monitoring should capture the entire “operational cycle” of the machine. The trick is to determine what that “operational cycle” should be. It will depend on the way that each workload and host machine is used. For example, watching the memory performance of a server may require a fast sampling rate with a window that spans just a few minutes. Conversely, watching the CPU utilization of a corporate HR system may require monitoring at a lower rate, but over a 30 day period or longer. There is no single answer, and various system attributes may be monitored at several different rates and windows.

“If you’re testing how well a server environment will work during a usage spike, the administrator should set their schedule that will look at regular operations, then the performance spike, and finally the return to normal operations,” said Bill Kleyman, virtualization architect at MTM Technologies Inc. “Setting the schedule too far back will capture useless data, and setting scheduling too short will miss some of the important data-at-rest statistics prior to the performance peak.”

Tool architecture. Performance monitoring tools rarely operate without the use of agents or drivers installed on each host system (or even each virtual machine). Agents are mixed blessings. They are useful because agents can collect and deliver far more granular information than “agentless” monitoring tools. However, agents are also software “clients” reporting back to a central server that collects and processes the data. So each agent also requires a certain amount of computing resources that can potentially impact the performance of the related workload.

“All the computers in my environment have two agents,” said Chris Steffen, principal technical architect at Kroll Factual Data. “An application agent monitors the health of our applications, and we have System Center [Virtual Machine Manager] agents on all virtual machine hosts.”

The negative impact of agents is generally lower now than in years past, but their influence should still be evaluated, especially on mission-critical or performance-dependent workloads. In addition, Steffen also notes that emerging tools may provide features that can automate the installation, reinstallation and maintenance of agents within the environment.

Virtualization awareness. Virtualization software works by abstracting the application workloads from the underlying hardware. When traditional performance monitoring tools attempt to report within a virtual environment, the abstraction layer often causes erroneous results because the older tool may attempt to monitor hardware directly rather than through the hypervisor, which controls computing resources.

Considering the popularity and importance of virtualization technology, administrators should certainly select performance monitoring tools that are virtualization aware. This allows performance monitoring to take place in both physical and virtual targets, and administrators can gather accurate information about the system’s resource utilization and behavior.

“Administrators will sometimes gather metrics of the VMs and the physical host that they are running on,” Kleyman said. “This way, performance can be monitored at the virtual and physical level to ensure the best workload performance and a solid end-user experience.”

Sensor calibration. Don’t overlook the importance of the sensors themselves. Digital data produced from the network switch or server may be quite reliable over time. But some sensors, such as temperature, humidity, air flow or other environmental sensors with an analog element, may also require regular calibration and periodic battery replacement to ensure reliable long-term operation.

Making the most of performance monitoring tools

Tools have little value if they are not employed productively. In far too many cases, performance monitoring tools are deployed, but there is no clear plan on how to use the vast amount of detailed data that the tool produces. The tool winds up being marginalized as administrators only use it for spot checks or occasional troubleshooting; it’s a wasted investment.

Experts suggest boosting the value of your performance monitoring tool by understanding the business implications – why it’s needed and how its data will be used – long before the tool is actually deployed. Also, take full advantage of the tool’s analytical features to help evaluate and report on collected data. It may take time to configure the tool’s reporting features for your specific environment, but the insights gained from proper analytics are worthwhile.

Performance monitoring reports can also provide a factual foundation for capacity planning or help make a case for technology refresh projects. “Performance metrics can help show ROI [return on investment],” Kleyman said. “By knowing what older systems did and how well new ones are performing, we are able to put a dollar figure on our environment and garner more funding for further improvements.”

Managing SQL Azure database isn’t no-fuss, but it’s close

2012-01-20T07:43:00.000-08:00

If your organization is considering moving some of its databases to a cloud service like Microsoft’s SQL Azure database platform, you might wonder what that means for you as a database administrator (DBA) -- however “reluctant” you are in taking on those duties.

The good news is that the answer is “pretty much the same thing.”

At the server level, you’ll actually have a bit less to worry about. Performance won’t necessarily be your problem (though keep reading; you’ll still have a role to play). Backing up the database and ensuring reliability become Microsoft’s problem, too, not yours. Replication isn’t something you’ll need to worry about, and the physical servers are also out of your control. You won’t need to worry about file groups, defragmenting hard disks or any other physical-level stuff.

Everything inside the database is still your problem, and performance will still be a concern. Microsoft’s charges for SQL Azure are based, in part, on the amount of computing power you use. Poorly written queries run against poorly maintained databases will require more computing power, making your database more expensive. It’s therefore more important than ever that your indexes are well-maintained and well-tuned, so a regular maintenance plan that includes index reorganization is a good idea.

Don’t overdo it, of course; indexes with a low fill factor will take more disk space in the cloud, and you’ll pay for that, too. The computing power needed to reorganize or rebuild an index is also something you’ll pay for. So you’ll have to pay closer attention to the health of your indexes: Are they defragmented and do they have a lot of page splits? Then a reorg or rebuild is probably called for.
You won’t rely on Performance Monitoring to watch performance, but rather SQL Azure’s Dynamic Management Views. You’ll pick up a new administrative task, too: SQL Azure’s firewall, which is how you control access to your SQL Azure instance. You can still back up your database, and it’s not a bad idea to do so regularly; that way, you’ll have a local copy in case something terrible happens to your data. Remember, Microsoft is responsible for ensuring its availability, but the company’s not responsible for protecting the data against accidental, unwanted changes -- so you’ll need your own backup to protect against that.

Many of your tools will remain the same, since SQL Azure responds to the same management console, PowerShell commands and other management tools that an on-premises SQL Server instance would use. There are a few differences, of course, but Microsoft has good guidance on those differences, such as when running Sqlcmd.exe or Bcp.exe.

All in all, managing a SQL Azure database isn’t much different than managing an on-premises database. Many of your tasks will be the same, although you’ll need to run them through the filter of how Azure charges: disk space, computing power, bandwidth, and transactions. Some tasks, such as those associated with physical server administration, are Microsoft’s problem, making your job just a bit easier.

Windows Server 2008 R2 hardware selection tips

2012-01-02T14:46:00.000-08:00

Hardware has always played second fiddle to software; after all, it is software that truly dictates what someone can accomplish in the world of IT. Nevertheless, selecting the appropriate hardware is probably the most critical element when deploying a new piece of technology. Take, for example, Microsoft’s Windows Server 2008 R2, a critical piece of software for most any local or wide-area network. Make an error here when selecting hardware and admins can expect significant repercussions, ranging from applications running too slow to use to budgets spinning out of control to a complete failure of a line-of-business application.

Picking the right hardware for Windows Server 2008 R2 takes a bit more than just checking off the options boxes on an order form for a server; there are several key steps and important calculations that must be done before narrowing down the selection of hardware to a few candidates. The first step is determining what version of Windows Server 2008 R2 you will run (there are at least five) and what role that server will be taking on (there are dozens of combinations for roles).

Windows Server 2008 R2 has different and specific CPU requirements. First and foremost is the fact that Windows Server 2008 R2 requires a 64-bit processor; 32-bit support went out the window when R2 hit the streets. Now, that may sound like a limiting factor, but the truth of the matter is that most of the CPUs on the market support 64-bit operation. This means any newer piece of server equipment will meet the minimum processor requirements. However, minimum is just that: a minimum! That means you will want to select a hardware combination that will meet your current and future needs, without overburdening the network, and leave those minimal requirements to the budget constrained. Of course, Microsoft has come up with a list of recommended configurations for each Windows Server 2008 R2 edition:

Windows Server 2008 R2 Foundation Edition

The Foundation edition of Windows Server 2008 R2 is an entry-level edition that is designed specifically for small businesses. It is the least expensive of the various editions and lacks some of the more advanced functionality typically found in the higher-end editions such as full Active Directory support and Hyper-V virtualization capabilities. In addition, the Foundation edition limits some of the included features, such as restricting the number of concurrent remote desktop services connections to 50, and is able to access a maximum of one processor and 8 GB of RAM. So, for those deploying the foundation edition, it doesn’t pay to go with a multiprocessor system or more than 8 GB of RAM – in other words, an entry-level server is all it will take to run Foundation Edition.

Windows Server 2008 R2 Standard Edition

Windows Server 2008 R2 Standard is also an entry-level server offering (alongside Windows Web Server R2 2008 and Windows Server R2 2008 Foundation). The R2 version supports only 64-bit processors and in terms of hardware, Standard Edition supports up to 4 GB of RAM and four processors.

Windows Server 2008 is primarily targeted at small- and mid-sized businesses (SMBs) and is ideal for providing domain, Web, DNS, remote access, print, file, and application services. However, there is no support for clustering, meaning that the standard edition is not meant for fault tolerant environments, data centers or other larger enterprise deployments. That helps to keep hardware requirements down. However, if Standard Edition is deployed to support multiple roles, multiple processors will come in handy. Also, those looking to the future may want to build a little more capability into their servers, as an upgrade path to Windows Server 2008 R2 Standard is available from Windows Server 2008, Windows 2000 Server and Windows Server 2003 Standard Edition.

Windows Server 2008 R2 Enterprise Edition

Windows Server 2008 R2 Enterprise Edition provides greater functionality and scalability than the Standard Edition, including support for clusters of up to 16 nodes and Active Directory Federated Services (ADFS). The core OS sports other enhancements as well, such as support for as many as eight processors and up to 2 TB of RAM.

With the Enterprise Edition, hardware calculations become a little more complex. IT managers will need to decide how many user accounts the server will support, how a cluster will be defined, how many additional services and roles will be deployed (i.e. SQL Server, SharePoint, etc.). Each of those elements increases load, as well as storage space.

Other capabilities to be aware of include Hyper-V virtualization, Firewall services, Terminal Services, Remote Desktop, failover, and synchronization – each of which adds to the server burden. Windows Server 2000, Windows 2000 Advanced Server, Windows Server 2003 Standard Edition and Windows Server 2003 Enterprise Edition may all be upgraded to Windows Server 2008 R2 Enterprise Edition.

Windows Server 2008 R2 Datacenter Edition

The Datacenter edition represents the top end of the Windows Server 2008 R2 product range and is targeted at mission-critical enterprises requiring stability and high uptime levels. Windows Server 2008 R2 Datacenter edition supports up to 2TB of RAM and a minimum of 8 processors up to a maximum of 64. Upgrade paths to Windows Server 2008 R2 Datacenter Edition are available from the Datacenter editions of Windows Server 2008, Windows 2000 and 2003.

With the Datacenter Edition, IT managers will find themselves in the world of rack-based blade servers, SAN arrays and other high-end, rack-mounted equipment. Here, support, as well as features and capabilities all play an equally important role – which shifts the hardware toward a more managed, more fault-tolerant environment.

Windows Web Server 2008 R2

Windows Web Server R2 2008 is essentially a version of Windows Server 2008 R2 designed primarily for the purpose of providing web services. It includes Internet Information Services (IIS) 7.0 along with associated services such as Simple Mail Transfer Protocol (SMTP) and Telnet. It supports up to 4 processors but RAM is limited to 32 GB. As with other entry level editions, Windows Web Server 2008 R2 lacks many of the features present in other editions such as clustering, BitLocker drive encryption, multipath I/O, Windows Internet Naming Service (WINS), Removable Storage Management and SAN Management.

The Web Server Edition can run on low-end server appliances or with minimal hardware, making it an inexpensive form of Windows server to deploy. However, since no traditional upgrade path exists – the Web Server Edition can be placed on set and forget it hardware configurations – because, odds are if you outgrow Web Server Edition, you will be moving to a different Web Server platform, with matching hardware to boot.

Looking at it from a basic point of view, selecting hardware is not all that difficult – perhaps the biggest distinction being what it is that you expect from your network, how much growth you anticipate and if there are any upgrade plans in the near future.

Major selection decisions depend on whether or not you are looking to deploy clusters/blades or SANs, as well as the expected activity on the network. For many, the easiest path is to discuss network needs and desires with server manufacturers and then pick a bundle that offers hardware with vendor support that is easy to upgrade and comes with Windows Server pre-installed. That way, sizing is left to engineers, while deployment is left to the hands-on IT staff.

BI secret to using Excel in Microsoft SQL Azure: PowerPivot

2011-12-18T13:50:00.000-08:00

Microsoft Excel has entrenched itself solidly as a data analysis tool, in part because many of the people performing business intelligence (BI) work cut their teeth doing stats in Excel and find it a comfortable environment to work in. To that end, many folks have found ways to use Excel as a BI tool with SQL Server -- as a front end for manipulating data reported from the server.

That’s SQL Server on the ground, but what about up in the cloud? Since SQL Azure, Microsoft’s cloud database service, is SQL Server (that is, in the cloud), are there ways of using Excel in Microsoft SQL Azure as a BI tool? The short answer is yes, but it requires a little work.

BI blogger Chris Webb attempted to do something like this with Excel 2007 and the Excel Web App, but found the toolset too limited. His idea was to create an Excel spreadsheet with live data links to SQL Azure, upload it to the Excel Web App and have that data refresh in real time. No such luck. He was able to use open database connectivity (ODBC) as the connection layer, but he had trouble linking to Azure through Excel. And the Excel Web App doesn’t support external data connections, so the resulting spreadsheet consisted of a static data snapshot -- not very useful for live BI.

What doeswork is the PowerPivot add-on in the desktop version of Excel 2010. PowerPivot allows Excel to perform real-time data analysis against a remote source. Normally, it’s used in conjunction with SQL Server, and it works with any SQL Azure data source as well.

The first step is to set up a data connection in Excel to SQL Azure. It’s not terribly different from connecting to a conventional SQL Server source. One important thing is that you will need to make sure your SQL Azure instance has its firewall open to allow access from the client in question. Azure instances have firewall settings locked down by default to refuse connections from anything except the Web-based management console.

Next up is installing and using PowerPivot in conjunction with Azure. Good news: PowerPivot supports Azure natively, which means your data can be cached and manipulated locally without an active connection and refreshed on demand. (It’s not quite live BI, but close enough for most people.) This offline caching and manipulation is handy if you want to do things like build service cubes, since Azure does not have native support for such things -- at least not yet.

Also worth mentioning here is that you cannot edit remote SQL Azure data through Excel. Excel can only be used to fetch, transform and report on the data, but it can’t make changes to it. One can assume most database administrators would like to keep it that way, since being able to make casual changes to what might be mission-critical data is a bad idea. If you’re determined to do this, though, and you have confidence that your Excel users are not going to make a mess of things, there are a few ways to pull it off.

The folks at SQLServerPedia have a walk through that explains how to do this with nothing more than Excel and ODBC. It involves a specially crafted Excel spreadsheet that obtains data from the remote server and then updates it whenever there’s a change. Most of the magic is done through macros, so you don’t need to perform any additional commands. If you want a full-blown product that turns Excel into a data-editing front end, check out SaveToDB, which works with both SQL Server and SQL Azure and has a sophisticated way of handling the data interchange between the client and back end.

Anyone who is serious about using Excel as a BI environment -- and not just with SQL Azure -- should use the 64-bit version of Excel whenever they can and run that on a machine with 4 GB of memory or more for the best possible performance. The more data is cached in memory, the faster the analysis.

Take BI up a notch with SQL Azure Reporting

2011-12-18T13:47:00.000-08:00

As part of a continued effort to expand its cloud-based services, Microsoft recently released the community technology preview of SQL Azure Reporting, a report hosting and rendering service built on the Windows Azure platform.

Like SQL Server Reporting Services (SSRS), SQL Azure Reporting delivers operational reports -- based on Microsoft’s report definition language (RDL) -- along with a variety of rich formatting and data visualization options. In fact, it is built on SSRS technology. The primary difference between the two, other than one being a cloud-based service as opposed to a desktop application, is that SQL Azure Reporting can use only Microsoft SQL Azure databases for its data sources.

When you host your reports on the service’s platform, users can view them through Web browsers, on-premises applications and applications in the Windows Azure cloud. However, to deliver those reports, you must create and publish them to the SQL Azure Reporting platform and set up the service’s environment, and that starts with having a basic understanding of how the report-delivery components fit together.

Managing SQL Azure ReportingAfter you sign up for SQL Azure Reporting in Windows Azure, you can use the SQL Azure Reporting Portal to perform the tasks necessary to manage your environment and your reports.

Your first task will be to set up a report server, which is comparable to setting up one for SSRS. You host the reports on the server, and the applications necessary to view those reports connect to the server. Figure 1 illustrates how the report delivery process works.

Figure 1. SQL Azure Reporting offers cloud-based reporting in Windows Azure.

You can also use the SQL Azure Reporting Portal to manage users and permissions, create shared data sources and download execution logs. In addition, you can upload reports through the portal; however, those reports are not verified as rigorously as reports deployed through SQL Server Business Intelligence Development Studio (BIDS), the primary development tool for SQL Azure reports.

Another option for managing the environment is using an application that interfaces with the Simple Object Access Protocol (SOAP) endpoints of the report server’s Web service. This method lets you perform common administrative tasks such as configuring shared data sources, creating users, setting permissions and managing reports.

Publishing reports to SQL Azure ReportingThe primary tool for developing and deploying reports to SQL Azure Reporting is the SQL Server 2008 R2 version of BIDS. If you know how to develop reports in BIDS (using the Report Designer feature), the process for creating SQL Azure reports will be familiar. You have access to the complete set of formatting and visualization tools -- such as maps, charts and gauges -- as well as the ability to report parts, shared data sources and shared data sets, just as you do in SSRS.

When SQL Server 2012 is released, you should also be able to use the SQL Server Data Tools --formerly known by the code name Juneau -- to create and deploy reports to the SQL Azure Reporting server. As with BIDS, SQL Server Data Tools should include the formatting and visualization components available in BIDS.

Another option for creating reports is Report Builder 3.0. However, you cannot deploy to the report server directly in Report Builder. Instead, you can take one of two steps: Move the reports into BIDS and deploy from there or upload your reports using the SQL Azure Reporting Portal.

Viewing reports in SQL Azure ReportingSQL Azure Reporting supports several methods for viewing reports, as shown in Figure 1. The quickest and easiest way is through a Web browser. The user simply navigates to the report via the URL that connects to the report server and signs in with the necessary credentials.

Users can also view reports through applications that have the Report Viewer control embedded in them. Report Viewer is a .NET control that lets you display a report within an application. When the control is set to remote mode, it can point to a SQL Azure Reporting or SSRS report server. For SQL Azure reports, you can use an on-premises ASP.NET or Windows Forms application or an application implemented on the Windows Azure platform.

Reporting in the cloudTogether, the components described here provide organizations with the means necessary to host and present their reports in a cloud environment. No longer do they have to provision or maintain the hardware necessary to provide reports to the people who need to see them. Because SQL Azure Reporting is hosted in Microsoft’s data center, organizations need only sign up for the service and create reports. Well, that and pay for the service. But for those that have been unable to deliver critical business intelligence data because of limited infrastructure, cloud reporting could prove to be the ideal offering.

Oracle database consolidation is a paradigm shift

2011-12-03T13:45:00.000-08:00

If you’re like most companies, the success of more than two decades of mid-range relational database deployments has created an IT dilemma – a lot of databases and an increasing total cost of ownership (TCO). The good news is that industry consolidation trends apply to the database too. Better yet, the Oracle database provides several strategies to enable multi-tenancy and thereby reduce the total number of databases.

Evolution of Oracle database sprawl

First, a little bit of history. Let’s ask ourselves how this got out of control. You could argue that line-of-business separation, budgeting, chargeback, security, acquisitions and performance concerns all played a part. But the biggest reason is much simpler: It’s just so darn easy to create a database. In fact, Oracle has made it so simple that you can now create a database and be up and running in minutes.

The proliferation of databases, sometimes referred to as database sprawl, is expensive and difficult to manage. It’s not uncommon to find one database per application per software development lifecycle (SDLC) environment. Think about it. If you have 10 applications each with a development, test, quality assurance, performance, training and production environment, you have 60 databases! It’s no wonder why many organizations find themselves with hundreds of databases and ever increasing costs.

This phenomenon is true whether you’re running dedicated physical hardware and software silos, virtualized database platforms or database grids. In each case database instances, sized to meet peak demands regardless if they are busy or idle, allocate and consume memory, CPU and storage resources.

Time to consider database consolidation

Today, most organizations have embraced server and storage consolidation which put more databases on fewer physical servers and storage systems. Surprisingly, few organizations are taking the next step of database consolidation (figure 2) and maybe missing an opportunity to further reduce cost and gain efficiency.

Database consolidation is nothing more than reducing the total number of databases by pooling and sharing database resources. The benefits of database consolidation are similar to server and storage consolidation - lower cost, reduced complexity and greater agility. Cost savings include both capital expenses for server, storage and software licensing and operational expenses for maintenance, management, energy and space. Complexity is reduced through fewer configurations and services as well as operating system and database version standardization. Greater agility is realized through rapid provisioning and response resulting in quicker time to market.

As with most solutions, there are no absolutes. Database consolidation may not be a good fit for vendor packages which require particular patch levels and applications which require complete physical isolation. However, for the majority of databases, consolidation makes good sense. Some guidelines for consolidation include applications with similar service level agreements, complimentary mixed workloads, SDLC environments and single application cloud services.

As organizations continue to look for opportunities to reduce cost and gain efficiencies, database consolidation can offer significant savings and simplicity. Understanding that database consolidation will require a paradigm shift from database isolation to database sharing is the first step. My advice is to figure out the right consolidation level for your organization, start small and consolidate over time.

Scalability, capacity skew colossal in SQL Server 2012

2011-12-03T13:37:00.000-08:00

Because issues of scale often arise in large data sets, most of the new features for scalability and capacity management in Microsoft’s upcoming SQL Server 2012 are aimed specifically at huge workloads: data warehousing, business intelligence (BI) and decision-support applications. Putting together apps like these is hard enough, but maintaining their data structures is a whole different ball game.

Column-store indexes

Column-store indexes are billed as the biggest scalability improvement in SQL Server 2012, now available in a community technology preview (CTP). A column-store index packs data for each column on a separate set of disk pages. This is in contrast with the traditional method of storing multiple rows per page.

Microsoft touts several advantages of using this alternate indexing method. For one, storing columns this way makes retrieving the columns needed for computing a particular query easy and fast. This is because the index compresses data; columns are on average more redundant than whole rows and therefore more compressible. Disk access patterns and caching allow the data to be pulled and retained much more quickly. The more columns you have in a given table or the more columns you plan to add to a given table, the better a columnar index will let that table scale up.

The advantages from a scalability point of view are clear. You can add capacity to a database without worrying about how that might affect the performance of BI queries, where the amount of data can run into billions of rows. Microsoft’s own preliminary work claims orders-of-magnitude speedup for various kinds of queries on such huge numbers of rows, depending on the types of data at hand.

One thing to note is that once you add a column-store index to a table, it becomes read-only. Specifically, you can’t use INSERT, UPDATE, DELETE or MERGE statements or use bulk operations to add data. My guess is this is a way to keep the column-store index from being repeatedly updated, thus killing the performance advantages of having one in the first place.

If you need to run queries without the column-store index (for instance, to determine the net performance gain) you can use the IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX option in aTransact-SQL (T-SQL) query -- the very name implies that Microsoft has plans to roll out clustered column-store indexes.

More table partitions

One way to add data to a table with a column-store index -- short of dropping the index, adding the data and recreating the index -- is to switch in a new partition for the table. By default, a table in SQL Server allows up to 1,000 partitions. SQL Server 2012 ramps up the limit to 15,000 by default, which makes it a lot easier to switch in partitions needed for massive operations. Apparently, Microsoft wanted to give users the option of using one partition every day; With 15,000 partitions, you can manage 41 years of data this way.

While adding partitions gives you more scaling flexibility, it doesn’t come without a cost. If you plan on using a lot of partitions, your database server should have at least 16 GB of random-access memory. This is not an outwardly enforced limit; SQL Server won’t explicitly refuse to mount a database with more than 1,000 partitions on a certain machine if it doesn’t detect some minimum amount of memory. But the more partitions you have, the greater the odds you’ll run into performance problems or memory exhaustion on queries.

If you want to use SQL Server 2012 CTP as a test bed for capacity planning with these new features in place, one thing to keep in mind is how the CTP handles statistics. Microsoft says, “Statistics [in the SQL Server 2012 CTP] are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the query optimizer uses the default sampling algorithm to generate statistics. … To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.” Microsoft claims these changes won’t affect query performance, but they can be problematic if the precision of your statistics affects any capacity or performance planning work.

These features were activated in SQL Server 2012 CTP3 and are not available in the earlier public CTP, which was released last November. Also, if you’re dealing with databases that still use the 32-bit x86 architecture, bear in mind that a table or index with more than 1,000 partitions is possible on those systems but it is not a supported configuration, thanks to Microsoft’s general push toward 64-bit servers.

AlwaysOn

AlwaysOn is Microsoft’s high-availability and disaster-recovery technology in SQL Server 2012. It uses a variant on the same “availability group” technology the company rolled into recent versions of Exchange.

I’m mentioning AlwaysOn here for two reasons. One, scalability and capacity planning will be affected by how you implement mirroring and clustering. The good news is that clustering in Windows no longer relies as heavily on homogenous hardware, so it’s a little easier to scale up (or out) and keep clustering working. Plus you won’t have to store everything in one place or use the same hardware across the board.

And two, AlwaysOn can be used to offload backups to a secondary replica as a way to ease the I/O and processing load on your main servers. If you haven’t given much thought to altering your backup infrastructure, this is a good excuse to do so. The end result: You can reduce the need to add capacity on the front end and scale out your back end much more efficiently.

FILESTREAM and FileTable

Another feature in SQL Server 2012 that may affect scalability and capacity is an improvement to the FILESTREAM feature introduced in SQL Server 2008. FILESTREAM allowed SQL Server to work directly with storage on the huge volumes that New Technology File System (NTFS) can hold, so binary large objects (blobs) can be stored as files on disk but addressed with T-SQL code. This lets SQL Server leverage NTFS for storing freeform data outside the constraints of the database engine -- a good way to enhance scalability if you previously shoehorned freeform data directly into databases.

SQL Server 2012 expands on FILESTREAM with FileTable, which does something even more interesting. It allows SQL Server applications to see the on-disk file system as a database table, write files to it and have other Windows applications -- not just other SQL Server apps -- recognize those files as well. FileTable uses a standard Windows share to expose the files in question to applications throughout Windows, so to get conventional Windows apps to work with them all you need to do is point them at the new share. FileTable’s even implemented as a Common Language Runtime class, so it can be accessed using .NET framework methods, too.

I should point out that while the new features in the current CTP are undeniably attention-getting, don’t count on using any of them in a production environment until SQL Server 2012is officially rolled out. If you want to get your feet wet with 2012, do it in an isolated fashion: Create a separate database instance on its own machine (or in a virtual machine) and work on copies of data, not originals. Microsoft has a history of offering remarkably polished technology previews -- the pre-beta editions of Windows 7, for instance, were astonishingly solid -- but that’s no reason to put your data or your database system’s integrity at risk.

Before database standardization, check skill sets, compatibility

2011-10-29T15:24:00.000-07:00

Enterprises that consolidate their business operations on as few database platforms as possible can reduce IT complexity and improve data quality, but experts say there are several cultural and technical issues to address before beginning a database standardization project.

For starters, organizations need to make sure they have the right skill sets in-house -- and not just the database skills. According to experts, IT workers need to understand how the chosen database standard will interoperate with supporting applications, hardware and the many workflows and business processes that fill a typical day.

“You’re going to run into silos of administrators who are tied to a particular stack, which is the combination of the database with the underlying platform, whether it’s the [operating system] or the hardware,” said Tony Iams, a vice president and analyst with Rye Brook, N.Y.-based technology research firm Ideas International. “The skill set on the back end is going to be critical.”

Pay attention to political and cultural issues

The decision to consolidate applications onto a single database standard can also cause interoffice political turmoil. Even if the business argument for consolidation is strong, Iams said, dealing with individual and group preferences within an organization can be a big challenge.

“The political questions are kind of the thorny ones because they go beyond technology [to] the heart of people’s allegiances and preferences.”

Beware of compatibility problems

Companies turn to database standardization and consolidation to clear up the confusion that results from having multiple database systems supporting multiple applications. End users point out, however, that this confusion often exists for a good reason, and standardizing on one platform can be highly problematic.

Enterprises need to run the right applications to fulfill business requirements, and sometimes those tools were designed with a specific underlying database technology in mind. As a result, more database platforms come into play when business requirements change over time.

That’s why anyone launching a database standardization and consolidation effort needs to stay on top of application migration and compatibility concerns. “If your application that your business depends on doesn’t support the new [database] then the whole idea is essentially a nonstarter,” Iams said.

It’s also a good idea to determine the total cost of the application migration well ahead of time to make sure the project will be worth the time and effort, said Christopher Carter, a data systems architect and the founder of C.L. Carter Co., a Nashville, Tenn.-based independent consulting firm.

“There would be a heavy migration labor cost that you would have to incur, and then you would have to figure what your return on investment will be over the next couple of years,” Carter said. “Usually, you’ll find [after] a year or two years that you’ll catch up and your return on investment will start to appear on the bottom line.”

Database standardization often a matter of semantics

Companies that choose to move source systems from one database platform to another as part of their standardization project should know that many differences exist between the major SQL-based systems, according to experts and technology professionals.

“Tip number one is brace yourself,” said Nathan Allan, a database developer and architect with Orem, Utah-based Database Consulting Group LLC. “There are many very subtle, sometimes semantic differences between the systems, and oftentimes people assume that the differences between the systems are superficial.”

One example of possible difference between SQL-based database management systemscenters on the way they handle empty strings. In some systems an empty string is synonymous with a null value, while in others it isn’t. That may seem like a tiny difference, but if gone unchecked, it could lead to unexpected downtime and lost business.

More differences between SQL-based systems can be found in their approaches to time-and-date functions, internationalization and the list goes on, according to Allan. “Despite a couple decades now of attempts at SQL standardization, we’re really not much closer than we’ve ever been.”

The task of identifying and quantifying the systems and applications that will be affected by database consolidation efforts can also present major problems. That’s why it’s important to allot plenty of time to make sure the job gets done properly.

“Oftentimes, you’ll have little fingers into your systems from all the different directions,” Allan said, “so actually determining the impact of moving a system is actually going to be is a project in and of itself.”

T-SQL Query Optimization in SQL Server

2011-10-26T15:55:00.000-07:00

Create index on any key column such as foreign key.

SET Transaction Isolation Level Read Uncommited at the top of SP

Use transaction when appropriate

Avoid temporary table if possible.

Always use profiling before and after of each change

Look for every possible way to reduce no of round trips to server

Avoid Index and join hints

Avoid functions in where clause

Every SQL query is broken down in to series of execution steps called as operators. Each operator performs basic operations like insertion, search, scan, updating, aggregation etc. There are 2 kinds of operators Logical operators and physical operators.

Creating covering index instead of Composite NCI

Unique column and preferred data types

The execution plan describes the sequence of operations, physical and logical, that SQL Server will perform in order to fulfill the query and produce the desired resultset.

Use SP instead of separate statements

Do not use * operators

Never use Computed by, cross join and cross join

Never use LIKE operator when there is an exact match

Make use of variables

Use two part naming conventions

Partition table and indexes

Use union all instead of union

Check out slow running query using query optimizer

Use SQL profiler for trace files and DTA for Advise for better performance

Author: Jatin Shah bidocumentation.blogspot.com

BI secret to using Excel in Microsoft SQL Azure: PowerPivot

2011-10-10T20:17:00.001-07:00

The folks at SQLServerPedia have a walkthrough that explains how to do this with nothing more than Excel and ODBC. It involves a specially crafted Excel spreadsheet that obtains data from the remote server and then updates it whenever there’s a change. Most of the magic is done through macros, so you don’t need to perform any additional commands. If you want a full-blown product that turns Excel into a data-editing front end, check out SaveToDB, which works with both SQL Server and SQL Azure and has a sophisticated way of handling the data interchange between the client and back end.

Understanding ABAP programming bits and bytes

2011-10-10T20:13:00.001-07:00

Bits and Bytes

Modern programming languages dosuch a tremendous job of abstracting the complexities of computer architecturesthat, these days, we seldom have any need to work at the bits and bytes level.However, with the advent of Unicode, it’s becoming more important to understandhow to work at this level because many external data sources encode their datausing multi-byte encodings — as opposed to the single-byte code pages normallyused in ABAP (e.g., ASCII, etc.). In addition, knowledge of this area can bequite handy in other applications, as you’ll see in a moment.

Binaryand Hexadecimal Numbers

If you have never worked withbinary or hexadecimal numbers before, then a brief introduction is in order. Abyte is a unit of measure for memory inside of a computer. Each byte iscomprised of 8 bits. The term bit is an abbreviation for binary digit. A bitcan have one of two logical values: 1 (or true) or 0 (or false). In terms ofcomputer circuitry, bits that have the value 1 are turned on, while those thathave the value 0 are turned off.

The binary (or base-2) numbersystem represents numeric values using binary digits. Figure 2 shows an exampleof an 8-bit binary number whose decimal value is 170. As you can see, readingfrom right to left, the value of each bit is calculated by multiplying one orzero (i.e., the bit value) by two raised to the power of the current index(where indexes start at zero).

2^7 2^6 2^5 2^4 2^3 2^2 2^1 2^0

= (2^7 * 1) + (2^5 * 1) + (2^3 *1) + (2^1 * 1)

= 170

Figure 2.8 Example of an 8-Bit Binary Number

Binary numbers can be verydifficult to work with if you’re not a computer. Therefore, the values of bytesare often represented using the hexadecimal (or base-16) numbering system. Eachhexadecimal digit is in the range [0123456789ABCDEF], where A = 10, B = 11, C =12, and so on. Conveniently, each hexadecimal digit can hold any possible valueof 4 bits (commonly called a nibble). Therefore, two hexadecimal digits can beused to represent a single byte of information in memory.

In addition to the fixed length Xdata type, ABAP also provides the XSTRING variable length hexadecimal type,which is commonly used in various input/output (I/O) operations. Here, as isthe case with the C and STRING data types described in Chapter 1, StringProcessing Techniques, there is a trade-off between performance andflexibility.

Now that you know a little bitabout the hexadecimal type, let’s take a look at the types of operations youcan perform on data objects of this type. The following sections describe thebuilt-in bitwise operators available in ABAP.

2.3.2Reading and Writing Individual Bits

You can use the GET BIT and SETBIT statements to read and write individual bits of a hexadecimal data object.The general syntax of these statements is shown in Listing 2.12 and Listing 2.13,respectively.

GET BIT lv_index OF lv_hex INTOlv_bit.

Listing 2.12 Syntax of GET BITStatement

SET BIT lv_index OF lv_hex TOlv_bit.

Listing 2.13 Syntax of SET BITStatement

To demonstrate how thesestatements work, let’s consider an example. Listing 2.14contains a contrivedpiece of sample code that swaps the first byte of a two-byte hexadecimal dataobject with the last byte by manipulating individual bits internally. For goodmeasure, we also shift the bits around one more time at the end of the codesnippet, using the SHIFT statement in bytemode.

DATA: lv_hex(2) TYPE x VALUE'F00F',lv_front_idx TYPE i,lv_back_idx TYPE i,lv_front_bit TYPE i,lv_back_bitTYPE i.WRITE: / lv_hex.DO 8 TIMES.lv_front_idx = sy-index.

lv_back_idx = lv_front_idx + 8.

GET BIT lv_front_idx OF lv_hexINTO lv_front_bit.

GET BIT lv_back_idx OF lv_hexINTO lv_back_bit.

SET BIT lv_front_idx OF lv_hex TOlv_back_bit.SET BIT lv_back_idx OF lv_hex TO lv_front_bit.ENDDO.WRITE: /lv_hex.SHIFT lv_hex BY 1 PLACES CIRCULAR IN BYTE MODE.

WRITE: / lv_hex.

Listing 2.14 Reading and WritingBits in ABAP

In and of itself, low-level bitmanipulation isn’t all that exciting. However, there are situations where itcan be quite useful.

For example, let’s imagine you’reworking on a problem where you need to work with arbitrarily large numbers thatexceed the limits of the built-in ABAP numeric types. One way other modernprogramming languages, such as Java or .NET, get around this limitation is bydeveloping a so-called numeric wrapper class. For instance, thejava.math.BigInteger class provided with the Java 2 SDK is used to representarbitrarily large integer values. Internally, bitwise operators are used to mimicthe behavior of a normal primitive type represented in two’scomplementnotation. Because this implementation is open source, it wouldn’t betoo difficult to reverse-engineer an ABAP version of this class to suit yourpurposes.

2.3.3 Bitwise Logical Operators

In addition to the GET BIT andSET BIT statements, ABAP also provides a series of bitwise logical operatorsthat can be used to build Boolean algebraic expressions. If you aren’t familiarwith Boolean algebra, there are many excellent resources available online —simply search for the term “Boolean Algebra,” and you’ll find a wealth ofinformation. Of course, even if you have worked with Boolean operators before,you might need a bit of a refresher. Table 2.4 depicts a truth table thatshows the values generated when applying the Boolean AND, OR, or XOR operators againstthe two bit values contained in Field A and Field B.

Field A	Field B	AND	OR	XOR
0	0	0	0	0
0	1	0	1	1
1	0	0	1	1

Table 2.4 Truth Table for BooleanOperators

Table 2.5 shows the bitwiseoperators provided with the ABAP language. Just like normal arithmeticoperators, the bitwise operators can be combined in complex expressions usingparentheses, and so on.

3 The two’s complement notationis a common system used to represent signed integers in computers.

Bitwise Operator	Description
BIT-NOT	Unary operator that flips all of the bits in the hexadecimal number to the opposite value. For example, applying this operator to a hexadecimal number having the bit-level value 10101010 (e.g., 'AA') would yield 01010101.
BIT-AND	Binary operator that compares each field bit-by-bit using the Boolean AND operator.
BIT-XOR	Binary operator that compares each field bit-by-bit using the Boolean XOR (or eXclusive OR) operator.

BIT-OR

Binary operator that compares each field bit-by-bit using the Boolean

OR operator.

Table 2.5 Bitwise LogicalOperators in ABAP

To see the power of bitwiseoperators such as the ones listed in Table 2.5, it’s useful to consider anexample. Imagine that you are tasked with building a custom document managementsystem. One of the requirements of this system is to be able to assign rightspermissions to the individual documents maintained in the system. For thepurposes of this simple example, let’s assume that the possible permissionsare Create, Remove, Update, and Display.

One way to store theseassignments might be to create a database table that contained a seriesof flag columns to indicate whether or not a user had a particular permissionfor a given document. Unfortunately, there are a couple of problems with thisapproach. First of all, it requires that we create separate fields for eachpossible permission type. As the system grows, additional permission type requiresa modification to the database table. This phenomenon leads into the second problem— namely, space. In other words, each additional flag column adds another byteor two of storage to every row in the table. Of course, another option is tocapture the permissions in separate rows. Still, either way you slice it, thiscan get expensive from a storageperspective.

Instead of creating a new flagcolumn each time we want to add a new permission type to our system, what if wecould figure out a way to store a bunch of Boolean flags in a single field?Naturally, the hexadecimal data type lends itself well to this kind of storageoperation because it can be used as a type of bit mask to represent alarge number of flags at the bit level. For example, a single byte bit maskcould represent up to 28, or 256, possible values, leaving us plenty of room togrow. The values of the individual Boolean flags can then be set using bitwiseoperators. Collectively, the process of representing a series of flags at thebit level and manipulating those flags using bitwiseoperators is referred to as bit masking.

The code excerpt in Listing 2.15demonstrates how bit masking works using the ABAP bitwise logical operators. Tokeep things simple, we’ve created an interface that contains constants torepresent the possible permission values (e.g., CO_CREATE,etc.). Thesepermission values are assigned to a display-only user using the BIT-OR operator,which effectively works like an addition operator in this case. We can thenconfirm whether or not the user has a given permission by applying the BIT-ANDoperator. Here, the result matches the permission constant bit-for-bit if theparticular permission has been assigned. This can be confirmed by using the equalityoperator in an IF statement. In the example, the user has Display permissionsbut not Create permissions.

INTERFACE lif_permissions. CONSTANTS:CO_CREATE TYPE x VALUE '01',CO_REMOVE TYPE x VALUE '02',CO_UPDATE TYPE x VALUE'04',CO_DISPLAY TYPE x VALUE '08'.

ENDINTERFACE.

DATA: lv_display_user TYPE x,

lv_permission TYPE x.

* Assign read-only access to adisplay user:lv_display_user =

lv_display_user BIT-ORlif_permissions=>CO_DISPLAY.

* Check the user'spermissions:lv_permission =lv_display_user BIT-ANDlif_permissions=>CO_DISPLAY.IF lv_permission EQlif_permissions=>CO_DISPLAY.WRITE: / 'User has display onlyaccess.'.ELSE.WRITE: / 'User does not have display access.'.

ENDIF.

lv_permission =lv_display_userBIT-AND lif_permissions=>CO_CREATE.IF lv_permission EQlif_permissions=>CO_CREATE.

WRITE: / 'User can createdocuments.'.

ELSE.WRITE: / 'User is notauthorized to create documents.'.

ENDIF.

Listing 2.15 Mapping PermissionsUsing Bit Masking

As you can see, bit masking canbe used as an effective compression technique. Other practical examples of bitmasking include the storage of user preferences and set operations, which aredescribed in an example in the online SAP Help Portal.

2.4 Summary

In this chapter, you learnedabout some advanced and perhaps lesser-known features of elementary data typesin ABAP. During the course of this book, you’ll see how some of thesefundamental concepts provide the foundation for implementing new features inSAP NetWeaverAS ABAP, such as support for Unicode and XML processing. In thenext chapter, we mix things up a bit and take a look at dynamic programming inABAP.

SAP NetWeaver Configuration and Customization

2011-10-10T20:05:00.001-07:00

One reason that NetWeaver exists is in order to allow you to configure and customize your SAP solutions with greater ease. Take advantage of NetWeaver configuration features to make your SAP and non-SAP applications more agile and interactive, and you could derive a number of technology- and business-related benefits. Remember that, if you configure NetWeaver in certain ways, you can achieve SAP customization more easily than in the past.

SAP NetWeaver Development Tips

NetWeaver is now standard SAP technology. In other words, it's what SAP uses to build SAP applications. However, there are two NetWeaver development environments: One is powered by ABAP, which is SAP's own development language, and the other is powered by Java. Java is more heavily emphasized in some NetWeaver contexts, such as the NetWeaver portal, certain types of Web Dynpro applications and NetWeaver Developer Studio, whereas ABAP predominates in other areas, such as NetWeaver PI. One of the raging debates in SAP circles is whether the SAP Java commitment will outweigh ABAP. SAP says that ABAP is alive and well -- and, indeed, the flagship SAP ERP is still built on ABAP -- but some observers believe that SAP Java is the way of the future. This is just a sampling of NetWeaver development tips that can be answered by our NetWeaver expert, Axel Angeli.

Extending SAP Value with NetWeaver

NetWeaver can be used to extend SAP solutions with custom components. This is an important development because, for much of its existence, SAP won a reputation as a software system that is highly resistant to easy customization. SOA is changing that, as the example of the recent SAP spell checker demonstrates. Thus, the development capability of NetWeaver should be actively approached as a way to tweak and customize SAP solutions.

The SAP Enterprise Service (ES) community is a marketplace for end users to buy and sell SOA- and NetWeaver-architected functionality. Before investing in in-house development of NetWeaver-powered applications and customizations, it's worth checking the ES marketplace to see whether what you need has already been posted by a community member.

SAP has targeted co-innovation and Web 2.0 as a higher priority in the years to come, and ES is just one of many ways that you can tap into the expertise of the SAP developer community to extend SAP value with NetWeaver.

SAP NetWeaver in Action: Case Studies of NetWeaver Users

NetWeaver has a number of enthusiastic end users. For example, Whirlpool used NetWeaver to good effect back in 2004.

Some general practices of SAP customers can be particularly helpful to NetWeaver adopters. For example, SAP end user Rosenthal USA achieved a successful SAP implementation by avoiding common pitfalls and adopting best practices. As a smaller company, Rosenthal USA offers helpful tips for negotiating with VARs and other strategies for driving down the price of an SAP implementation or upgrade.

There are also NetWeaver-pertinent lessons to be learned from Rubbermaid's user of SAP master data management and McKesson's focus on streamlining business processes as part of an SAP implementation. Remember, using or planning to use SAP products is always an opportunity to revisit and improve your business processes.

Command-line utilities for SQL Server database tuning, comparing tables

2011-10-07T20:48:00.000-07:00

Microsoft SQL Server 2008 R2 Unleashed

Chapter 5: SQL Server Command-Line Utilities

This section focuses on command-line utilities for SQL Server database tuning, such as the dta command-line utility and how it, along with its counterpart, the graphical Database Engine Tuning Advisor, does an analysis on a given workload and provides recommendations. And we’ll look at the tablediff command-line utility, which lets you compare the contents of two tables.

Table of Contents

SQL Server command-line utilities: sqlcmd
Command-line utilities for SQL Server database tuning, comparing tables
Using bcp utility for data moving, sqldiag for troubleshooting

The dta Command-Line Utility

dta is the command-line version of the graphical Database Engine Tuning Advisor. Both the command-line utility and graphical tool provide performance recommendations based on the workload provided to them. The syntax for dta is as follows:

Dta [ -? ] |
[
         [ -S server_name[ \instance ] ]
         {
{ -U login_id [-P password ] }
| –E }
{ -D database_name [ ,...n ] }
[-d database_name ]
[ -Tl table_list | -Tf table_list_file ]
{ -if workload_file | -it workload_trace_table_name }
{ -s session_name | -ID session_ID }
[ -F ]
[ -of output_script_file_name ]
[ -or output_xml_report_file_name ]
[ -ox output_XML_file_name ]
[ -rl analysis_report_list [ ,...n ] ]
[ -ix input_XML_file_name ]
[ -A time_for_tuning_in_minutes ]
[ -n number_of_events ]
[ -m minimum_improvement ]
[ -fa physical_design_structures_to_add ]
[ -fp partitioning_strategy ]
[ -fk keep_existing_option ]
[ -fx drop_only_mode ]
[ -B storage_size ]
[ -c max_key_columns_in_index ]
[ -C max_columns_in_index ]
[ -e | -e tuning_log_name ]
[ -N online_option]
[ -q ]
[ -u ]
     [ -x ]
     [ -a ]
]

An extensive number of options is available with this utility, but many of them are not required to do basic analysis. At a minimum, you need to use options that provide connection information to the database, a workload to tune, a tuning session identifier, and the location to store the tuning recommendations. The connection options include –S for the server name, –D for the database, and either –E for a trusted connection or –U and –P, which can be used to specify the user and password.

The workload to tune is either a workload file or workload table. The –if option is used to specify the workload file location, and the –it option is used to specify a workload table. The workload file must be a Profiler trace file (.trc), SQL script (.sql) that contains T-SQL commands, or SQL Server trace file (.log). The workload table is a table that contains output from a workload trace. The table is specified in the formdatabase_name.owner_name.table_name.which can be stored in a script file or in XML. The –of option is used to specify the output script filename. XML output is generated when the –or or –ox option is used. The –or option generates a filename if one is not specified, and the –ox option requires a filename. The –F option can be used with any of the output options to force an overwrite of a file with the same name, if one exists.

The tuning session must be identified with either a session name or session ID. The session name is character based and is specified with the –s option. If the session name is not provided, a session ID must be provided instead. The session ID is numeric and is set using the –ID option. If the session name is specified instead of the session ID, the dta generates an ID anyway.

The last options required for a basic dta execution identify the destination to store the dta performance recommendations,

To illustrate the use of dta with basic options, let’s look at an example of tuning a simple SELECT statement against the AdventureWorks2008R2 database. To begin, you use the following T-SQL, which is stored in a workload file named c:\myScript.sql:

USE AdventureWorks2008R2 ;
GO
select *
from Production.transactionHistory
where TransactionDate = ‘9/1/04’
The following example shows the basic dta execution options that can be used to acquire performance recommendations:
dta -S xpvirtual1 -E -D AdventureWorks2008R2 -if c:\MyScript.sql
-s MySessionX -of C:\MySessionOutputScript.sql -F

The preceding example utilizes a trusted connection against the AdventureWorks2008R2 database, a workload file named c:\MyScript.sql, and a session named MySessionX, and it outputs the performance recommendations to a text file named c:\MySessionOutputScript.sql. The –F option is used to force a replacement of the output file if it already exists. The output file contains the following performance recommendations:

se [AdventureWorks2008R2]
go
CREATE NONCLUSTERED INDEX [_dta_index_TransactionHistory_5]
ON [Production].[TransactionHistory]
(
[TransactionDate] ASC
)
INCLUDE ( [TransactionID],
[ProductID],
[ReferenceOrderID],
[ReferenceOrderLineID],
[TransactionType],
[Quantity],
[ActualCost],
[ModifiedDate])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

In short, the dta output recommends that a new index be created on the TransactionDate column in the TransactionHistory table. This is a viable recommendation, considering that there was no index on the TransactionHistory.TransactionDate column, and it was used as a search argument in the workload file.

Many other options (that go beyond basic execution) can be used to manipulate the way dta makes recommendations. For example, a list can be provided to limit which tables the dta looks at during the tuning process. Options can be set to limit the amount of time that the dta tunes or the number of events. These options go beyond the scope of this chapter, but you can gain further insight into them by looking at the graphical DTA, which contains many of the same types of options. You can refine your tuning options in the DTA, export the options to an XML file, and use the –ix option with the dta utility to import the XML options and run the analysis.

The tablediff Command-Line Utility

The tablediff utility enables you to compare the contents of two tables. It was originally developed for replication scenarios to help troubleshoot nonconvergence, but it is also very useful in other scenarios. When data in two tables should be the same or similar, this tool can help determine whether they are the same, and if they are different, it can identify what data in the tables is different.

The syntax for tablediff is as follows:
tablediff
[ -? ] |
{
-sourceserver source_server_name[\instance_name]
-sourcedatabase source_database
-sourcetable source_table_name
[ -sourceschema source_schema_name ]
[ -sourcepassword source_password ]
[ -sourceuser source_login ]
[ -sourcelocked ]
-destinationserver destination_server_name[\instance_name]
-destinationdatabase subscription_database
-destinationtable destination_table
[ -destinationschema destination_schema_name ]
[ -destinationpassword destination_password ]
[ -destinationuser destination_login ]
[ -destinationlocked ]
[ -b large_object_bytes ]
[ -bf number_of_statements ]
[ -c ]
[ -dt ]
[ -et table_name ]
[ -f [ file_name ] ]
[ -o output_file_name ]
[ -q ]
[ -rc number_of_retries ]
[ -ri retry_interval ]5
[ -strict ]
[ -t connection_timeouts ]
}

The tablediff syntax requires source and destination connection information to perform a comparison. This information includes the servers, databases, and tables that will be compared. Connection information must be provided for SQL Server authentication but can be left out if Windows authentication can be used. The source and destination parameters can be for two different servers or the same server, and the tablediff utility can be run on a machine that is neither the source nor the destination.

To illustrate the usefulness of this tool, let’s look at a sample comparison in the AdventureWorks2008R2 database. The simplest way to create some data for comparison is to select the contents of one table into another and then update some of the rows in one of the tables. The following SELECT statement makes a copy of the AddressType table in the AdventureWorks2008R2 database to the AddressTypeCopy table:

select *
into Person.AddressTypeCopy
from Person.AddressType

In addition, the following statement updates two rows in the AddressTypeCopy table so that you can use the tablediff utility to identify the changes:

UPDATE Person.AddressTypeCopy
SET Name = ‘Billing New’ WHERE AddressTypeId = 1

UPDATE Person.AddressTypeCopy SET Name = ‘Shipping New’, ModifiedDate = ‘20090918’WHERE AddressTypeId = 5

The tablediff utility can be executed with the following parameters to identify the differences in the AddressType and AddressTypeCopy tables:

tablediff -sourceserver “(local)” -sourcedatabase “AdventureWorks2008R2”
-sourceschema “Person”-sourcetable “AddressType”
-destinationserver “(local)” -destinationdatabase “AdventureWorks2008R2”
-destinationschema “Person” -destinationtable “AddressTypeCopy”
-f c:\TableDiff_Output.txt

The destination and source parameters are the same as in the previous example, except for the table parameters, which have the source AddressType and the destination AddressTypeCopy. The execution of the utility with these parameters results in the following output to the command prompt window:

User-specified agent parameter values:
-sourceserver (local)
-sourcedatabase AdventureWorks2008R2
-sourceschema Person
-sourcetable AddressType
-destinationserver (local)
-destinationdatabase AdventureWorks2008R2
-destinationschema Person
-destinationtable AddressTypeCopy
-f c:\TableDiff_Output

Table [AdventureWorks2008R2].[Person].[AddressType] on (local)
and Table [AdventureWorks2008R2].[Person].[AddressTypeCopy] on (local)
have 2 differences.
Fix SQL written to c:\TableDiff_Output.sql.
Err AddressTypeID Col
Mismatch 1 Name
Mismatch 5 ModifiedDate Name
The requested operation took 0.296875 seconds.

The output first displays a summary of the parameters used and then shows the comparison results. In this example, it found the two differences that are due to updates performed on AddressTypeCopy. In addition, the –f parameter used in the example caused the tablediff utility to output a SQL file that can be used to fix the differences in the destination table. The output file from this example looks as follows:

— Host: (local)
— Database: [AdventureWorks2008R2]
— Table: [Person].[AddressTypeCopy]
SET IDENTITY_INSERT [Person].[AddressTypeCopy] ON
UPDATE [Person].[AddressTypeCopy]
   SET [Name]=’Billing’
   WHERE [AddressTypeID] = 1
UPDATE [Person].[AddressTypeCopy]
   SET [ModifiedDate]=’2002-06-01 00:00:00.000’,
   [Name]=’Shipping’ WHERE [AddressTypeID] = 5
SET IDENTITY_INSERT [Person].[AddressTypeCopy] OFF

Keep in mind that several different types of comparisons can be done with the tablediff utility. The –q option causes a quick comparison that compares only record counts and looks for differences in the schema. The –strict option forces the schemas of each table to be the same when the comparison is run. If this option is not used, the utility allows some columns to be of different data types, as long as they meet the mapping requirements for the data type (for example, INT can be compared to BIGINT).

The tablediff utility can be used for many different types of comparisons. How you use this tool depends on several factors, including the amount and type of data you are comparing.

Three third-party tools for SQL Server

2011-10-07T20:36:00.000-07:00

SQL Server comes with a decently robust set of management tools, including ones for performance and tracing, that can be invaluable. Don’t believe for a moment, however, that SQL Server is fully equipped out of the box. Even if you’re just supporting the SQL Server database that comes with a packaged software system, there are a few specific third-party tools you should have on hand to make SQL Server more reliable, more secure and easier to manage.

First up is the SQL Admin Toolset from Idera. This is a package of two dozen tools designed for monitoring, troubleshooting and managing one or more SQL Server computers. You get a unified console that makes accessing each tool easy, including tools for checking SQL Server’s patch status, analyzing index performance, moving databases, quickly reindexing tables and more. And priced at under $300, it’s affordable, too.

Next in third-party tools is AppAssure’s Replay product. This is a generic backup and recovery tool, but it doesn’t have SQL Server-specific functionality. Replay is different than the time-based snapshot backups you’re probably used to. Rather than grabbing a full or differential backup every night, Replay uses an agent that’s installed on SQL Server itself to grab every disk block that changes as SQL Server writes data to disk. That includes database and transaction log files. Changed blocks are replicated immediately to a separate backup server, which stores the blocks.

In the event of a total failure, you can get a database back up and running in a few minutes, as Replay can also restore blocks as needed by SQL Server. But beyond dealing with total failures (which, admit it, don’t happen every day), Replay can also mount its backup data as a live Microsoft SQL Server database that can be queried, enabling you to restore anything down to a single row of data.

What’s more, Replay can mount any given point in time as a live SQL Server database, meaning you could retrieve a row that was deleted at 3:34 p.m. yesterday afternoon if you needed to. There are tools from other vendors that have this same basic approach, and they tend to cost hundreds of dollars, not thousands, so they offer an affordable alternative to tape-based backup. The backup server itself can be dumped to tape periodically, allowing you to maintain your off-site backup rotation schedule.

Last up is Capacity Manager for SQL Server from Quest Software. If you have more than a few SQL Server machines to worry about, this is a great way to get a handle on database growth, disk space utilization, index maintenance and other capacity-related issues. More and more companies are trying to consolidate Microsoft SQL Server databases into fewer machines and often rely on virtualization to do so. Quest’s tool helps by giving you a centralized view of your SQL Server databases, letting you see your total storage needs, project growth and make smart decisions when it comes to consolidation.

All database administrators -- no matter how “reluctant” -- have their favorite tools, and these are some of mine. Anything that helps me get a better handle on SQL Server utilization and performance and that helps keep SQL Server safe and reliable, is a big help to me every day.

Four tips on boosting SQL Server scalability

2011-10-07T20:33:00.000-07:00

Scaling out a SQL Server environment across multiple systems can be a difficult and complicated project, involving partitioned databases, federation and more. So, when it comes to SQL Server scalability, most organizations prefer to scale individual systems up as much as possible before trying to tackle the out option. Here are four tips for making the scale-up process easier and more effective.

Maximize SQL Server performance components. Every server’s performance comes down to four basic components: memory, disk storage, network adapters and CPU. Your first step will be to maximize all four of these components in an existing server.

Start with memory – it can have the biggest impact and it’s usually the most easily expanded piece of a server. There is just one limitation: On servers running a 32-bit version of Windows, there’s no reason to have more than 4 GB of total RAM installed, because the operating system can’t make use of more than that. On 64-bit machines running 64-bit versions of Windows and SQL Server, install as much RAM as the server can hold to get the best performance from SQL Server.

In fact, if you have any SQL Server instances running on 32-bit versions of Windows, migrate them to a 64-bit machine as your very first step, because enabling your systems to address that additional memory is one of the biggest performance improvements you can make. And don’t be cheap on memory; buy whatever your server manufacturer recommends, which is often more expensive, error-correcting, high-speed memory. It’s worth the expense.

Storage is the next thing you’ll look at. It’s a big topic, and it takes up its own tip later in this article. Suffice to say that faster storage performance is always a good thing.

Network connectivity is third. Plenty of SQL Server computers get performance-bound at the network adapter level. If you can afford them, multiple network adapters will provide multiple paths to your network. Gigabit Ethernet (GbE) adapters should be a minimum for SQL Server computers, and if your network can support 10 GbE, then go for it. It’s especially important that your computers use a separate network and have at least one network adapter for every major use.

For example, if you’re relying on iSCSI for storage communications, data transfers should be happening over a dedicated network interface controller (NIC) and a dedicated network, not over networks that are shared by client traffic.

Finally, examine your server’s processors. They are last on this list for a reason: It’s rare that you can upgrade them in a cost-effective fashion. That’s because of the way processors are matched to their motherboards, which typically are designed for a specific family and generation of CPUs. To install significantly faster processors, you often have to get a new motherboard – and that usually means new memory and new everything else. In other words, a whole new server.

Adding more processors, if your server has room for them, will add some kick – with upgrades, more is always preferable to slightly faster. But here again, your options might be limited: Most servers are purchased with fully populated sockets, leaving no room for additional processors.

Virtualize when it makes sense. Believe it or not, virtualization can be a clever way to bring about performance benefits from SQL Server. It seems counterintuitive; after all, the point of virtualization is to run multiple workloads on a single host computer. So, by dedicating a single computer entirely to SQL Server, wouldn’t you get better SQL Server performance?

Generally speaking, you would. If you have one SQL Server database that can keep an entire physical server completely occupied, then do just that. Many organizations, however, load their SQL Server systems with multiple databases or even multiple instances. There’s nothing wrong with that – it’s what SQL Server was designed for, in fact – but it doesn’t give you much flexibility for matching workloads with the available hardware resources.

Instead, move those databases that need less than an entire physical server into a virtual machine. SQL Server is a great virtual-machine guest application, and by separating your databases across multiple SQL Server instances, each hosted in different virtual machines, you gain flexibility. Using live migration technologies, you can quickly move virtual machines from host to host, rearranging the virtual machines to best utilize available hardware based on current workload demands.

You can pull off similar tricks with clustering technologies, including Windows Cluster Service. Partition your databases into different clustered SQL Server instances (a SQL Server instance is, after all, a form of virtualization). You can then move those instances around at will, with very little interruption in database availability. If Database A needs to be scaled up one afternoon, shift other instances off of that database’s cluster node, freeing up resources for use by the instance that’s getting hammered. This kind of dynamic scaling can produce impressive performance results, although it does require your organization to develop a mature performance monitoring and response model.

If you’re going to be spending big money on new server hardware, it makes sense to do so in conjunction with some kind of shared-resources scheme, such as clustering or virtualization. That way you’ll have your expensive new hardware working as close as possible to its maximum capacity at all times while still delivering the performance your users require.

Upgrade storage performance. The size of your storage systems is driven by the capacity needs of your databases; the speed of those storage systems is something too often overlooked. In the SQL Server world, disk storage speed is king. SQL Server is most likely to become I/O-bound before memory, network adapters or processors become an issue. You want storage that’s fast. That means putting storage area networks (SANs) on the other end of hyper-fast fiber-optic connections, and using fast SAN protocols such as iSCSI to communicate with your disks.

Pay close attention to the exact kind of workload your database handles and match storage technologies such as RAID to that workload. RAID 5, for example, offers recoverability in the event of a device failure, but it can slightly slow down write times because the extra striping information, which enables data recovery, must be written along with each update.

Fast disk-controller electronics can help resolve that issue by caching data as fast as the server sends it, and then quickly dumping the data to platters. Every element of the storage subsystem plays a crucial role in performance – platter rotational speed, raw device I/O, average seek times, communications media (copper or fiber). Work with an experienced storage vendor to put together a system that offers the best performance for SQL Server.

If money is an issue, skimp on processor speed, and even memory, before you skimp on storage. Storage performance will go a long way toward scaling SQL Server up, up, up.

Invest in new servers. At some point, you’re going to look at your existing servers and realize that you can’t squeeze any more performance out of them. Your storage I/O is as fast as it’s going to get. You’ve maxed out the memory. Every processor slot is full. The boxes are bristling with 10 GbE NICs, and they have enough cooling fans to power a hovercraft. In those cases, it’s easy to talk yourself into new servers (although your chief financial officer might still argue the point with you).

What’s tougher is to look at a server that clearly has room for expansion and decide to ditch it for a new one. When you’re looking at empty memory slots, available CPU sockets and desolate PCI backplanes, it seems much more cost-effective to start filling in those holes with new, performance-enhancing components.

Sometimes that’s a great idea. Sometimes you need to resist the urge.

First of all, never spend a dime upgrading a 32-bit computer. Replace it with a 64-bit one and install 64-bit versions of Windows and SQL Server. Load the new server with RAM aplenty, four or more multicore processors, and fast new NICs.

One last thought on SQL Server scalability: Any server more than four or five years old should be replaced, not upgraded. The cost to add memory or processors to an older server is often too close to what a brand-new server would cost, and a new server comes with lots of performance upgrades you couldn’t buy a la carte: faster BIOS circuits, faster chipsets, faster memory bridges. That old server can still find useful life as a file server, or it can handle some other, less-intense workload than running SQL Server.

Moving beyond SSIS: 5 third-party SQL Server tools for ETL

2011-10-07T20:30:00.000-07:00

If most of your data resides in SQL Server databases and your source data is readily accessible, chances are you’re using SQL Server Integration Services (SSIS) to perform your extract, transform and load (ETL) operations. SSIS comes free with SQL Server, it’s easy to install and it’s an excellent ETL tool.

Yet, many organizations are going with third-party ETL tools, even licensed SQL Server shops. Some might be familiar with a specific product; others have a lack of knowledge about SSIS or have data management needs that SSIS cannot easily address, such as enterprise resource planning (ERP) and customer relationship management (CRM) capabilities.

There’s certainly no shortage of products out there. In fact, there are so many ETL tools that selecting one can be a daunting task. With that in mind, I looked at five of the more popular products for this ETL tools comparison.

In selecting these tools, I reviewed numerous online discussion threads and article comments to get a sense of which products are generating the biggest buzz. I eliminated any tools affiliated with a specific relational database management system, such as Oracle Warehouse Builder. Note, however, I’m not endorsing or recommending any one product, nor am I presenting them in any particular order. I’m simply providing an overview of what I’ve discovered.

Informatica PowerCenter

Few products garner as much attention as Informatica PowerCenter. With its easy-to-use interface and service-oriented architecture, PowerCenter supports data migration and consolidation, replication and synchronization, and data warehousing. You can use PowerCenter to retrieve data from most business systems and deliver that data throughout the enterprise in batches or in near real time, real time or on demand.

The product also supports comprehensive reporting and auditing capabilities. In addition, development teams throughout the organization can share and reuse data definitions and data mapping logic across projects and platforms. Because of PowerCenter’s metadata-driven architecture, definitions can be standardized, and technical and business metadata can be integrated into a single data integration catalog.

SAS Enterprise Data Integration Server

Data integration is the name of the game with SAS Enterprise Data Integration Server. With its integrated development and workflow structure, this ETL tool lets you access a variety of databases, enterprise applications, mainframe data sources, message-oriented middleware, structured and semi-structured data, static and streaming Web data, and numerous file types -- all using both native formats and open standards.

You can also migrate and synchronize data between databases, applications, mainframe legacy files and other sources. Because of its multithreaded parallel processing architecture, Integration Server lets you move data quickly and efficiently, and there’s plenty you can do with that data. The transformation library includes more than 300 predefined table and column transformations, and during the data conversions, you can capture and document metadata and data integration processes. A common metadata repository supports centralized storage and process management so you can reuse work and minimize development efforts.

BusinessObjects Data Integrator

When organizations are looking for a system to address their ERP, CRM, business intelligence (BI), and data migration needs, they often seek out products likeBusinessObjects Data Integrator. The product provides a single view of enterprise metadata and supports advanced data profiling so you can understand the structure, content and quality of your data. This metadata integration lets you perform end-to-end impact analyses that show you how changes to the source data can affect the ETL and BI environments.

The integration also lets you audit data throughout the ETL process. Another benefit of the metadata integration is the product’s inherent data lineage, which lets users see how data was computed, when it was updated and where it originated. Like other products, Data Integrator supports team-based development and provides a central metadata repository.

Pentaho Data Integration

Many organizations now turn to open source technologies to meet their ETL needs. One of the most popular of these products is the Community Edition of Pentaho Data Integration, also known as Kettle. This product takes a metadata-driven approach to creating complex jobs and transformations in a drag-and-drop GUI environment. As a result, you don’t have to generate custom code, and you have access to ERP connectors, data quality plug-ins, and over 150 out-of-the-box mapping objects that support advanced warehousing components such as slowly changing dimensions and junk dimensions.

Kettle also provides a unified ETL, modeling and visualization development environment that is built on a scalable, standards-based architecture. Note that the Community Edition is a self-supported product. For technical support, managed upgrades and enterprise features, you’ll have to upgrade to the Enterprise Edition.

Talend Open Studio

Another popular open source ETL product is Talend Open Studio. Like Kettle, Open Studio is a metadata-driven solution that supports data migration, integration, and synchronization. Open Studio takes a top-down approach to business modeling that lets line-of-business stakeholders participate in the design of integration processes and monitor the development of those processes.

The development environment provides both a graphical and functional view of the integration processes and includes a graphical palette of open source components and connectors that address all types of tasks and operations. You can also track data throughout the transformation processes and automatically generate technical reference documentation. In addition, Open Studio supports comprehensive connectivity to packaged applications such as ERP and CRM solutions as well as to data warehouses and online analytical processing (OLAP) applications.

Choosing a Third-Party ETL Tool

As you can see, third-party ETL tools can be quite varied, and in addition to the ones I’ve described here, there are many more from which to choose. If you’ve already implemented SQL Server in your organization, it’s worth the effort to try to make SSIS work. However, if you’re data management needs go beyond the ETL functionality available in SSIS, you might consider one of the many products out there.

Before you select a tool, however, assess the types of business systems from which you’ll be extracting data and analyze the specifics of that data. You’ll also need to determine what functionality beyond your basic ETL requirements you’ll need to implement in your system. For example, you might need to incorporate ERP and CRM capabilities. You’ll also want to determine whether your organization is willing to work with open source. Some stakeholders are not comfortable with implementing open source technology. In the end, however, it’s up to you to do the evaluation necessary to determine the best ETL tool for your organization.

Microsoft SQL Azure just pie in the sky? Think again

2011-10-07T20:26:00.000-07:00

The reality, though, is that SQL Azure -- the version of SQL Server that lives in Microsoft’s Azure cloud computing service -- is a pretty important product.

Especially for organizations that only use SQL Server to support third-party applications, cloud-based databases promise less overhead, more turnkey operations and other advantages. There are potential downsides, of course, but this column isn’t about whether Microsoft SQL Azure (or any cloud service) is right for you; it’s about what the “reluctant DBA” needs to know if his organization wants to push their data into Azure.

The neat thing about Microsoft SQL Azure is that, by and large, you manage it a lot like you would a local SQL Server installation, only you don’t worry at all about the actual hardware. You still create logins; you still have databases; you still rebuild indexes. Azure isn’t like the “shared hosted SQL Server” that hosting providers have offered for years; with Azure, it doesn’t look like you’re sharing anything with anyone. In many respects, it’s as if some Microsoft ninjas broke into your data center, picked up your SQL Server computers, and carried them off to some other data center. Apart from the location of the hardware, not much changes.

That said, the Azure team -- like most of Microsoft these days -- is buying into Windows PowerShell in a big way, and the SQL Server technology teams are continuing to expand their support for, and reliance on, this task automation framework. So if you’re planning to automate any of your SQL Server tasks, start wrapping your head around PowerShell now.

If, however, you’re only looking to manage and maintain a few Azure-based databases from time to time, then the graphical user interface tools you know and love can continue to do the job. The allure of Azure, aside from moving the computing activity out of your own data center, is that it can make your data available anywhere in the world through the back-end magic of the platform itself.

What Microsoft SQL Azure doesn’t do is change how you plan, secure, maintain or troubleshoot your databases. Backups don’t even become less critical: You’re not likely to face a disaster recovery scenario with Azure, but you still may want the ability to roll a table back to a specific point in time to undo some unwanted change. Backups are still the key to that.

From a DBA perspective, you’ll face the following tasks:

Rebuilding and reorganizing indexed on a regular basis. Azure doesn’t change the way indexes work or how SQL Server uses them.
Grabbing backups with whatever frequency is appropriate for your organization.
Managing logins and database users, as well as database roles, application roles and other security principals within SQL Server itself.

If you’re already used to performing these tasks in a local SQL Server installation, then performing them in an Azure-based database won’t be too much of a challenge. That’s actually one of the biggest selling points of Microsoft SQL Azure compared with other companies’ cloud offerings: It works (more or less) just like the SQL Server product you’re already used to.

SQL Server BI in the cloud: feasible or fantasy?

2011-10-07T20:24:00.000-07:00

As surely as business intelligence (BI) and cloud computing have become marketing buzzwords, the two IT trends have indeed come together. An increasing number of vendors are offering cloud-based business intelligence systems, including SQL Server BI in the cloud, often targeting them squarely at small and medium-sized businesses (SMBs).

Whatever you think of “the cloud,” BI in the cloud is something you have to pay attention to if you’re considering implementing a BI system. Essentially, you’re looking at a Software as a Service (SaaS) technology not unlike Salesforce.com, which is arguably the poster child for cloud services. The advantages of a BI system in the cloud are numerous:

You don’t install or implement anything (well, almost -- keep reading) in your own data center. You just “turn on” the service one day and you’re up and running. Sort of.
You don’t need to worry about outfitting the right kind of server with tons of memory, storage and processors -- the service provider handles it.
You don’t need to know a thing about building a BI system, because it’s already built. You’re buying a more-or-less “prepackaged” solution, in much the same way that you probably bought your accounting and customer relationship management (CRM) software.
You will often be looking at a smaller -- possibly zero -- capital investment. Instead, you simply pay monthly or annual service fees based on your usage of the SQL Server BI system in the cloud.

That last bit is where you need to be careful: How is usage metered, and what will you pay for it? Some providers charge based on the number of users who will be utilizing the BI system; others charge based on how much data you’re storing, while still others use some combination of these and possibly other factors. The cost factor is always the big warning point in a SaaS system, because it isn’t always as straightforward as the host-it-yourself solutions we’re all used to.

Another point of caution -- or at least an area you need to learn -- concerns the fact that BI systems, by definition, need to integrate pretty tightly with your data center, something SaaS solutions haven’t typically needed to do.

A cloud-based CRM system, for example, may not need to integrate with any of your other back-end systems (although more of them are gaining the ability to do so). A BI system, by contrast, is useless if it can’t access a wide range of your existing corporate data.

Today, cloud BI providers often achieve this integration by requiring some infrastructure to be deployed in your data center -- often in the form of agents that collect data from your existing systems.

Some BI providers are capable of collecting data from cloud-based storage, such as Windows Azure or Amazon’s EC2/S3 infrastructure, but not many companies are storing their back-end data (things like sales figures and financials) in the cloud -- at least not yet.

Some providers require you to do a traditional extract, transform and load (ETL) cycle to upload your data to their servers for analysis; others enable your live data to be connected to the BI system through agents, connectors and other software components.

How your data gets to the cloud is the big question, and it’s the main area where today’s cloud BI vendors differentiate themselves. Make sure you focus on that area when evaluating systems, along with the more obvious user functionality like dashboards, reports, scorecards and the like.

Securing an Apache Web server with SELinux

2011-10-07T20:06:00.001-07:00

The chances of having your Web servers hacked are real, but SELinux can be used to make sure that your website doesn’t suffer real damage.

You can use SELinux types to create an exact definition of what a service can do and where it can do it. By default, the httpd_sys_content type is set to /var/www, which defines that the httpd process is allowed to work from this directory. If a hacker breaches the Apache Web server and tries to write somewhere else—like the default /tmp to Red Hat and similar Linux distributions such as Fedora and CentOS. But you need to know what you’re dealing with. If you configure Apache to serve content in the /data directory, SELinux would prevent that by default too.

Because of the complications that these default settings may introduce, many Linux administrators disable SELinux. And while it’s true that disabling SELinux makes it easier to provide service on your server, it also increases security risks.

Managing SELinux Settings for ApacheManaging SELinux settings for services like Apache is not hard. The problem is that there are no easy graphical tools that allow you to do a quick setup. But by applying just three commands, you can configure it.

To set the file type on the directory that you want to give your service access to, you must first determine which file system type to use. To do that, type ls –ldZ on the default directory that your service uses.

is set (httpd_sys_content_t). This also is the file type that you need to set as your new document root.

For Apache, you would use ls –ldZ /var/www. You’ll notice that in this cases, the –Z option gives additional file properties, and the –t option is the one that matters. This process defines the current file system type to which the Apache environment

There are two commands that you can use for setting your content type: with chcon you can make a temporary change—which disappears after a reboot—and with semanage, followed by restorecon, you can make the changes permanent.

The semanage command may seem complicated, but it’s actually quite simple because you need to change only the type you want to use and the target directory. In the example below, there are just two parameters you would need to change. The following shows that the rest of the example command can remain exactly as it is:

semanage fcontext -a -t httpd_sys_content_t /web(/.*)/?

After using semanage to set the default file type, use the restorecon command to make sure that it is applied.. In the example above, where the file type for the directory /web is changed to allow Apache to server files from that directory, run the following command to apply the changes:

restorecon -R -v /web

At this point, Apache will be able to serve files from the new nondefault document root directory.

Managing Booleans for SELinuxAnother aspect of SELinux that youneed to manage are the SELinux Booleans. These are binary values that switch certain functions on or off. Booleans are available for many services. Use the getsebool –a command to get an overview of all the existing Booleans . This command normally gives a long list of settings that you can apply.

To find all Booleans for the service you want to configure, pipe the output of getsebool –a through grep. For instance, use getsebool –a | grep http to find all Booleans that match the string http. Even without having a clear understanding of each Boolean, you’ll often find out by just looking at their names what they are supposed to be doing.

Use getsebool –a to find out which SELinux Booleans are available to modify behavior of your services:

[root@bia Desktop]# getsebool -a | grep http allow_httpd_anon_write --> off allow_httpd_mod_auth_ntlm_winbind --> off allow_httpd_mod_auth_pam --> off allow_httpd_sys_script_anon_write --> off httpd_builtin_scripting --> on httpd_can_check_spam --> off httpd_can_network_connect --> off httpd_can_network_connect_cobbler --> off httpd_can_network_connect_db --> off httpd_can_network_relay --> off httpd_can_sendmail --> off httpd_dbus_avahi --> on httpd_enable_cgi --> on httpd_enable_ftp_server --> off httpd_enable_homedirs --> off httpd_execmem --> off httpd_read_user_content --> off httpd_setrlimit --> off httpd_ssi_exec --> off httpd_tmp_exec --> off httpd_tty_comm --> on httpd_unified --> on httpd_use_cifs --> off httpd_use_gpg --> off httpd_use_nfs --> off

After learning which Booleans you want to use, apply them using setsebool -P. If you want to allow Apache to use nfs, use setsebool -P http_use_nfs on. For a complete list of all Booleans that are available, consult the httpd_selinux man page, which explains Apache specific file labels as well as Booleans.

Top five new features in Windows Server 8

2011-10-07T20:03:00.000-07:00

Earlier this month, Microsoft announced details about what’s new in the upcoming version of its Windows Server 8 operating system. So what are the top five most useful or innovative features? I’ve broken them down here.

1. Hyper-V replication. Disaster recovery preparation is challenging. There is a storage cost, and also a lack of storage flexibility; for example, you can't replicate from SAN to DAS systems, or file-based to block-based storage systems. There are also issues surrounding networking capacity and requirements, in particular, the time to initially replicate large datasets.

Hyper-V Replica aims to change all of that with in-the-box, asynchronous, application-consistent VM replication with data compression and encryption. Hyper-V Replica includes support for Windows Integrated and certificate-based authentication, and both offline and online initial replication. It also comes with a decent management API and solves many of the problems with spinning up VMs over long distances, for both disaster-recovery and standard operational reasons.

2. A GUI that can switch on and off without reinstall. In the past, there were only two choices when installing Windows Server 2008 R2: a full installation, with the standard graphical user interface; or the Server Core installation option, which is basically a headless version with only a command prompt. In Windows Server 8, it's simple to move between Server Core and full server by ticking a box once the server is installed. For novice administrators or poorly-coded software that doesn’t offer a command-line or remote installation option, just turn the GUI on, install the problematic software and then flip the GUI back off.

3. A full IP address management solution in the box. Most enterprises are way behind in this area: you have your internal IP ranges in spreadsheets. What happens when you get a new device and need to put it on the network? You scan your spreadsheet, hoping to find an available address that fits in your addressing scheme – x-to-y for servers, z-to-n for network equipment, and so on. Windows Server 8 brings your spreadsheet into a nice, visual database with support for automatic network discovery, automatic IP selection based on a device and regular audit and compliance reports to ensure there’s no more management nightmare when it comes to assigning IPv4 addresses to your infrastructure.

4. New Hyper-V virtual switch. In Windows Server 8, Microsoft is introducing the Hyper-V virtual network switch, which essentially gives administrators very tight control over networking between virtual machines and the physical network fabric. The virtual network switch will support applications like deep content security and filtering, traffic monitoring and analysis, tight integration with existing network infrastructure and virtual appliances already in production, as well as a customized management interface. Additionally, partners can write software extensions for the virtual switch that allow for the capture, filtering, and forwarding of all traffic—whether it originates physically or from a virtual machine—enabling any number of intrusion detection, compliance and access control scenarios while also allowing the flexibility to run these types of workloads in virtual machines.

5. Over 2,300 PowerShell cmdlets are introduced in Windows Server 8. Essentially, the world is intrinsically messy because of existing systems and rapid innovation, leaving administrators with no great way for any one management system to pull everything together into a cohesive whole. PowerShell transforms a messy world of non-woven systems and difficult integration into a world of high-level, task-oriented commands that are easy to understand and follow. Microsoft sees Powershell becoming a system administrator’s automation tool of choice in cloud and virtualized environments, and has made the scripting language into an automation engine with API, CLI, and remoting interfaces.

In-house BI implementation and development lessons from Yes Bank

2011-09-23T14:11:00.000-07:00

A business intelligence implementation should act as an impetus rather than a delay in the acquisition of useful information. To respond rapidly to this information need, we at Yes Bank realized a while back that an in-house developed BI solution may be the best bet. Here are some pointers on how you can develop an efficient in-house BI solution by leveraging internal resources.

1) Articulate the need: Today, BI is used not only for operational reporting and dash-boards, but also to get profitable returns from an organization’s data. So there needs to be a clear view of what is expected from this data. BI implementation should help to acquire useful information to deliver the desired results.

The requirements of a company vary, depending on its size and scale. These requirements play an important role in BI implementation. The role of data in every organizational function should be carefully analyzed to ensure that the solution meets its requirements. The person in-charge of the function should get precisely what he needs to base his business decisions on, and not be loaded with excess data. If this need does not coincide with the provided data, the BI implementation should lend itself to minute scrutiny of the issue. Here, development of an in-house BI can provide greater value than having a generic platform.to market changes promptly. This does not mean that you can expect plans to deliver in the next three to four months. In Yes Bank’s case, our in-house BI solution has been very agile in responding to business needs. It took us around 18 months to deploy the process, and more than 200 reports were put in place.

2) Define short, medium and long-term goals: Technology should be driven by business. Based on existing demand, your BI implementation should be quick enough to equip the business to respond.

The short, medium and long-term goals should be well-defined. It’s recommended to have a short-term need that can be managed in-house and later merged into a long-term goal. These two can also run parallel to each other. The solution should have scope to evolve with business needs. We need to move from point A to point B; for this, an in-house BI solution is what you may need.

3) Measure the return on investment (RoI): We deployed the BI solution at a cost of USD 2,00,000. If the cost of BI implementation is going to overbear the profit to be derived from it, an in-house developed and managed BI does the trick. For example, if a taxi ride does the job for me, why should I buy an expensive car for the same? Thus, measure return on investment (RoI) of the deployment with respect to your in-house capabilities. Industrial knowledge is key to successful BI implementation — not expensive and fancy technology.

4) Evaluate the potential: BI cannot be restricted to only data warehousing. Business intelligence is not just about extraction, cleaning and storing information in a data mart to be produced as per requirement. Delivering information is one part of BI, but putting that information into action is the crucial part. We wanted an action-oriented BI implementation that records action taken on the generated information since it will show as an error or exception otherwise. The level of customization depends on the action to be taken from the BI data. This kind of role-based business intelligence approach can very well be developed in-house.

Define important BI implementation milestones as per your needs. This will help you clearly chart the progress of your in-house development team.

5) Select the right vendor: In-house BI implementation also requires technology partners and hence, it is necessary to thoroughly evaluate vendors for the same. You could choose new vendors or partners you have had a past association with. However, this need not bethe cornerstone of BI implementation. Vendor choice should be more of a wisdom pool. It should help you adapt your own solution in such a way that you adapt best practices while avoiding the faux pas of available solutions.

We used the programming platform LogiXML for Yes Bank’s BI implementation. The first phase – Hindsight – involved getting our processes in place. The second phase – Insight – involved system abilities to analyze data. These sufficed our needs at the time.

As we have grown since then, we aim to completing the fourth phase of our BI deployment – Line of Sight — by next year. This phase will provide us with forecasting capabilities through the use of certain templates. The third phase – Foresight – involves systems and advanced analytics that we do not currently need. So we will not implement it.

Sound system architecture helps avoid SharePoint 2010 project pitfalls

2011-09-23T14:06:00.000-07:00

Anyone who has ever deployed SharePoint 2010 understands that a lot of planning needs to go into the process. Even so, unforeseen issues can arise during deployment. More often, such issues do not become apparent until long after the deployment process has been completed and the SharePoint collaboration platform is being used across an enterprise.

These latent concerns, which are annoying at best and can be downright problematic at times, catch many people off guard because using the SharePoint 2010 setup wizard is relatively painless. If you’ve done even a modest amount of planning, you’re unlikely to encounter a deployment issue so severe that it directly impacts the setup process. Instead, most of the issues that people experience are tied to migrations from previous versions of SharePoint or to resource management tasks. This article will focus on how to avoid some of the challenges and pitfalls common to SharePoint 2010 deployments that can affect resource management on an ongoing process.

When preparing to deploy SharePoint 2010, it is important to consider the hardware on which SharePoint will run. It’s easy to assume that if a server was previously able to run SharePoint 2007, then it must be adequate for running SharePoint 2010. However, SharePoint 2010 is more demanding than SharePoint 2007, requiring more memory and greater processing power. For example, the minimum memory required for a SharePoint 2007 application server was 2 GB, and Microsoft recommended that the server have at least 4 GB. In contrast, the minimum memory for a SharePoint 2010 application server has doubled to 4 GB, with 8 GB recommended.

From a processing standpoint, the minimum requirement for a SharePoint 2007 application or Web server was a 2.5 GHz CPU, with Microsoft recommending a dual-core processor. The minimum clock speed hasn’t changed for SharePoint 2010, but Microsoft now requires a quad-core CPU for both types of servers.

The chart below illustrates how SharePoint’s requirements have changed:

	SharePoint 2007 Web Server	SharePoint 2007 App Server	SharePoint 2010 Web Server	SharePoint 2010 App Server
Minimum memory	2 GB	2 GB	4 GB	4 GB
Recommended memory	2 GB+	4 GB	8 GB	8 GB
Minimum CPU	2.5 GHz CPU	2.5 GHz CPU	Quad-core 2.5 GHz	Quad-core 2.5 GHz
Recommended CPU	3.0 GHz dual-core CPU	2.5 GHz+ dual-core CPU	No longer specified	No longer specified

SharePoint 2010 will run within a virtualized data center, but the hardware requirements remain the same regardless of whether the servers are running on physical or virtual hardware. If SharePoint servers are to be virtualized, avoid using memory over commitment (which allows other VMs to “steal” unused memory) or thin provisioning (which allocates physical disk space to a VM on demand rather than reserving the space specifically for the VM ahead of time) for storage. Both will negatively impact SharePoint performance. Memory over commitment is a virtualization technology that allows at the moment.

The art of server selection
In all but the smallest environments, SharePoint is designed to be deployed as a distributed application that spans multiple servers. Choosing the number of servers required for a successful SharePoint 2010 deployment is something of an art form. It’s necessary to have a sufficient number of SharePoint servers to provide redundancy and load balancing without going overboard. At the same time, it is wise to limit the overall number of SharePoint servers because the bigger the SharePoint farm, the more it costs. Not only do hardware and software licensing costs increase in relation to the number of servers, but so do support costs.

Medium-sized and large SharePoint 2010 deployments should always be constructed as a three-tier topology. The top tier should consist of a minimum of two Web servers (for the sake of redundancy and load balancing). Larger organizations should consider a minimum of four, with two handling inbound Web requests and the other two dedicated to administration and “crawling” or indexing SharePoint data.

The middle tier should consist of the application servers. In medium-sized organizations, two application servers will often be sufficient for handling the workload and providing redundancy. Larger organizations often require several additional application servers. For example, large enterprises usually have a pair of “crawl servers,” a pair of query servers, a group of servers for all of the other SharePoint services (plus the Central Administration site), and possibly some servers for running “sandboxed” code in connection with a new SharePoint 2010 feature that enables end users with the right system privileges to deploy applications on their own.

Finally, the bottom tier should contain a set of clustered SQL Server systems. The best thing an organization can do to make sure its SharePoint deployment runs well is to focus on the performance of SQL Server. In small organizations, a pair of clustered systems running the database may be sufficient. In larger organizations, it is often necessary to create three separate SQL Server clusters. One cluster would be used for the “search” databases that store content-indexing information. Another cluster would be used for content databases, while still another would be used for all other SharePoint databases.

While designing a database topology to meet your organization’s needs is critical, it would also make sense to “pre-grow” the individual databases. Many SharePoint administrators prefer the convenience of using SQL Server’s autogrow feature to automatically increase database file sizes when needed, but that can add processing time to transactions. Assessing your expected file-size requirements and setting limits that can accommodate future growth needs typically yields much better performance.

It also helps to carefully plan your SQL Server storage environment. SharePoint 2010 is very I/O-intensive, so the more I/O operations per second that SQL Server can deliver, the better SharePoint is going to perform. It would be wise to store SharePoint databases on a RAID 10 array: That offers the high performance of a striped set of data and the fault tolerance that comes with mirroring. It’s also worth considering using separate disks (or disk arrays) for each database. SQL Server will perform better if separate storage is used for each workload.

There are numerous things an organization can do to avoid server performance- and failure-related pitfalls as part of SharePoint 2010 deployments. The key during the preparation process is to remember that there is no substitute for thorough architectural planning.

The most dangerous game: Hunting down bad SQL query performance

2011-09-23T13:58:00.000-07:00

Once you’ve fine-tuned your databases’ indexes, maxed out your hardware and gotten the fastest disks money can buy, you’ll hit a limit on how much performance you can squeeze from your SQL Server machines.

At some point you have to focus on fine-tuning the applications rather than beefing up SQL Server itself. That takes you into the somewhat tricky world of query analysis, where you try to identify database queries that -- because of the way they are written --aren’t performing as well as they could be.

SQL Server comes with an excellent tool, SQL Profiler, that’s designed to capture traces.Think of these as similar to a network packet capture: You’re actually capturing the raw queries being fed to SQL Server, along with information about their execution time. Using this raw data, you can spot bad SQL query performance, and then offer advice to the application developers on how to improve them.

Actually improving performance does depend on the ability to change the applications themselves, so this isn’t often an option for prepackaged applications on which you can’t make changes to the source code. Instead, this approach is usually limited to in-house applications. With these, you or someone you work with can get at the code to make alterations and improvements.

When you open SQL Profiler, you’ll start by creating a new trace. Part of the trace definition is a list of the events that you want to capture. You’ll usually want to capture remote procedure call (RPC) events as well as Transact-SQL events, as these two event types represent the two ways queries can be submitted to SQL Server or stored procedures can be executed. I usually include the following event classes in my traces:

RPC:Completed. This is generated after a stored procedure is executed from an RPC and includes information about such parameters as the duration of the execution, the CPU utilization and the name of the stored procedure.
SP:StmtCompleted. This is fired whenever a statement within a stored procedure finishes running and also includes data on metrics such as execution duration and CPU use.
SQL:BatchStarting. You’ll see this whenever Transact-SQL batches begin, including those inside and outside stored procedures.
SQL:BatchCompleted. This occurs when a Transact-SQL batch finishes; it provides data similar to the RPC and stored-procedure examples listed above.
Showplan XML. This gets you a graphical execution plan for a query—key to understanding how the query was executed and spotting performance problems.

Once your trace is set up, start capturing data. You’ll want to capture representative data, and often that means running the trace during production workloads. Be sure to capture to a file or SQL Server table that’s on a machine other than the one you’re analyzing so that the analysis itself doesn’t affect performance.

You’ll need to tell SQL Profiler which data columns you want to view; I usually start with this list:

Duration
ObjectName
TextData
CPU
Reads
Writes
DatabaseName
ApplicationName
StartTime
EndTime
EventSequence

These columns give me good insight into how long each query took to run, and I can often just skim through the Duration column looking for especially large values. You’ll want to focus on longer-running queries to see if you can improve execution time. The duration is shown in milliseconds (although it’s stored internally in microseconds), so don’t be alarmed if all of the values seem large at first.

I’ll also scan through the CPU column, since a query can run quickly but consume a lot of CPU time. Heavy-CPU queries will often bog down when the server is especially busy and can’t devote a lot of CPU capacity to them; as a result, rewriting queries so that they’re a bit less CPU-hungry can result in better performance. Profiler lets you create filters, and I’ll often start by creating a filter that hides anything taking less than 5,000 milliseconds, just so I can focus on the longer-running queries.

All in all, there are a number of things to look for, though most of the fixes for these problems will have to be implemented by the application developer:

Ad hoc SQL queries that are run outside of a stored procedure. Stored procedures almost always offer better performance because SQL Server can cache their execution plans; ad hoc queries should, whenever feasible, be converted to stored procedures.
Long-running or CPU-heavy queries in execution plans. Table scan operations indicate the lack of a suitable index, and putting an index in place to eliminate the table scan can have an immediate and positive effect on performance.
Queries that include a large number of joins. Joins take time, and while SQL Server is obviously designed to handle them, a large number of joins can really slow things down. The general rule of thumb I use is seven joins; if you have more than that, you have to start looking at ways to cut back.
A slow-running query that always runs slowly. This is a query that could perhaps be rewritten to perform better. A query that runs slowly some of the time is one that’s likely being affected by outside factors, such as locks or resource contention.

SQL query-performance tuning is as much art as science, and really, it belongs to the realm of application developers. The goal for database folks is to identify those slow-running or CPU-intensive queries, gather evidence and then work with developers to find ways of improving them.

SQL Server 2008 R2 SP1 Useful Things

2011-09-23T13:49:00.000-07:00

Aside from the usual round of bug fixes, a few new features have snuck in under the wire with SQL Server 2008 R2 Service Pack 1 (SP1). They are small changes, however, so I can see why Microsoft decided to give them to us now rather than force us to wait for the release ofits upcoming version of SQL Server, code-named Denali, to go gold, especially since many of them haven’t shown up in either of the two public previews of Denali. Here's a rundown of what's new.

The new FORCESCAN query hintAnyone who has taken the trouble to optimize queries knows the difference between a scanand a seek. A scan involves reading every row in a table to answer a query; a seek uses the table’s leaf data to find rows that answer the same query. You typically get the best results with a seek when the results involve less than 10% to 15% of the table’s data. A scan is optimal when you have a high number of likely hits, so it’s easier to read the whole table into memory and return results from there instead of going through the extra step of parsing the index.

The new FORCESCAN hint ensures the query optimizer doesn’t use a seek in a given operation; instead it uses a scan. Of course, if you put a query together using this hint you’re going to be responsible for any poor performance, so use it carefully. It works as you would expect:

SELECT user_type FROM user_table WITH (FORCESCAN)

This is assuming the user_type column is relatively un-unique -- what database wonks call a “low-cardinality” column -- and won’t benefit much from being indexed.

As the query optimizer in SQL Server grows that much more sophisticated, I’ve been shying away from using hints in my own programming, but there are clearly cases in which it’s valuable. For instance, let’s say you’re putting together a test suite. Forcing table scans is one way to torture-test the system. FORCESCAN would come in handy there.

New functionality for the FORCESEEK query hintIf FORCESEEK sounds like the inverse counterpart to FORCESCAN, you’re right. It’s another query hint that forces the use of a seek, though it’s been around for some time. In SQL Server 2008 R2 SP1, it’s been tweaked to allow you to specify which index or column you want to seek with:

SELECT user_name FROM user_table
WITH (FORCESEEK(user_table_index(user_name))

As you can probably guess from this example, there’s an index on user_table nameduser_table_index, which has user_name (a uniquely constrained value) as one of its columns. This forces the query optimizer to use that index and that column to do the seek. You can seek on multiple columns as well, but you need to specify them in the same order they’re presented in the index.

One possible application for this: you’re creating a system in which indexes are being generated on the fly and you want them to be specified “by hand,” either in an automatically generated stored procedure or through generated in-line SQL.

New system objectsNew system objects tend to sneak in across versions of SQL Server, often with little fanfare, but intrepid users uncover them and do their best to document them. Here are some of the new ones that have emerged this time around:

sys.dm_os_volume_stats: When queried with a database and file ID, this dynamic management function returns information about the disk on which your database files are stored. It tells you, among other things, how much free space there is on the volume and whether the volume supports compression.
sys.dm_os_windows_info: Returns data about the version of Windows that SQL Server is running on, including the service pack level and operating system language.
sys.dm_server_registry: Returns registry information relevant to the current installation of SQL Server.
sys.dm_server_services: Returns running services relevant to SQL Server, including the last startup time and whether a service is running in a clustered instance.

Support for 512e drivesNew hard drives are coming onto the market that use a new “512e” format -- they’re formatted internally with 4K sectors but report themselves as having 512-byte sector sizes to their hosts unless a host is also 4K-aware. I wrote about this issue for SearchWindowsServer.com, since it has performance impacts for applications like SQL Server. SP1 lets SQL Server 2008 R2 detect and correctly make use of such drives, as long as you’re running Windows Server 2008 R2 or better. (Lesser versions of Windows are not 4K-sector aware.)

Data-tier Application Component Framework (DAC Fx)Database schema management is one of the things most database administrators shy away from as being a terrible chore, but SQL Server has been gradually adding tools to make the job mildly less onerous. DAC, or the Data-tier Application Component, is one such tool: It makes it easier for data-tier objects (that is, everything in your database) to be written and deployed. SP1 includes a new DAC Fx and DAC Upgrade Wizard, which can upgrade schemas for existing databases in SQL Server or Microsoft’s cloud database service SQL Azure, wherever DAC is supported.

SharePoint PowerPivot disk space controlMicrosoft PowerPivot data is often cached in SharePoint, but until now you didn’t have direct control over the caching mechanisms. SQL Server 2008 R2 SP1 lets you control the amount of time an inactive database is cached in memory and how long cached PowerPivot files are kept on disk before being purged. This is useful if you have only a few PowerPivot items that you want to keep cached for longer periods of time because the underlying data doesn’t change often.

Up to 15,000 partitions on a single table via sys.sp_db_increased_partitionsThe sys.sp_db_increased_partitions stored procedure allowed you to enable theincreased_partitions setting for a given table. Originally this meant support for up to 1,000 partitions, but the limit has since been raised to 15,000.

Be sure to check out the TechNet wiki notes on SQL Server 2008 R2 SP1 for the most up-to-date information on these and other changes.

Top five features in SQL Server Developer Tools

2011-09-23T13:46:00.000-07:00

When Microsoft releases Denali -- code name for the next version of SQL Server -- the company also plans to release SQL Server Developer Tools (SSDT). Introduced as Project Juneau last November and made available in July as part of the second public Denali preview, SSDT provides an integrated environment to develop SQL Server databases and objects and deploy them to different SQL Server platforms.

SSDT includes a set of features that facilitate and enhance these development efforts. Five of those features in particular go a long way to making the life of database developers easier, more efficient and a lot more fun.

Session-based updates
In SQL Server Developer Tools, you can connect to an instance of SQL Server and modify database objects just like in SQL Server Management Studio (SSMS). However, with SSDT, you connect to the SQL Server instance, make changes to the database object and verify the accuracy of those changes before committing them to the database.

In the Transact-SQL Editor and Table Designer, SSDT automatically stores your edits in the current session rather than making the changes to the live database. If your changes generate any errors, those errors show up immediately in the Error List pane. You can then resolve the errors and commit the code changes to the database or generate an update script that you can run at a later time.

Offline developmentLike SQL Server Business Intelligence Development Studio (BIDS), SSDT is integrated into the Visual Studio environment. And also like BIDS, SSDT includes project templates to support business intelligence development (SQL Server Integration Services, Reporting Services and Analysis Services).

However, SSDT also includes a template for SQL Server database development. After you’ve set up your database project, you can copy a database schema from a SQL Server instance into the project environment. You can then modify the objects in the sandbox database, compare that schema with the schema of the original database and deploy those changes to the production environment.

In addition, you can develop a database offline and then publish that database to a SQL Server instance. The SSDT database project also lets you deploy to a variety of SQL Server platforms, including SQL Server 2005, 2008, 2008 R2, Denali and SQL Azure, Microsoft’s cloud database service.

Table DesignerTable Designer provides a quick and easy way to create and modify tables and related objects. The design window includes an editable columns grid that lists the column names along with their data types, nullability and defaults, if any.

You can also easily add primary keys, foreign keys, indexes and triggers. In addition, Table Designer includes a script pane that lets you edit the CREATE TABLE statement directly. Table Designer keeps the code in the script pane in sync with the columns grid. For instance, if you change a column’s nullability in the columns grid, that change is immediately reflected in the script pane and visa versa. The best part is, you can use Table Designer to modify database tables on SQL Server instances or tables within a database project.

Schema CompareThe Schema Compare tool lets you compare any two database schemas to determine how they differ. You can compare a database project to a database on an instance of SQL Server or a database project to a project snapshot, and you can compare databases in different instances of SQL Server.

After the tool performs the comparison, you can sort the results by database object, schema or necessary action on the target database, such as creating or updating objects. The tool also displays the code from the compared objects so you can see specific differences. Once you’ve reviewed the differences, you can specify what objects to update in the target database. You can then perform those updates immediately or generate a script to update the target database later.

Project snapshotsA project snapshot provides a read-only checkpoint reflecting the database schema at the time the snapshot was created. If errors are introduced into a project after a snapshot is created, you can revert to the image by using Schema Compare to determine what has changed and what items should be rolled back. You can also import database objects from your snapshot into a project, and you can compare a snapshot to a database on a SQL Server instance to help troubleshoot problems that might have been introduced into your production environment.

SSDT’s arsenal of toolsCertainly, the features described here represent just some of the new features you’ll find in SQL Server Developer Tools, such as code snippets, source control integration and change tracking. But these five features go a long way in providing SQL Server developers with the tools they need to work in a fully integrated development environment, offering up an arsenal of tools that will change the way SQL Server databases are developed and deployed.

Oracle looks to further integrate Hyperion into BI suite

2011-08-11T23:28:00.001-07:00

Oracle business intelligence (BI) and enterprise performance management (EPM) took center stage at this year’s Connection Point, with topics like Oracle’s further integration of Hyperion into its BI suite.

Hari Sankar, Oracle vice president of Hyperion EPM applications, said there are continued efforts to improve and increase interactions between Oracle EPM applications and tools. This is good news. As Oracle brings the Oracle Hyperion products together with its BI suite, we will begin to see benefits from reuse. One improvement might be streamlined security processes – if users maintain one set of models and code instead of two, IT staff can focus efforts elsewhere.

Another example is the migration of Hyperion Financial Management (HFM) and Planning onto the Oracle Application Development Framework (ADF). Sankar also noted how the use of EPM is expanding from finance into other areas of the enterprise.

Speaking on Oracle EPM 11.1.2.2, Sankar summarized expected improvements in Oracle EPM 11.1.2.2, which is expected to come out in the next year:

Fusion Applications will integrate with Oracle EPM.
Planning and HFM will have a “revamped user experience” as it now will leverage Oracle’s ADF.
HFM-Configurable (Additional) Dimensionality (a very welcome addition).
HFM and Hyperion Financial Close Management (HFC) analytics will use Oracle BI Enterprise Edition.
Extending Hyperion Profitability Cost Management to allow allocation to micro-objects.
A prebuilt project planning module will be available.
Integration of some predictive capabilities from Crystal Ball will be included in Planning.
Additional offerings and changes to existing components include Long-term Strategic Finance, Workforce Planning and Capital Assets Planning.
Better Microsoft Office integration in Planning.

Improving Oracle data visualization

A keynote speech and breakout session focused on efforts to improve data visualization in reports. The discussions were based on the writings of authors such as Edward R. Tufte, Stephen Few and Colin Ware, whose books discuss ways in which users consume information and improve on it.

The discussion highlighted problems businesses see in their reports. Many are cluttered with unnecessary and meaningless graphics in an effort to be flashy and often fail to convey the intended message. For example, varying the size of dots is usually ineffective because it’s hard for most people to see the difference..

Most important, this effort looks like it’s having an effect on Oracle’s reporting software. Oracle’s changes to Oracle BI Suite Enterprise Edition (OBIEE) seem to follow this school of thought,, with reduced space for borders and taskbars. In addition, OBIEE reporting objects seem to use a lower ink-to-space ratio, making them appear cleaner and more targeted toward getting the right information to the user.

Oracle BI data on mobile devices

Tobin Gilman, Oracle vice president of BI and EPM product marketing discussed the trend toward accessing and manipulating data on mobile devices, the use of social media and an increase in BI in operations. Gilman referenced a Gartner report that expects 33% of all BI functionality to be consumed on mobile devices by 2013. In turn, Oracle has enabled both the iPhone and iPad on its latest BI platform. Many platforms now support various social media tools such as RSS and social networks.

Operational systems are also seeing increased use of BI. As it has become more widely used within finance, the need for expanding BI capabilities across operational systems is growing. Oracle continues to expand its offerings, which reach back into operational systems such as SAP, J.D. Edwards, and others to retrieve detailed data.

OAUG has done an excellent job of providing a forum for a regionally based and customer focused subset of Collaborate, OpenWorld, and Kaleidoscope for a lower price and smaller time commitment. The group has continued its tradition of bringing in key Oracle speakers on a range of relevant topics. It is important, though, for the local customer base to provide feedback and suggestions for future conferences.

During a discussion near the close of the session, several suggestions were made, including whether OAUG might provide more opportunities for product demonstrations during sessions. This is valuable insight the organizers should leverage to organize sessions that meet the needs of attendees.

Upgrading to Oracle Database 11g

2011-08-11T23:27:00.000-07:00

If you have previously installed an earlier version of the Oracle database server, you canupgrade your database to Oracle Database 11g. Multiple upgrade paths are supported; the right choice for you will depend on factors such as your current Oracle software version and your database size. In this chapter, you will see descriptions of these methods along with guidelines for their use.

If you have not used a version of Oracle prior to Oracle Database 11g you can skip this chapter for now. However, you will likely need to refer to it when you upgrade from Oracle Database 11g to a later version or when you migrate data from a different database into your database.

Prior to beginning the upgrade, you should read the Oracle Database 11g Installation Guide for your operating system. A successful installation is dependent on a properly configured environment—including operating system patch levels and system parameter settings. Plan to get the installation and upgrade right the first time rather than attempting to restart a partially successful installation. Configure the system to support both the installation of the Oracle software and the creation of a usable starter database.

distinct advantages to installing the Oracle Database 11gsoftware and creating the database in separate steps. When installing from scratch, you have greater control over initialization parameters, database file locations, memory allocation, and so forth when you create the database in a separate step; when upgrading from a previous release, installing the software first provides you with the Oracle Pre-Upgrade Information Tool that you use against the existing database to alert you to any potential compatibility problems when you upgrade to Oracle Database 11g . To upgrade that database, you have four options:

This chapter assumes that your installation of the Oracle Database 11g software (see Chapter 1 and the appendix titled "Installation and Configuration") completed successfully and that you have an Oracle database that uses an earlier version of the Oracle software on the same server. Note that whether you are installing from scratch or upgrading a previous version of the Oracle Database, there are

Use the Database Upgrade Assistant (DBUA) to guide and perform the upgrade in place. The old database will become an Oracle 11g database during this process. DBUA supports both Oracle Real Application Clusters (RAC) and Automatic Storage Management (ASM); you can launch DBUA as part of the installation process or as a standalone tool after installation. Oracle strongly recommend using DBUA for Oracle Database major releases or patch release upgrades.
Perform a manual upgrade of the database. The old database will become an Oracle 11g database during this process. While you have very precise control over every step of the process, this method is more susceptible to error if you miss a step or forget a prerequisite step.
Use the Export and Import (or Oracle Data Pump) utilities to move data from an earlier version of Oracle to the Oracle 11g database. Two separate databases will be used—the old database as the source for the export and the new database as the target for the import. If you are upgrading from Oracle Database 10g , you will use Oracle Data Pump to move your data from the old database to the new database.
Copy data from an earlier version of Oracle to an Oracle 11g database. Two separate databases will be used—the old database as the source for the copy and the new database as the target for the copy. This method is the most straightforward because your migration consists primarily of create table as select SQL statements referencing the old and newdatabases; however, unless your database has very few tables and you aren't concerned with using existing SQL tuning sets, statistics, and so forth, Oracle does not recommend this method for production databases.
Upgrading a database in place—via either the Database Upgrade Assistant or the manual upgrade path—is called a direct upgrade. Because a direct upgrade does not involve creating a second database for the one being upgraded, it may complete faster and require less disk space than an indirect upgrade.

An overview of Forefront Protection 2010 for Exchange Server

2011-07-29T22:35:00.000-07:00

Microsoft acquired Sybari Software Inc. in 2005 and, with it, acquired its Antigen for Exchange product line. Microsoft later released its first suite of Microsoft-branded Antigen products in June 2006 -- marking its first line of antivirus products specifically for Exchange Server 2000 and Exchange Server 2003.

The next generation of this product -- Forefront Security for Exchange Server -- was released shortly after the debut of Exchange Server 2007. This version was enhanced to support the new role-based architecture and leverage the new transport pipeline in Exchange Server 2007.

Forefront Protection 2010 for Exchange Server is the current generation and next evolution of antispam and antivirus protection from Microsoft. Microsoft’s 2005 acquisition of FrontBridge Technologies Inc., a managed services provider for corporate email compliance, security and high availability, paved the way for its hosted security solution for Exchange, which now includes Forefront Online Protection 2010 for Exchange Server.

Exchange 2010 built-in antispam protection

When you deploy an edge transport server role, a wide range of antispam agents are installed that leverage Exchange Server 2010’s built-in API hooks. Exchange 2010’s antispam transport agents are derived from long-standing Exchange Server technology (Figure 1).

Figure 1. Exchange Server 2010 already has plenty of antispam transport agents built in.

Transport agents were first introduced in Exchange 2007 and can directly leverage the transport pipeline to allow antivirus and antispam applications to proactively scan inbound and outbound email processed by the edge transport server before it enters or exits an organization.

If the edge transport server isn’t deployed, the antispam transport agents can be imported onto a hub transport server role using the install-AntispamAgents.ps1 script. This allows any Exchange Server deployment topology to benefit from antispam protection. Of course, an antispam application will only address half of the problem; you still need an antivirus product to protect the organization from malware.

Forefront Protection for Exchange Server (On-premises)

Forefront Protection 2010 for Exchange Server (FPE) is an on-premises application that can be implemented in the internal network on the hub transport and mailbox roles. It can also be implemented in the perimeter network, on the edge transport or threat management gateway (TMG). FPE was designed to provide three distinct layers of filtering: connection filtering, protocol filtering and content filtering.

Layer 1 – Connection filtering (Approximately 80% of inbound spam rejected)

DNS Block List (DNSBL)
IP Allow/IP Block
Sender ID

Layer 2 – SMTP filtering (3% to 5% rejected)

Sender
Recipient
Global safe list
Global block list
Sender ID
Backscatter

Layer 3 – Content filtering (55% to 60% rejected)

Cloudmark
Automatic updates every 45 seconds

FPE can also be installed on the mailbox role. The table below lists available configuration options when FPE is installed on a mailbox server.

Forefront solution	Description
Forefront Endpoint Protection 2010	Malware protection for business desktop PCs, laptops and server operating systems that is easier to manage and control
Microsoft Forefront Protection 2010 for Exchange Server	Multiple-engine antimalware and anti-spam protection for on-premises Microsoft Exchange Server environments
Microsoft Forefront Online Protection for Exchange	Microsoft-hosted antimalware and anti-spam service offering enterprise-class reliability for messaging security and management
Microsoft Forefront Protection 2010 for SharePoint	File filtering, keyword blocking and antivirus scanning for Microsoft Office SharePoint Server document libraries
Microsoft Forefront Security for Office Communications Server	Virus scanning and content filtering for instant message conversations and file transfers
Forefront Threat Management Gateway Web Protection Service	URL filtering and Web antimalware update service for Forefront Threat Management Gateway 2010

Microsoft Forefront Protection Server Management Console (FPSMC) 2010

Microsoft Forefront Protection Server Management Console (FPSMC) 2010, allows administrators to manage not only multiple FPE servers within an organization but also the settings for FOPE, is available as a free download. FPSMC has an intuitive graphical interface that administrators can use for server discovery, configuration deployment, reporting, and quarantine management.

FOPE administrators can also utilize FPSMC as it is integrated with Forefront Online Protection for Exchange. FPSMC also includes some reports to help administrators understand the nature and trends of malware and spam protection.

The FPE Server Administrator Console does an adequate job of allowing you to configure FPE and FOPE for an organization and is all that is really necessary for single server deployments. The new dashboard view (Figure 2) makes it very easy to track current activity and the status of the different components in FPE.

Figure 2. You can manage FPE from its new dashboard.

New Forefront features to look for

Forefront Protection for Exchange Server has several features that might be new to Exchange Server administrators. Let’s take a look at some of the coolest new features and how they work.

DNSBL. This feature automates subscriptions to real-time block list (RBL) services and enables configuration through a single mouse click. This is possible because Microsoft has already subscribed to some of the most respected RBL providers to create its own DNS block list (DNSBL). When you enable DNSBL, you subscribe to the Microsoft list; enabling DNSBL will eliminate subscriptions fees that are often required to transfer block-list information to your servers. It can also eliminate the headache of managing and configuring your own subscriptions.

Backscatter. This feature protects your organization from bogus non-delivery report (NDR) messages. Prior to the release of FPE 2010, there was no Microsoft solution that could prevent fictitious NDR messages from being delivered to users’ mailboxes. When you enable Backscatter and generate a set of keys, each outbound message will have an attached token that’s based on a hashed tab to P1.MailFrom: in the email header. If the external messaging system that receives the email must return a non-delivery report, the token will be returned as well.

If the Backscatter feature on Exchange 2010 transport servers can validate the hash, then the NDR will be allowed into the organization. However, if the NDR is missing the hashed tag or Backscatter cannot validate the hash, then the NDR message will be dropped.

Note: To prevent inadvertently dropping valid NDR messages, all transport servers must have the Backscatter feature enabled. At the very least, it should be enabled on all Internet-facing transport servers.

Cloudmark. You can license this antispam solution from Microsoft for both FPE and FOPE. Once FPE is installed, it will replace the default antispam connection filter engine with Cloudmark. Cloudmark has proven to have a 99.77% catch rate. Microsoft guarantees a 98% catch rate in its server-level agreement (SLA) for FOPE.

Third-party spam and virus protection

Microsoft claims that there are four features in Forefront Protection 2010 for Exchange Server that differentiates the product from third-party solutions.

1. FPE uses five simultaneous scanning engines.2. It uses multi-layer defense architecture.3. FPE is easy to administer, monitor and report.

4. The solution supports a hybrid model that integrates both on-premise and online servers as well a singular solution.

Despite these advantages, however, it isn’t everything for everyone. Sometimes you need a third-party antivirus or antispam solution. There are a number of well-known antivirus and antispam vendors for Microsoft Exchange Server. When it comes down to choosing the best one for your enterprise, which factors should you consider? Key aspects to look for in a third-party antivirus solution for Exchange Server 2010 are:

Support for latest VSAPI
Support for hub, edge and mailbox roles
Use of transport agents for scanning
Support for antivirus stamping
Support for multiple scanning engines

Can the cloud reduce your spam carbon footprint?

There is a concept with antimalware and antispam prevention that suggests the sooner you can eliminate the threat, the less it will cost your organization. To describe this concept in today’s environmentally conscious landscape, some have coined this as "reducing the carbon footprint of spam and malware."

The last 10 years has seen an explosion in hardware appliances and perimeter-based email security designed to prevent unwanted email from even making it inside an organization. The downside to these solutions is that they require additional security expertise to maintain and they must be kept up to date in order to be effective. For many organizations, there is not enough staff to meet these challenges. The consequences of a solution failing are too great for many organizations, so they have begun to seek alternatives.

The use of cloud-based managed security solutions for email systems has increased significantly over the last few years. Cloud-based security solutions give companies the potential to maintain the smallest carbon footprint possible for malware and spam because these solutions eliminate unwanted email in the cloud -- not in the perimeter.

When Microsoft acquired FrontBridge, it became one of the top email hygiene providers along with Postini (Google), Message Labs (Symantec), SOPHOS and Trend Micro. Today there are more than 10 well-known hosted email hygiene/security providers to select from as well as several lesser-known vendors.

Microsoft’s technological advances with FOPE make it an excellent choice for a managed security solution in the cloud and a strong competitor with the predominant providers. The strongest argument for FOPE, however, is that it is the only solution that is tightly integrated with its on-premises counterpart, FPE. FOPE can also be enabled and provisioned with a few clicks of the mouse, using the same tools you need to manage FPE.

Example deployment topologies

FPE and FOPE were designed to support environments of all sizes. FOPE is a hosted solution, so it was designed to scale support for even the largest enterprises. There are different ways to deploy FPE and FOPE for an Exchange Server 2010 organization. FPE can protect Exchange organizations with single servers with combined roles or with dedicated server roles. FOPE can be leveraged by itself without FPE. However the most comprehensive solution is to deploy both FOPE and FPE together.

On-Premises: Combined Exchange Server roles
All Exchange Server roles are combined on a single server. Although the client access server role and unified messaging role are on the same server, FPE does not directly support them. All email and voicemail are submitted to the mailbox role; therefore, CAS and UM roles are indirectly protected (Figure 3).

Figure 3. Though not directly supported, the client access server and unified messaging roles are protected by FPE.
On-Premises: Dedicated Exchange Server roles
FPE is installed on the edge, hub and mailbox server roles, but it isn’t necessary to install on the UM or CAS roles. This topology gives Exchange administrators the greatest level of flexibility when sizing each server to meet the resource requirements of both Exchange 2010 and FPE. A TMG was also deployed to provide protection for the CAS role (Figure 4).

Figure 4. FPE is installed on the edge, hub and mailbox server roles.
On Premises/Hosted: Hybrid
FPE and FOPE are deployed as a holistic antimalware/antispam solution. The Forefront Protection Manager allows admins to centrally manage the antispam policy. There is an additional FOPE gateway server in this configuration. This function takes very little resources and is used to push the antispam policy to FOPE from the FPMSC (Figure 5).

Figure 5. FPE and FOPE can be deployed together as a hybrid antispam/antimalware solution.

Deployment recommendations

There are a few general rules you should follow when deploying Forefront Protection for Exchange Server.

Deploy FPE on an edge transport server.
Deploy FPE on all hub transport servers.
Deploy FPE on all mailbox servers.
Run all five engines, if possible, and run no less than two engines for fault tolerance.
During a malware outbreak, enable the Scan after engine update setting for real-time scanning on mailbox servers.
Optionally, deploy FPE on a Threat Management Gateway (TMG) instead of an edge server.
Use the Forefront Protection 2010 for Exchange Server Capacity Planning Tool.

Because running antivirus software consumes additional resources, it is important to plan appropriately. The capacity planning tool let you select reference architecture and customize the memory and hardware constraints. After it runs, it will produce a summary of the hardware requirements and number of servers that should be used, based on the specified constraints.

Viruses and worms of a decade ago seemed like the biggest threats to messaging security, but when you consider what they have evolved into today, for example: the latest phishing and malware attacks with criminal intent, it is no surprise the security industry has evolved as well. Email administrators are at the center of the malware and spam storm and have the greatest responsibility to provide their organizations with appropriate levels of protection.

The good news is there are more antispam and antimalware solutions on the market than there have ever been that are specifically designed for messaging systems. Microsoft has even included several layers of antispam protection built into Exchange Server 2010. As the industry moves forward, it seems that the more noticeable trends are the managed security solutions. The managed security solutions in the cloud are becoming more attractive to administrators that have found the task of keeping pace with the exponentially growing threats to their email systems more and more difficult to perform.

Three data warehouse maintenance tips for DBAs

2011-07-29T22:31:00.000-07:00

It’s easy to think of data warehouses as being more or less maintenance-free. After all, apart from occasionally loading the data warehouse with new and updated data, what else are SQL Server database administrators (DBAs) expected to do with it? Quite a bit. Let’s start with a few data warehouse maintenance tips.

Purge data. While data warehouses are designed to handle enormous amounts of information, you shouldn’t consider them to be an infinite data store. Sure, your company may want to retain three or more years’ worth of data, but at some point, some data is going to have to come out in order to retain the system’s performance.

Data warehouses can easily attract the worst kind of “data packrat” in your organization, with all kinds of data making its way into the warehouse and sticking around for what seems to be forever. But you should create a plan to move older data out of the system, either deleting it outright or moving it to some less-expensive means of archival storage.

Tune the system. No plan of attack survives first contact with the enemy, and in the world of data warehousing, the “enemy” is your user population. Regardless how expertly designed your data warehouse, no expert can foresee the real, day-to-day uses that it will evolve to support. For that reason, you’re going to need to periodically review how the data warehouse is being used and fine-tune its configuration to provide the best performance. In some cases, that may even mean revisiting the data warehouse’s design and restructuring data. After all, if your users aren’t asking the kinds of questions that the original structure was intended to answer, a new structure is going to be necessary.

This plays into data maintenance as well. When the time does come to restructure data, you’ll be able to do so more easily if your data warehouse isn’t retaining years and years of now useless data. Keeping the data store trimmed to “just the right amount” of data will make restructuring and tuning easier to accomplish.

Maintain the database. I see a lot of DBAs who don’t realize that a data warehouse is just another database. True, it’s designed differently than a transaction-processing database, but it’s a database all the same. That means all the usual database maintenance tasks apply.

Defragment and rebuild indexes as needed -- at least when indexes become more than 30% fragmented, which can easily happen after a major data loading operation.

Delete dimensional data on a regularly scheduled basis. It’s a slow process, so this might only happen once or twice a year, but it needs to happen.

Clear out obsolete logs and other historical data. This is especially important when the data warehouse is supporting a front-end system that is writing log data to the database.

Review the database size, configuration settings and other attributes to make sure they’re properly configured for the workload that the data warehouse supports. This will differ from organization to organization; the moral is to never assume that what, from a configuration perspective, was a good idea on Day 1 is still a good idea today.

But if you’re still in doubt, ask around. A data warehouse will require maintenance like any other database. If your data warehouse is part of a packaged system that your company bought, contact the vendor to find out what maintenance is appropriate. If it’s part of a system that was developed in-house, work with your developers to find out what maintenance is appropriate.

Understanding the testing tools in Visual Studio 2010

2011-07-29T22:26:00.001-07:00

Writing successful software requires learning a long list of skills and tools. Experienced programmers know that these skills are not static, and need refreshing from time to time. Knowing the ways to test your software is considered a necessary skill. This tip will give you an overview of the testing tools available in Visual Studio. As an added bonus you will learn which testing features are in which Visual Studio edition.

Visual Studio editions

Microsoft produces a number of versions of their popular Visual Studio product. Prices for these editions cover a wide range. You might be surprised to learn that there are free versions available for you to use. On the other hand you can get out your corporate credit and spend some serious cash for the Ultimate edition which costs up to $12,000. There are a number price points in between these two extremes. Microsoft caters to every niche.

There are four versions of Visual Studio that contain testing tools - Professional, Premium, Ultimate and Test Professional (aka Test Manager). Note that all these editions are paid versions. That’s right, no testing tools in the free versions.

Team Foundation Server

tools, Microsoft provides an optional server product, known as Team Foundation Server (TFS). TFS is the backbone of many corporate development shops. It serves as a repository for items considered crucial for professional software projects. For example it stores test cases, bug reports, and product work items. It also contains source control repositories, workflow generators and project progress reports. Visual Studio incorporates these items in the easy to use Team Explorer window.

In addition to the Visual Studio

Dedicated Testing edition

Testers get their own dedicated version of Visual Studio called Test Professional, also known as Test Manager 2010 (MTM). (MTM) works in tandem with TFS, providing the test team with access to test plans and detailed test cases. MTM also contains a Lab Center. Lab Center serves as a hub to create, mount and manage your virtual testing environments.

Individual Virtual Machines are mounted via Lab Manger, then used to execute one or more tests. One neat feature in the Lab Center is that a tester can save the virtual machine with a bug report. Then, when the developer works on bug they can also checkout the virtual machine.

Types of testing

I plan on covering these testing tools in futures tips, but here is a brief overview of each testing type.

Unit Tests: Validate code accuracy and whether the code meets that state functionality.

Ordered Tests: When you need to execute a series of tests in a certain order, use the ordered test.

DB Unit Tests: Test whether database entities are changed correctly during application runtime.

Code Coverage: determine what portion of your code is not executed during application runtime.

Web Performance: Record a series of HTTP requests to automatically send to your web server. Useful for testing the performance and information for page results, page redirects, view state, validations and authentication.

Coded UI Test: A way to automate testing of non –web UI (Windows Forms, WPF).

Test Impact Analysis: this feature allows you to see what unit tests have been impacted by your latest code changes. Rather than run your entire test suite, you can run the smaller suggested.

Here is a simple diagram that shows which feature is in each Visual Studio 2010 edition.

Ten command line time-savers for Linux administrators

2011-07-23T14:10:00.000-07:00

Although the Linux desktop has been subject to enormous improvements over the past twenty years (with perhaps the most notable change coming by way of the Ubuntu Unity interface), the command line remains unparalleled in terms of the power it can offer an experienced system administrator. Although most of the following 10 tips focus on the Bash shell, all of these tips will be easily applicable to other modern shells.

1. Create and enter a directory using one commandCreating and subsequently entering a new directory is such a common task it seems that there should be a shortcut for executing both commands in the shell. While it’s not, you can add the following function to your .bashrc file:

mkcd() { mkdir $1 cd $1 }

Then run source .bashrc to read the changes into memory, and complete both tasks using the mkcd command:

wjgilmore@ubuntu:~$ mkcd articles wjgilmore@ubuntu:~/articles $

2. Return to the previous directoryWhen you need to move from a deeply embedded directory and want to return to the original directory you could pass the previous path into the cd command, but a little-knowncd argument makes this trivial. This sequence demonstrates the behavior:

wjgilmore@ubuntu-laptop:~/Documents/techtarget_articles/ten_command_line_tricks/test2$ cd wjgilmore@ubuntu-laptop:~$ cd - ~/Documents/techtarget_articles/ten_command_line_tricks/test2$ wjgilmore@ubuntu-laptop:~/Documents/techtarget_articles/ten_command_line_tricks/test2$

3. Creating directory bookmarksContinuing along with the theme of directory interaction, there are some directories that you will inevitably return to time and again. It's possible to create bookmarks that allow you to quickly navigate to those directories by adding their paths to the $CDPATH shell variable (within your .bashrc file):

CDPATH='.:/home/wjgilmore/books'

Once added, you can navigate directly to the books directory from anywhere within the operating system path simply by executing the following command:

$ cd books

4. Deftly edit the command lineHow many times have you tediously edited and executed a series of slightly dissimilar commands? Such as when building the PDF version of various book chapters I'm working on from the Markdown source I regularly execute the following command:

$ pandoc -o html/chapter06.html chapters/chapter06.md --template=templates/html.template

up to retrieve the previously executed (above) command from history, and then arrowing left until replacing both instances of chapter06.md with chapter04.md. There are several more efficient ways to perform this task. First, consider using Bash's command line editing keyboard shortcuts (two modes are supported: Emacs and vi), which allow you to quickly navigate to the desired location:

In order to also build the chapter04.md source document command line novices would quickly tire of arrowing

Ctrl + a: Go to beginning of line Ctrl + e: Go to end of line Alt + f: Go forward one word Alt + b: Go backward one word

A second and even more efficient approach involves using command line substitution. The following command will replace the 06 found in the previously executed command with 04:

$ pandoc -o html/chapter06.html chapters/chapter06.md --template=templates/html.template $ !!:gs/06/04 pandoc -o html/chapter04.html chapters/chapter04.md --template=templates/html.template

Incidentally if you're using the GNOME terminal then the meta (Alt) key won't work as described, because GNOME terminal already binds the Alt key to toolbar commands. Alternatively you can use Shift + Alt as the meta key, but this is a bit awkward. Instead, if you don't require the toolbar command shortcuts, disable them by navigating to Edit -> Keyboard Shortcuts... and disable the Enable menu access keys option.

5. Saving a long command for later useWhen working through a sequence of system administration operations, it is possible to type a particularly long command and then realize before executing it a step in the sequence has been left out. Rather than deleting the command, you can save it to the history without executing it by appending a hash mark (#) to the beginning of the command:

$ #this is some ridiculously long command that I want to save

After pressing the Enter button, arrow up and you'll see the command has been saved. To execute the command, just remove the hash mark from the beginning of the line before execution.

6. Save typing using command aliasesThe ls command's long listing format (ls -l) can be frequently used, but the hyphen makes it a bit unwieldy when typing furiously. You can create command aliases of for longer commands using the alias command within .bashrc. In this example, the command aliasdir is substituted for ls -l:

alias dir='ls -l'

7. Saving more typing by ignoring typosYou're in the terminal zone, blazing from one directory to the next while copying, updating and removing files at will. Or you're not, because the fingers are moving faster than the brain or even keyboard response time can handle, causing you to constantly backtrack and correct your typos. Add the following line to your .bashrc file and the shell will automatically fix any typing blunders you make when identifying file or path names.

shopt -s cdspell

8. Opening applications in the backgroundWhen cruising around the command line, you may need to do another task such as respond to an email. Of course, it's possible to open GUI applications from the terminal in the same way you'd execute any other command, done simply by invoking their name, in this case, opening Gimp:

$ gimp

But doing so effectively ends your terminal session, because the application will open in the foreground. If you're regularly opening a particular application from the command-line, consider modifying its default invocation within your .bashrc file:

gimp() { command gimp "$@" & }

Reload your .bashrc file (see the source command) and you'll be able to invoke the Gimp application, passing along the names of any image files you'd like to open, with the added bonus of retaining control of the terminal.

9. Do more with lessThe more command is useful for quickly perusing the contents of a text file. Once the file is loaded into the page you can use the forward slash (/) to search the file. The problem is that once you've found the desired string it's not possible to navigate up and inspect the contents that appeared prior to this string. The less command doesn't suffer from this disadvantage, allowing you to scroll both up and down within a text file. The less command is invoked in the same manner as more:

$ less sometextfile.txt

10. Clean up your command line historyThe history command is easily one of the most powerful tools at your disposal. But there is one timesaver in particular that deserves mention: the $HISTIGNORE shell variable.

Over time your history list will become incredibly long. Take advantage of the $HISTIGNORE;variable to mute the recording of any commands you deem irrelevant:

$ export $HISTIGNORE="&:cd:exit:ls"

This will cause all duplicate commands, and the cd, exit, and ls commands to be omitted from the history list.

Speed is key to mastering the command line, and these ten tips and tricks should get you started on your command line mastery. If you would like to share any other tips, please contact me via my Web site.

Validate determines root cause of server network issues

2011-07-18T10:15:00.000-07:00

Some of the toughest network issues to troubleshoot are those dealing with Windows failover clusters. Fortunately there is a tool, Validate, built into the Windows 2008 and Windows 2008 R2 operating systems that can assist with isolating network failures.

The original intent of Validate, or the Cluster Validation Wizard, was to check potential servers to determine if all the components were configured properly to support a failover cluster. A variety of tests are performed to ensure the various components are working consistently across the different servers. Once all the tests have passed, the cluster can be created and supported by Microsoft. Validate can also be used to inventory a variety of components such as network adapters, driver revision levels and firmware settings.

As Validate runs the tests and collects configuration data, it generates an HTML report that can be viewed with a Web browser. The report is organized into sections such as network configuration and storage with hyperlinks that display further details.

Validate is included with the Windows Failover Cluster Management MMC snap-in. This software is installed when using the Server Manager to add the Failover Cluster feature. Once you invoke the Failover Cluster Management snap-in, the action to "Validate a Configuration" becomes available.

Validate can also be used to troubleshoot an existing cluster. The tool allows you to select specific tests that focus on a particular component such as the network configuration or storage. This allows you to isolate a network problem without disrupting the storage. In the following figure, Validate prompts to run all tests or just the ones selected.

To troubleshoot a network problem, select the various tests under the Network section as seen below. Clear the check box for all the other tests such as system configuration and storage to avoid unnecessary disruption. The network tests will verify network communication, IP and subnet configuration, binding order, firewall and cluster network configuration (Figure 1).

Figure 1: Test Selection (Click to enlarge)

More specifically, the List Network Binding Order test will list the order in which networks are bound to adapters. You always want to favor the public adapter over the heartbeat (private) adapter since outbound connections to clients will only be resolved across the public interface. The IP configuration test verifies a unique IP address is being used across the cluster and that multiple adapters are not in the same subnet.

The Firewall test validates that the firewall configuration is properly setup to support failover clusters. And the Cluster Network test ensures that all IP addresses are either static or DHCP, but not both. The Cluster Network test also confirms that IPv4 or IPv6 are consistently set across all network adapters. Finally, the Network Communication test verifies that servers can communicate across multiple interfaces with acceptable latencies and without a single point of failure. As the tests are executed, a status window displays progress and results. The tests that pass successfully are marked in green while those resulting in errors are flagged in red. In the following example, the IP configuration test failed so it is marked in red (Figure 2).

Figure 2: Test Failed (Click to enlarge)

After Validate is finished executing the tests a summary window is displayed, listing the results of each one. This generates a report containing a hyperlink (Figure 3) where one can drill down for more troubleshooting information.

Figure 3: View report (Click to enlarge)

Select "View Report" to open the Web browser to display the Validation Report. Clicking the hyperlink for the Cluster Network Configuration test displays the error message encountered during testing in a red box. You can use this information (Figure 4) to troubleshoot what is causing network-related problems.

Figure 4: Error message (Click to enlarge)

The Cluster Validation Wizard is a menu-driven tool that can be used to troubleshoot a variety of cluster network related problems, in addition to storage and system configuration issues. One can selectively use the tool to validate certain components depending on the symptoms.

CLR triggers for SQL Server

2011-07-16T17:05:00.001-07:00

In addition to stored procedures and user-defined functions, the new .NET integration capabilities found in SQL Server 2005 also provide the ability to create CLR triggers. To create a trigger using Visual Studio 2005, you start your project as you saw in the earlier examples. To create a trigger using Visual Studio 2005, select the New | Project option, give your project a name, and click OK to create the project.

For this project, I used the name ti_ShowInserted for my trigger. This trigger essentially retrieves the values of the row being inserted in a table and displays them. After naming the project and clicking OK, I filled out the New Database Reference dialog using the same values that were shown in the previous examples. Next, I used the Project | Add Trigger menu option that you can see in Figure 3-10 to create a starter project for the CLR trigger.

Figure 3-10: Adding a CLR trigger

As you saw in the earlier example of CLR database objects, you select the Trigger option from the list of templates and then provide the name of the trigger in the name prompt. Visual Studio 2005 will generate a starter project file that you can add your code to. The starter project includes the appropriate import directives as well as generating a class, in this case appropriately named Triggers, and a method named ti_ShowInserted with its appropriate method attribute. The following code listing shows the completed code for the CLR trigger named ti_ShowInserted:

The example CLR trigger displays the contents of the data that is used for an insert action that's performed on the Person.ContactTypes table in the Adventureworks database. The first thing to notice in this code listing is the Attribute for the ti_ShowInserted subroutine (the code enclosed within the < > markers). The Attribute is used to name the trigger and identify the table the trigger will be applied to as well as the event that will cause the trigger to fire.

When the Visual Studio 2005 trigger template initially generates this Attribute, it is prefaced by a comment symbol—essentially making the line a comment. This is because the trigger template doesn't know how or where you want the trigger to be used. In order for Visual Studio 2005 to deploy the trigger, you need to uncomment the Attribute line and then fill in the appropriate properties. The following table lists the properties used by the Visual Studio 2005 trigger template:

In this example, the resulting trigger will be named ti_ShowInserted. It will be applied to the table named Person.ContactType, which is in the AdventureWorks database, and the trigger will only be fired for an insert operation.

The primary code for the trigger is found within the ti_ShowInserted subroutine. This code example makes use of another new ADO.NET object: SqlTriggerContext. The SqlTriggerContext object provides information about the trigger action that's fired and the columns that are affected. The SqlTriggerContext object is always instantiated by the SqlContext object. Generally, the SqlContext object provides information about the caller's context. Specifically, in this case, the SqlContext object enables the code to access the virtual table that's created during the execution of the trigger. This virtual table stores the data that caused the trigger to fire.

Next, a SqlPipe object is created. The SqlPipe object enables the trigger to communicate with the external caller, in this case to pass the inserted data values to the caller. The TriggerAction property of the SqlContext object is used to determine if the trigger action was an insert operation. Using the TriggerAction property is quite straightforward. It supports the following values:

If the TriggerAction property equals TriggerAction.Insert, then an insert was performed and the contents of the virtual trigger table are retrieved and sent to the caller using the SqlPipe object's Execute method. In order to retrieve the contents of the virtual table, a SqlConnection object and a SqlCommand object are needed. These objects come from the System.Data.SqlClient namespace. You should note that when used with server-side programming, the Connection String used by the SqlConnection object must be set to the value of "context Connection=true". Then a SqlCommand object named oCmd is instantiated that uses the statement "Select * from inserted" to retrieve all of the rows and columns from the virtual table that contains the inserted values. Finally, the ExecuteAndSend method of SqlPipe object is used to execute the command and send the results back to the caller.

Deploying the trigger

Once the code has been created, you can either deploy it to the database using the Visual Studio 2005 Build | Deploy solution option or manually drop and re-create the assembly and any dependent objects you saw in UDF examples earlier in this chapter. ch03.

To manually deploy the code, you'd need to copy ti_ShowInserted.dll to the SQL Server system or to a share that's accessible to the SQL Server system and then execute the following T-SQL Server commands:

This example assumes that ti_ShowInsert.dll was copied into the c:temp directory on the SQL Server system. First, the Create Assembly statement is used to copy the DLL into the SQL Server database and then the Create Trigger statement is used with the As External Name clause to create a trigger named ti_ShowInserted and attach it to the Person.ContactTypes table. As in the earlier examples, the As External Name clause identifies the assembly using a three-part name: asssembly.class.method. Pay particular attention to the class portion of this name. For triggers you must bracket the class name and include the namespace just before the class name. In this example, the assembly is named ti_ShowInserted. The Namespace is ti_ShowInserted. The class is named Triggers, and the method is named ti_ShowInserted.

Using the trigger

After the CLR trigger has been deployed, it will be fired for every insert operation that's performed on the base table. For example, the following INSERT statement will add a row to the Person.ContactType table, which will cause the CLR trigger to fire:

The example trigger, ti_ShowInserted, performs a select statement on the inserted row value. Then it uses the SqlPipe object to send the results back to the caller. In this example the trigger will send the contents of the inserted row values back to the caller: