Search results matching tags 'SQL Server' and 'Web'

PowerShell PowerPack Download

BuckWoody — Wed, 07 Apr 2010 13:30:20 GMT

I read Jeffery Hicks’ article in this month’s Redmond Magazine on a new add-in for Windows PowerShell 2.0. It’s called the PowerShell Pack and it has a some great new features that I plan to put into place on my production systems as soon as I finished learning and testing them.

You can download the pack here if you have PowerShell 2.0. I’m having a lot of fun with it, and I’ll blog about what I’m learning here in the near future, but you should check it out. The only issue I have with it right now is that you have to load a module and then use get-help to find out what it does, because I haven’t found a lot of other documentation so far.

The most interesting modules for me are the ones that can run a command elevated (in PSUserTools), the task scheduling commands (in TaskScheduler) and the file system checks and tools (in FileSystem). There’s also a way to create simple Graphical User Interface panels (in ). I plan to string all these together to install a management set of tools on my SQL Server Express Instances, giving the user “task buttons” to backup or restore a database, add or delete users and so on. Yes, I’ll be careful, and yes, I’ll make sure the user is allowed to do that.

For now, I’m testing the download, but I thought I would share what I’m up to. If you have PowerShell 2.0 and you download the pack, let me know how you use it.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

PowerShell: Read Excel to Create Inserts

BuckWoody — Thu, 18 Mar 2010 12:09:36 GMT

I’m writing a series of articles on how to migrate “departmental” data into SQL Server. I also hold workshops on the entire process – from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach) a few methods on actually moving the data.

One of those options is to use PowerShell. There are a lot of ways even with that choice, but the one I show is to read two columns from the spreadsheet and output statements that would insert the data using a stored procedure. Of course, you could re-write this as INSERT statements, out to a text file for bcp, or even use a database connection in the script to move the data directly from Excel into SQL Server.

This snippet won’t run on your system, of course – it assumes a Microsoft Office Excel 2007 spreadsheet located at c:\temp called VendorList.xlsx. It looks for a tab in that spreadsheet called Vendors. The statement that does the writing just uses one column: Vendor Code. Here’s the breakdown of what I’m doing:

In the first block, I connect to Microsoft Office Excel. That connection string is specific to Excel 2007, so if you need a different version you’ll need to look that up.

In the second block I set up a selection from the entire spreadsheet based on that tab. Note that if you’re only after certain data you shouldn’t get the whole spreadsheet – that’s just good practice.

In the next block I create the text I want, inserting the Vendor Code field as I go.

Finally I close the connection.

Enjoy!

$ExcelConnection= New-Object -com "ADODB.Connection"
$ExcelFile="c:\temp\VendorList.xlsx"
$ExcelConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;`
Data Source=$ExcelFile;Extended Properties=Excel 12.0;")

$strQuery="Select * from [Vendors$]"
$ExcelRecordSet=$ExcelConnection.Execute($strQuery)

do {
Write-Host "EXEC sp_InsertVendors '" $ExcelRecordSet.Fields.Item("Vendor Code").Value "'"
$ExcelRecordSet.MoveNext()}
Until ($ExcelRecordSet.EOF)

$ExcelConnection.Close()

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Consolidation Strategy References

BuckWoody — Tue, 09 Mar 2010 13:11:19 GMT

I have a presentation that I give on SQL Server Consolidation Strategies, and in that presentation I talk about a few links that are useful. Here are some that I’ve found – feel free to comment on more, or if these links go stale:

Consolidation using SQL Server: http://msdn.microsoft.com/en-us/library/ee692366.aspx

SQL Server Consolidation Guidance: http://msdn.microsoft.com/en-us/library/ee819082.aspx

More references for SQL Server and Hyper-V: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Virtualization-with-SQL-Server.aspx

Quick overview of Virtual Server licensing implications: http://www.microsoft.com/uk/licensing/morethan250/learn/virtualisation.mspx

SQL Server and Hyper-V best practices: http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx

High-Availability and Hyper-V: http://technet.microsoft.com/en-us/magazine/2008.10.higha.aspx

Virtualization Calculator: http://www.microsoft.com/Windowsserver2008/en/us/hyperv-calculators.aspx

May not be current, but here’s a whitepaper from VMWare for SQL Server: http://www.vmware.com/files/pdf/SQLServerWorkloads.pdf

More information on SQL Server and VMWare: http://blogs.msdn.com/cindygross/archive/2009/10/23/considerations-for-installing-sql-server-on-vmware.aspx

Server Virtualization Validation Program: http://www.windowsservercatalog.com/svvp.aspx?svvppage=svvp.htm

Weekly Challenge – Tools for Thought Site

BuckWoody — Wed, 24 Feb 2010 14:15:06 GMT

A good friend of mine years ago showed me this web site: http://www.rheingold.com/texts/tft/.

I took my lunch hour each day for a week or so and read each “page” on this site. It deals with computer technology history in a way that I had never read before. As I study each new trend in computer and database technology, I still find this site to be very relevant.

So my challenge to you is to read this site, from beginning to end. Tell me what you think, and if it makes an impact on you.

Can’t Travel for Training? No Problem

BuckWoody — Wed, 17 Feb 2010 14:14:11 GMT

Right now it’s tough to get businesses to loosen the purse a little and get you out to some training. But you have features in the product that you’re not even able to use because you haven’t been trained. What’s a Data Professional to do?

Why, go to free or low cost training over the web, of course! And we’ve got you covered. Here’s the scoop:

Travel-Free Training

Microsoft offers SQL Server E-learning courses

Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary. Subject matter includes: administration, architecture, database, programming and security. Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog.

Course Title

Type

Price

Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability

E-Learning Course

Free

Clinic 10258: SQL Server 2008: Upgrade and Application Compatibility

E-Learning Course

Free

Course 10156: Automating Administrative Tasks in Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10152: Creating and Managing Databases in Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10155: Managing Security in Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10154: Backing Up and Restoring Databases in Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10151: Installing and Configuring Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10153: Planning, Creating, and Optimizing Indexes in Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10158: Implementing High Availability and Replication in Microsoft SQL Server 2008

E-Learning Course

$39.99

Course 10157: Monitoring and Controlling Resources in Microsoft SQL Server 2008

E-Learning Course

$39.99

Clinic 10164: Essential SQL Server 2008 for Developers

E-Learning Course

Free

Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services

E-Learning Course

Free

Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence

E-Learning Course

Free

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform

E-Learning Course

Free

Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

E-Learning Course

Free

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

SQL Server Chargeback Strategies

BuckWoody — Tue, 29 Dec 2009 14:47:37 GMT

It seems the more things change the more they stay the same. One of the things I used to create on the mainframe system when I started years ago was a “charge-back” system. It tracked the time and resources used by the employees so that we could charge their department money for the time they spent on the (very expensive) mainframe.

When LAN systems came out, IT departments were just charged as a general expense. But these days, with shrinking budgets and deeper scrutiny on how resources are used, this idea has come back around. IT and Data Systems managers are keen to show the organization that it isn’t free to operate a database system.

From time to time I get asked if it is possible to do a chargeback system for SQL Server. And it is! In fact, I wrote a couple of articles on how to do that right here:

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=311

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=312

Data is Just Data – Information – that’s another thing!

BuckWoody — Mon, 28 Dec 2009 15:09:46 GMT

Many data professionals deal in the storage, retrieval and display of data from a central set of systems. But there is another side to our craft, and I think it’s the most rewarding part of the job for both the professional and the organization they serve.

Data, in and of itself, isn’t very interesting. It’s when that data gains meaning that the job becomes a lot more fun. Of course you have to know your data to begin with, and it’s also important to know the business side of your organization.

But visualization is important as well. Knowing how to create an interesting report, while not my main job, is a fun part of working with my systems. And from time to time I run across new ways to see and explore data, like this new tool from Microsoft Research: http://www.getpivot.com/.

Make sure you check that one out, and spend a little time learning how to show the data underneath all those servers and databases you control.

The Top 20 Questions in Database Design

BuckWoody — Thu, 17 Dec 2009 14:18:00 GMT

I'm still re-reading the "Fourth Paradigm" book by Microsoft Research, and one section continues to intrigues me. There's a part where the book explains database design, and puts forth that the most important thing when you're designing large data sets is to find out the "Top Twenty Questions" the database has to answer. The quote is this:

"Most selections involving human choices follow a 'long tail,' or so-called 1/f distribution, it is clear that the relative information in the queries ranked by importance is logarithmic, so the gain realized by going from approximately 20 (24.5) to 100 (26.5) is quite modest."

I find this facinating - it just doesn't seem to make "common" sense. Surely you have to ask a lot more questions than that to "get" the shape of the data? I researched the mathematical concept he's describing (http://www.scholarpedia.org/article/1/f_noise), and I'll try some experiments here. I'll let you know what I uncover!

Here's the link for the book if you want to read it:

http://research.microsoft.com/en-us/collaboration/fourthparadigm/4th_paradigm_book_complete_lr.pdf

The Magical World of SQL Server Licensing

BuckWoody — Thu, 26 Nov 2009 15:15:30 GMT

Licensing. Sometimes it feels like to you need a specialized degree, a black robe and a secret handshake to really understand it – but it’s not quite that bad.

There are several licensing “models”, from just buying SQL Server off the shelf to Software Assurance, or SA, where you just install to your heart’s content and then “true up” or pay at the end of a certain period.

But it’s best to go to the source. There are three guides I use:

This is the simplest guide: http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20Overview%20final.docx

This is the more complicated but more complete guide: http://download.microsoft.com/download/6/F/8/6F84A9FE-1E5C-44CC-87BB-C236BFCBA4DF/SQLServer2008_LicensingGuide.pdf

And this is the phone number I call when I’m stumped: (800) 426-9400

They key is to pay for what you use. All of us have had Instance “pop up” in the organization without our knowledge. If that happens, contact that number and ask what to do. Nobody will come to your organization and take you to court – in fact, you’ll find that we’ll work with you to help you stay current. We’re all headed the same direction – trying to make sure SQL Server solves business problems in your company.