Outlooks on Business and Technology

New England CIOs, Techies, Developers, Geeks and SQL Passionatos

Come bring your energy and passion for Databases to the Southern New England SQL User Group!

When:            March 09, 2011 6:00PM - 9:00PM

Where:           Hall of Fame Room at New England Tech

Speaker:          John Miner

Topic:            "Leveraging Transaction SQL 2008 to solve business problems (Part I)"

 

This presentation will review how to build a database solution from the ground up using SQL commands.  What if you were a SQL Server developer at a local consulting company tasked with creating a database for a local Boy Scout of America (BSA) Troop, where would you begin? The following topics will be reviewed to make sure you have a successful data storage solution.

Defining the Database:

Defining file groups for the database, Creating schemas to separate tables by functionality, Creating normalized tables to hold the BSA data, Adding data integrity to the design with constraints/triggers, Adding referential integrity to the design with primary/foreign keys, Adding logins/users and granting rights

Loading initial Data:

Loading data into staging area with BULK INSERT, OPENROWSET or BCP, Quick review of TSQL statements for TRANSACTIONS, CONTROL FLOW, and ERROR HANDLING, Writing Stored Procedures to move staging data to production, Schedule Procedures using SQL Server Agent

Giving Developers Access:

Using stored procedures to lock down CRUD (create, read, update, and delete) operations, Using functions and views to look at the data differently.

In my next presentation, I will be going over advance topics such as PARTITIONING – for keeping track of all those Christmas Trees they sell each year, FILESTREAM – for showing images of the merit badges they might earn, SPATIAL – for keeping track of GPS latitude and longitude coordinates when hiking, and XML for interacting with other systems via messages.

John Miner has twenty years of data processing and proven project management experience, specializing in the banking, health care, and government areas. His architecture expertise encompasses all phases of the project life cycle, including design, development, implementation, and maintenance.

His credentials include a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages. He has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).

 

A bit of Reading: September's Accounting Technology from SourceMedia

I was catching up on a bit of magazine reading last night, and I was enlightened by a new article in Accounting Technology. This article was actually focused on eliminating data entry through new scanning technologies.

It made some good points: Think about taking original documents from clients or even databases of accounting information and interfacing that data directly into the Accountant's tax preparation software.

Now that is what I call innovative thinking, and it was a very refreshing change from the subject matter of the past. Vision, problem solving, using the latest advances in hardware and the software tools to solve real business problems ... now that is what I like to see!

Thoughts on Databases/Discussions with the CPA forums

It is fun to approach databases from a vendor mindset…

MS ACCESS is owned by Microsoft. If you look at the development goals and history of Access you learn that it was never really designed to be a core data store. It was designed as an office application. A desktop database to be used for single person database needs. Small lists of various configuration. MS ACCESS is also designed to be used to query or pull data from other databases for a front view/different look at that data. So ACCESS can be used well when it is layered with another database and you will also see MSDE databases used in this same way. If an access database is open and the computer is “turned off” the access database has a very, very high risk of corruption. There is no code to protect against this.

ORACLE is owned by Oracle. This database was created to handle millions/billions of transactions in very large enterprises. It is a data store and has the programming and technical expertise surrounding it to store data with extremely high data integrity. If a disk fails for instance in an environment that is hosting an Oracle database, the Oracle database has programming built in to protect the data AND to check that the data was protected.

So what about the middle? SMB niche? In this niche MS SQL is a high integrity database at a reasonable cost. It is designed to run on a server (not a desktop) and has an extremely good reputation from the database gurus of the world (database administrators, designers, ISVs etc.) Of note in the OpenSource world MySQL also has a great reputation as well.

You can also look at databases more generically which is also what many of the database pros do. As in the difference between a relational database and a network hierarchical database and a flat file database. Over the last set of years you might have also heard of the term Client/Server as being a buzz word.. As software becomes more and more “the same” it is critical for all business/manager type people to understand that DATA is (and will become more so) a FIRM asset, and understanding how this data is protected will be an area which is perfect for the CPA niche to be able to talk on.

Oh and in terms of Enterprise versus Micro-Small.. Even the smallest of small firms can have millions of bytes of data that they depend on daily. AND CPA firms now moving to paperless office technologies, need to be careful.

It is not unreasonable to meet a database expert who has spent years studying only databases.

SQL Service Pack 2

SQL SP2 is causing a bit of trouble in the field.  Many people might need this

FIX

Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at different intervals than intended

For those reading this using Microsoft Dynamics CRM and thinking of upgrading to  SQL 2005 SP2, you want to download SQL 2005 SP2a here.

Want to do a bit of Beta?

"On September 27th, Microsoft announced the public beta of System Center Data Protection Manager version 2."

Through the back channels I have recently learned that they are interested in more participation. If I/O Bottlenecking is an issue and/or your team is doing more pro-active analysis and research around performance, data protection and data recovery tools, perhaps this opportunity is for you.

Here is more!

"As part of our Optimizing Core IO customer campaign, DPM fulfills the “Data Protection and Recovery” pillar.

  Data Protection Manager (DPM) is a key member of the Microsoft System Center family of management products, designed to help IT Pros manage their Windows environment.   

Today, DPM 2006 delivers centralized backup for branch offices, while providing rapid and reliable recovery of files from readily accessible disk instead of waiting to locate and mount tapes.  Restores are achieved from an easy-to-use IT administrator interface, as well as enabling end-users to restore their own data directly from Windows® Explorer or any Microsoft Office™ application. 

Data Protection Manager (DPM) v2 is the next generation of Microsoft’s data protection platform – and the backup/restore tool that administrators of Microsoft networks have been asking for.  DPM “v2” sets a new standard for Windows backup and recovery -- delivering continuous data protection for Microsoft application and file servers to a seamlessly integrated secondary disk and tape solution on the DPM server.  DPM enables better backups as well as rapid and reliable recoveries for both the IT professional and the end-user.  DPM significantly reduces the costs and complexities associated with data protection through advanced technology for enterprises of all sizes.

DPM version 2 provides:

• Continuous Data Protection for Windows Application and File Servers - DPM protects core Windows Server workloads (Exchange, SQL, SharePoint and File) by continuously capturing data changes with application-aware block-level agents, providing an easy-to-manage and robust disk/tape back end platform, and one-click lossless application recovery.

• Rapid and Reliable Recovery - DPM enables IT administrators and end-users to easily recover data in minutes from easily accessible disk instead of locating and restoring from less-reliable tapes.

• Advanced Technology for Enterprises of all sizes - DPM brings together the best aspects of CDP real-time protection with traditional tape backup/restore to provide a comprehensive disk‑to‑disk‑to‑tape data recovery solution.  Combined with Microsoft’s experience in Windows Server technology, DPM v2 provides a technically advanced and comprehensive data protection solution for the most demanding Windows environments – from the SMB to the Enterprise.

When helping customers mature through the stages of Core IO, be aware of opportunities for customers to better protect their environments with this new Microsoft offering.

Even More:

• For information on DPM 2006 and DPM v2, get the new DPM datasheet 
• Read the Beta Announcement press release. 
• Customers can be directed to www.microsoft.com/DPM.  Partners go to https://partner.microsoft.com/dpm.  Anyone can  send email to [email protected] for more information.

Data Integration between applications

I was surprised to learn recently that the pain of integrating data between multiple applications continues to be extremely sharp! With that in mind I did a little digging and found that for the marketing industry in general the central repository of contact information continues to be an elusive task. Considering that we are all meeting new people on a regular basis and that those new people are sources of new business and we are sources of new business for them, this is not just another tiny problem to tackle.

Carol Krol says it best when she indicates

"Part of the problem is that [people] marketers are trying to process more data then ever before. In addition to the information they can now collect on their Web sites, many marketers gather and store customer data in various information technology systems or software. These include customer relationship management, enterprise resource planning, enterprise marketing management and business intelligence systems"1 Check out Database Integration the Key to Market Strategy by Carol Krol

So what can be done?
Solution 1: A software application can mine and distribute information to other systems within a firm. To do this effectively a central master repository of like data is needed.

Solution 2: Data can be collected and then cross referenced to a master National database for instance firms such as Acxiom, Harte-Hanks and Experien all have master databases of contact information.

What about the smaller firms? The saving grace for the SMB market might be that more and more software vendors are writing software applications using the same software suite of tools and the same database backends. For those that use more unique and sometimes more secure databases the industry pressure is still to make the data within those easy to retrieve. We basically want access to our data so we can present and crunch it our own way.