Search results matching tags 'Developer', 'DBA', and 'SQL Server'

A SQL Saturday in Cambridge – Buck Woody’s Ragtime Database Workshop

BuckWoody — Mon, 30 Jul 2012 20:49:00 GMT

The SQL Server community is really engaged. They are an active bunch on Twitter, Facebook and LinkedIn, they help each other on forums, they attend conferences. But that isn’t enough interaction – the community started a grass-roots effort to hold local conferences on a Saturday. Free conferences. Odds are there’s one near you… http://www.sqlsaturday.com/default.aspx … and if not, you can start one.

Sessions at SQL Saturdays are all over the map, and there’s something for (almost) everyone, from Business Intelligence to Database Administration and Development. Some of these events have “training days” associated with them – longer, more in-depth training that has a fee. I’ve taught quite a few of these, and of course I’ve done my share of other presentations at the events as well.

I’m honored to be presenting at the Cambridge, UK SQL Saturday this year (http://www.sqlsaturday.com/162/eventhome.aspx) in September. For one thing, I used to live near there and plan to take the family with me to show them my old stomping grounds. For another, I’m excited about the sessions I get to present.

The Training Day

Friday I’ll be leading one of those training days – and I’ll be delivering a very important workshop, where I’ll cover SQL Server – all of it. OK, maybe not *all* of it, and maybe you won’t be a complete PhD after the class, but we’ll do a complete immersion in learning SQL Server as a product from the ground up. It’s a workshop format, so no sitting and listening to someone droning on and on for hours. You’ll be asked to bring a laptop, and do actual work on the product from the first few minutes of the 8-hour day to the last of the workshop. You’ll be jumping in from the very start, and in deep until the end of the day.

But wait – this is kind of a “beginner” thing, isn’t it? Shouldn’t I be doing something on the internals of the locking mechanism of the hashing system in memory on x64 architectures, with complete code diagrams? Shouldn’t you be able to snatch the T-SQL pebble from the master’s hand when you’re done, or be able to shoot the wings off of an XML fly when you’re done?

No.

We need this kind of session. For one thing, you can get that depth in other sessions. For another, we need to have a place for someone that wants to learn SQL Server but doesn’t have a lot of time to do that. We need something relatively inexpensive that a boss can send a developer, administrator or new employee to learn how to take over the SQL Server, or augment the DBA team. We need a place where good habits are formed, and where someone can branch out into a new part of technology, into one of the best places in tech to be – data.

After you’re done with this session, stick around for Saturday – now all those presentations will make more sense. And you’ll be able to network with a lot of folks that already do what you learn about on Friday, and who knows – find out where to look for work in this amazing career field.

What will you learn?

You’ll start with knowing that SQL Server is a database product by Microsoft. That’s all the pre-requisite you need, other than being the technology industry. From there we’ll cover:

The Data Professional Career
Installing, setting up and configuring the right Edition of SQL Server for the job (including SQL Azure)
Database engine fundamentals – How does the engine work, what are the components, what can you configure and tune
Transactions, Locking and Blocking
Creating and managing databases
Database options and their impact
Database Objects including Tables, Views, Stored Procedures, Functions and more
General maintenance including backups and recovery
Security fundamentals including users, roles, and object security
Performance tuning fundamentals including indexes and query research tools
Multiple resources to help you get to the next level

In 8-hours. Come ready to learn. You’ll need a laptop, and complete focus for a few hours. You’ll leave with the ability to manage and work with a SQL Server system – and you’ll learn what to do next.

Who should go?

If you’re new to Relational Database Management Systems (RDBMS’s) but not technology, and you’re looking to expand your technical reach, coming from another platform (to be sure, there will be some repeat info here), want to explore a new tech career area, want to learn more about developing against an RDBMS or know someone who does.

The registration for the training day is here: https://www.regonline.co.uk/?eventID=1120017&rTypeID=242030

What else are you doing whilst (I love saying whilst) there?

I’m also pleased to be providing the keynote on Saturday. (I can’t wait to see what I’m going to say), as well as two other sessions – more on those soon. My daughter wants to be a Zoologist, so while we’re there we’ll be visiting the Zoology museum at one of the colleges, I’ll probably eat too much and potentially go punting. I’ll also mingle with you, my SQL Family, and we’ll just generally have a good time.

If you’re not busy in September, and even if you are, make plans to come check all this out. It promises to be awesome. (Americans think everything is awesome)

See you there.

The Data Scientist

BuckWoody — Tue, 15 Nov 2011 15:00:18 GMT

A new term - well, perhaps not that new - has come up and I’m actually very excited about it. The term is Data Scientist, and since it’s new, it’s fairly undefined. I’ll explain what I think it means, and why I’m excited about it.

In general, I’ve found the term deals at its most basic with analyzing data. Of course, we all do that, and the term itself in that definition is redundant. There is no science that I know of that does not work with analyzing lots of data. But the term seems to refer to more than the common practices of looking at data visually, putting it in a spreadsheet or report, or even using simple coding to examine data sets.

The term Data Scientist (as far as I can make out this early in it’s use) is someone who has a strong understanding of data sources, relevance (statistical and otherwise) and processing methods as well as front-end displays of large sets of complicated data. Some - but not all - Business Intelligence professionals have these skills. In other cases, senior developers, database architects or others fill these needs, but in my experience, many lack the strong mathematical skills needed to make these choices properly.

I’ve divided the knowledge base for someone that would wear this title into three large segments. It remains to be seen if a given Data Scientist would be responsible for knowing all these areas or would specialize. There are pretty high requirements on the math side, specifically in graduate-degree level statistics, but in my experience a company will only have a few of these folks, so they are expected to know quite a bit in each of these areas.

Persistence

The first area is finding, cleaning and storing the data. In some cases, no cleaning is done prior to storage - it’s just identified and the cleansing is done in a later step. This area is where the professional would be able to tell if a particular data set should be stored in a Relational Database Management System (RDBMS), across a set of key/value pair storage (NoSQL) or in a file system like HDFS (part of the Hadoop landscape) or other methods. Or do you examine the stream of data without storing it in another system at all?

This is an important decision - it’s a foundation choice that deals not only with a lot of expense of purchasing systems or even using Cloud Computing (PaaS, SaaS or IaaS) to source it, but also the skillsets and other resources needed to care and feed the system for a long time. The Data Scientist sets something into motion that will probably outlast his or her career at a company or organization.

Often these choices are made by senior developers, database administrators or architects in a company. But sometimes each of these has a certain bias towards making a decision one way or another. The Data Scientist would examine these choices in light of the data itself, starting perhaps even before the business requirements are created. The business may not even be aware of all the strategic and tactical data sources that they have access to.

Processing

Once the decision is made to store the data, the next set of decisions are based around how to process the data. An RDBMS scales well to a certain level, and provides a high degree of ACID compliance as well as offering a well-known set-based language to work with this data. In other cases, scale should be spread among multiple nodes (as in the case of Hadoop landscapes or NoSQL offerings) or even across a Cloud provider like Windows Azure Table Storage. In fact, in many cases - most of the ones I’m dealing with lately - the data should be split among multiple types of processing environments. This is a newer idea. Many data professionals simply pick a methodology (RDBMS with Star Schemas, NoSQL, etc.) and put all data there, regardless of its shape, processing needs and so on.

A Data Scientist is familiar not only with the various processing methods, but how they work, so that they can choose the right one for a given need. This is a huge time commitment, hence the need for a dedicated title like this one.

Presentation

This is where the need for a Data Scientist is most often already being filled, sometimes with more or less success. The latest Business Intelligence systems are quite good at allowing you to create amazing graphics - but it’s the data behind the graphics that are the most important component of truly effective displays.

This is where the mathematics requirement of the Data Scientist title is the most unforgiving. In fact, someone without a good foundation in statistics is not a good candidate for creating reports. Even a basic level of statistics can be dangerous. Anyone who works in analyzing data will tell you that there are multiple errors possible when data just seems right - and basic statistics bears out that you’re on the right track - that are only solvable when you understanding why the statistical formula works the way it does.

And there are lots of ways of presenting data. Sometimes all you need is a “yes” or “no” answer that can only come after heavy analysis work. In that case, a simple e-mail might be all the reporting you need. In others, complex relationships and multiple components require a deep understanding of the various graphical methods of presenting data. Knowing which kind of chart, color, graphic or shape conveys a particular datum best is essential knowledge for the Data Scientist.

Why I’m excited

I love this area of study. I like math, stats, and computing technologies, but it goes beyond that. I love what data can do - how it can help an organization. I’ve been fortunate enough in my professional career these past two decades to work with lots of folks who perform this role at companies from aerospace to medical firms, from manufacturing to retail.

Interestingly, the size of the company really isn’t germane here. I worked with one very small bio-tech (cryogenics) company that worked deeply with analysis of complex interrelated data.

So watch this space. No, I’m not leaving Azure or distributed computing or Microsoft. In fact, I think I’m perfectly situated to investigate this role further. We have a huge set of tools, from RDBMS to Hadoop to allow me to explore. And I’m happy to share what I learn along the way.

Big Data and the Cloud - More Hype or a Real Workload?

BuckWoody — Tue, 18 Oct 2011 13:57:36 GMT

Last week Microsoft announced several new offerings for “Big Data” - and since I’m a stickler for definitions, I wanted to make sure I understood what that really means. What is “Big Data”? What size hard drive is that? After all, my laptop has 1TB of storage - is my laptop “Big Data”?

There are actually a few definitions for this term, most notably those involving the “Four V’s” Volume, Velocity, Variety and Variability. Others disagree with this definition. I tend to try and get things into their simplest form, so I’m using this definition for myself:

Big data is defined as a large set of computationally expensive data that is worked on simultaneously.

Let me flesh that out a little. To be sure, “Big Data” has a larger size than say a few megabytes. The reason this is important is that it takes special hardware to be able to move large sets of data around, store it, process it and so on. (large set)

If you store a LOT of data, but only use a small portion of it at a time, that really isn’t super-hard to do. It’s mainly a storage issue at that point. But, if you do need to work with a large portion of the data at one time, then the memory, CPU and transfer components of the system have to adapt to be responsive - new ways to work with that data (game theory, knot-algorithms, map-reduce, etc.) need to be brought into play. (computationally expensive)

Once that data is loaded into the processing area (memory or whatever other mechanism is used) it must be worked on in parallel to come back in a reasonable time. You have two options here - you can scale the system up with more internal hardware (CPU’s, memory and so on) or you can scale it out to have multiple systems work on it at the same time using paradigms such as map/reduce and so on. Actually, when you lay this out in an architecture diagram, scale up or out doesn’t actually change the logical structure of the process - in scale out the network becomes the bus, and the nodes become more RAM and computing power. Of course, there are changes in code for how you stitch the workload back together. (worked on simultaneously)

So back to the original question. Is Big Data, as I have defined it here, a workload for Windows and SQL Azure? Absolutely! In fact, it’s probably one of the main workloads, and I believe it represents the latest, and perhaps also the earliest frontier of computing. Jim Gray, a former researcher here at Microsoft and a hero of mine, was working on this very topic. I believe as he did - all computing is simply an interface over data.

Microsoft has multiple offerings on the topic of Big Data. In posts that follow from myself and my co-workers, we’ll explore when and where you use each one. Whether you are a data professional or a developer, this is the new frontier - don’t wait to educate yourself on how to leverage Big Data for your organization.

Hadoop on Windows Azure and SQL Server - Microsoft’s partnership to include Hadoop workloads on Windows Azure and SQL Server/Parallel Data Warehouse (PDW)

LINQ to HPC - Microsoft’s High-Performance Computing SKU of HPC is now in Azure

Windows Azure Table Storage - A key/value pair type storage with full partitioning that is immediately consistent, able to handle huge loads of data and works with any REST-compatible language

Other offerings - Including the new Data Explorer, Project Daytona (with a Big Data Toolkit for Scientists and researchers), Power View and more.

The era of Big Data is here. And you can use Windows and SQL Azure to bring it to your organization.

More Tables or More Databases?

BuckWoody — Wed, 19 May 2010 11:37:22 GMT

I got an e-mail from someone that has an interesting situation. He has 15,000 customers, and he asks if he should have a database for their data per customer. Without a LOT more data it’s impossible to say, of course, but there are some general concepts to keep in mind.

Whenever you’re segmenting data, it’s all about boundary choices. You have not only boundaries around how big the data will get, but things like how many objects (tables, stored procedures and so on) that will be involved, if there are any cross-sections of data (do they share location or product information) and – very important – what are the security requirements?

From the answer to these types of questions, you now have the choice of making multiple tables in a single database, or using multiple databases. A database carries some overhead – it needs a certain amount of memory for locking and so on. But it has a very clean boundary – everything from objects to security can be kept apart. Having multiple users in the same database is possible as well, using things like a Schema. But keeping 15,000 schemas can be challenging as well.

My recommendation in complex situations like this is similar to a post on decisions that I did earlier – I lay out the choices on a spreadsheet in rows, and then my requirements at the top in the columns. I give each choice a number based on how well it meets each requirement. At the end, the highest number wins.

And many times it’s a mix – perhaps this person could segment customers into larger regions or districts or products, in a database. Within that database might be multiple schemas for the customers. Of course, he needs to query across all customers, that becomes another requirement.

Use Those Schemas, People!

BuckWoody — Tue, 18 May 2010 12:39:35 GMT

Database Schemas are just containers – they aren’t users or anything else – think of a sub-directory on the hard drive. In early versions of SQL Server we “hid” schemas, placing all objects under “dbo”, which gave the erroneous perception that Schemas are users.

In SQL Server 2005, we “un-hid” or re-introduced schemas within the database. Users can have a default schema (a place where their new objects go), you can add new schemas and transfer objects between them, and they have many other benefits.

But I still see a lot of applications, developed by shops I know as well as vendors, that don’t make use of a Schema. Everything is piled under dbo. I completely understand this – since permissions can be granted to a schema, they feel a lot like a user, so it’s just easier not to worry about both users and schemas when you create a database. But if you’ll use them properly you can make your application more understandable and portable.

You should at least take a few minutes and read more about them – you owe it to your users: http://msdn.microsoft.com/en-us/library/ms190387.aspx

SQL Server for the Oracle DBA Links

BuckWoody — Wed, 28 Apr 2010 14:29:00 GMT

I do a presentation (and a class) called "SQL Server for the Oracle DBA". It's a non-marketing overview that gives you the basics of working with SQL Server if you're already familiar wtih how Oracle works. This class and these links DO NOT help you with "Why should I use Oracle/SQL Server instead of Oracle/SQL Server" - I'll assume you're already there, and if not, there are LOTS of sites to help you make that decision.

Although these links might contain slight marketing slants (I don't control them) I've tried to get the best links I can. Feel free to comment here to add more/better links.

As such, these aren't links that help you work with Oracle - they are links to help you work with SQL Server. Some of them contain more information than you actually need, others don't have near enough. Taken together (and with the class) you're able to get done what you need to do.

"Practical SQL Server for Oracle Professionals" - A Microsoft Whitepaper, probably the best place to get started: http://download.microsoft.com/download/6/9/d/69d1fea7-5b42-437a-b3ba-a4ad13e34ef6/SQLServer2008forOracle.docx

Free Training: http://technet.microsoft.com/en-us/sqlserver/dd548020.aspx

Classroom training (will cost you): http://www.microsoft.com/learning/en/us/course.aspx?ID=50068A&locale=en-us

Terminology Differences: http://www.associatedcontent.com/article/2383466/oracle_and_sql_server_basic_terminology.html

Datatype mapping between Oracle and SQL Server: http://msdn.microsoft.com/en-us/library/ms151817.aspx

The "other" direction - can still be useful for the Oracle professional to see the other side: http://blog.benday.com/archive/2008/10/23/23195.aspx

Process Improvement and the Data Professional

BuckWoody — Tue, 06 Apr 2010 13:23:48 GMT

Don’t be afraid of that title – I’m not talking about Six Sigma or anything super-formal here. In many organizations, there are more folks in other IT roles than in the Data Professional area. In other words, there are more developers, system administrators and so on than there are the “DBA” role.

That means we often have more to do than the time we need to do it. And, oddly enough, the first thing that is sacrificed is process improvement – the little things we need to do to make the day go faster in the first place. Then we get even more behind, the work piles up and…well, you know all about that.

Earlier I challenged you to find 10-30 minutes a day to study. Some folks wrote back and asked “where do I start”? Well, why not be super-efficient and combine that time with learning how to make yourself more efficient? Try out a new scripting language, learn a new tool that automates things or find out ways others have automated their systems. In general, find out what you’re doing and how, and then see if that can be improved. It’s kind of like doing a performance tuning gig on yourself!

If you’re pressed for time, look for bite-sized articles (like the ones I’ve done here for PowerShell and SQL Server) that you can follow in a “serial” fashion. In a short time you’ll have a new set of knowledge you can use to make your day faster.

It’s OK to take a Shortcut Sometimes

BuckWoody — Mon, 08 Mar 2010 14:06:12 GMT

I was working this weekend with a fairly simple Excel spreadsheet, and I had to decompose one cell in it out to three columns in a SQL Server table. There are tools within SQL Server Integration Services (SSIS) that should be able to do that, but I just couldn’t find my way around them properly.

I’m not as familiar with SSIS as I would like to be – it’s just not my day-to-day tool. I was struggling with the split of the cells, since it had several different types and kinds of delimeters for the data. I knew how to do it with regular expressions, I knew how to do it with Transact-SQL, I knew how to do it with lots of programs, but I just couldn’t figure it out in SSIS. I’m sure that’s my fault – not the fault of the tool, by the way.

I spoke with the person that created the spreadsheet, and it turns out that he had combined those fields from his source to begin with! In other words, he could split them out for me. What a relief! Yes, I need to come back some time and figure out how to beat this, but right now I just needed to get the work done. He split the cells out for me in the spreadsheet (took him about 3 minutes) and then I had the three discreet cells I was looking for.

Sometimes it’s OK to take a shortcut. As long as you think it through, you can safely do things the “easy way”, when you’re careful and understand what the ramifications are. The key here is that this was a one-time import, I understood both sides of the data, and had done my research. The data itself didn’t change; just it’s cell location. And that was all OK.

So don’t make it harder on yourself than it needs to be. Consider the alternatives – and take the help where you safely can.

Lessons from Volunteering

BuckWoody — Tue, 23 Feb 2010 14:31:01 GMT

Saturday I volunteered at a work-day at our church. Our building is one of the first buildings built in this area of Washington, so it’s a beautiful old structure, which of course means there’s lots of maintenance.

I’m fairly decent with tools, I’ve done some woodworking in my day, and I’ve even rebuilt a car or two. But working on a structure this old requires some special skills, skills that I don’t (or didn’t) have. And as I re-glazed a stained-glass window into an ancient window opening, I was not only humbled, I thought about how the entire project was handled and how I could learn about being a better DBA. I decided to share those lessons I learned here.

Not everybody knows what you do
This seems obvious – but I’m so often on the “knowing” end of a project that it’s easy to forget. Several of the other people at the workday knew what went where, how things worked, and what to do to take something out or put it back in. Even with my construction skills, I felt a little overwhelmed. Happily, the people that were there were VERY gracious and helped me do what I could. The nice thing is that they did not talk down to me or treat me like an idiot – they found out what I did know, and then mapped that to what I could do. They helped me learn, and by doing that I became useful to them.
This is also true in our career field. Are you having trouble between developers and data professionals? Buy a pizza, have a lunch and each team should take a turn at learning what the other knows, and what they would like them to know.

If you’re good at something, lead
One of the people at the workday was an older gentleman who was amazing in his knowledge. He’s a contractor, who’s dad trained him in construction starting at 5 years old! And in fact, his dad (and him) actually helped build this church. He’s an amazing guy. But he was running from task to task, doing all of the “hard” things. So I pulled him aside, and told him that we couldn’t do what he could do – but we were all willing hands and heads. I explained that if he would be willing, he could lead us. I asked him to group us into skillsets, and then come help each of us as needed. He agreed, and soon we were all productive.
You should do the same. If your kid’s school needs a “teacher for a day”, step up. Teach the kids what you know about databases. You’ll be surprised! Don’t be afraid to take a leadership role where you are.

Break things down into simple tasks
To do the two things above, you need to plan a little. The gentleman that helped us at first gave us things like “re-glaze that window.” After we stared at him blankly, he broke down the work into the steps of heating the old glaze with a torch, gently removing the old glaze, replacing the section of glass, and re-applying the glaze, all the while showing us the little parts of those tasks that keep the glass from shattering.
One of the easiest things to do when you’re a technical professional is to “glaze over” all of the little steps into a large one. You’ll need to learn to break things down into the simplest components, or at least the simple components that are needed for your workers.

I hope these tips are useful – I’ve been applying them each day.

Code that Writes Code - A Good Idea or Not?

BuckWoody — Tue, 16 Feb 2010 14:14:00 GMT

I’m a big fan of code that writes code – most of the time. For instance, whenever you use the “templates” feature in SQL Server Management Studio (SSMS) or the Maintenance Wizard, you’re using code that writes other code. There’s even a trick of writing Transact-SQL (T-SQL) code that in turn creates other code.

But there is a class of code that writes code that I’m more cautious about. Whenever a program “automatically” generates a database schema, I begin to get nervous. No, I’m not talking about Entity Relationship Diagram (ERD) tools such as those from Quest and Embarcadero, I’m talking about things like NHibernate and other coding paradigms that “abstract” the database layer away from the developer.

I have two reasons that I’m not impressed with these programs and paradigms.

First, they do not take the entire solution into account. As data professionals we learn our platform (whether that’s XML, flat files, SQL Server, Oracle, IBM, whatever) and we study how each of the features maps to a complete solution. I might choose to use Replication, Service Broker, Clustering, FileStream, or any number of features to completely remove the need for code in that area. And by making those choices, I change the design of the database accordingly, based on the solution. The abstraction tools don’t – they just spit out the same Data Definition Language (DDL) statements they know how to create, without thinking about maintenance, speed, reliability or anything else.

When I mention this to the developer, they say “just put that in later” – and that’s the beginning of woes for the data professional. Most of the time you can’t put things of a fundamental nature in later. In some cases, it’s a complete tear-down and re-write of the entire database. Very painful, and something you never want to experience.

The second main reason that I am skeptical about tools that automatically create DDL statements to create databases is that they don’t do a good job. Once again, this is because of a holistic view – the tool doesn’t have the capability to take everything into account, including the data pattern, so it has no idea how far to normalize, where to put files and filegroups, what kind (if any) indexes are needed, or when to choose between a natural or surrogate key.

Is all this just because I’m a DBA, and anal about my databases? No. It’s a business problem, because these tools continue to separate the DBA from the Developer, and both from the Business Analyst. And it's the business that suffers when developers (or DBA’s) take shortcuts. I wonder how much time and money are wasted in business re-writing databases because of a shortcut using poor tools?

So let’s do the hard work – let’s let the business requirements dictate the solution, rather than the other way around. Even for the “little” databases, which of course never stay that way.

Now, does this mean I hate NHibernate or the Entity Framework? Not at all! Just work with your data professionals instead of without them. You’ll find that by bringing the best practices of these ORM tools together with a well-designed database, you’ll deliver a solution to the business that is fast, reliable and safe. Isn’t that what we all want?