How quickly can you copy data from one place to another?
There’s no magic solution for many of the problems we face in SQL Server. Whether we are trying to maintain concurrency under high write loads, restore databases quicker, or ensure extremely high availability we need to make trade-offs. Sometimes those tradeoffs are frustrating, and people look to other solutions like NoSQL, not realizing that they are just making different trade-offs.
I see the frustrations and comments constantly on the SQLServerCentral forums, and one recent discussion was no exception. In this particular debate, a user was frustrated by the requirement to copy backup files across the Internet to another location. The transfers were taking too long and the poster were trying to find some magic way of decreasing the transfer time. Increasing the bandwidth, the simplest solution, wasn’t an option, and that usually isn’t easy to do in a corporate environment. The poster seemed to think there had to be some magic way of reducing the time it takes to copy large backup files, and there isn’t any magic solution. The things that help reduce transfer time are often the common things we think of.
Compression is an obvious solution. There are numerous free programs to compress files and even software that will compress the backups as they occur. There are techniques in Windows for speeding up copies, though many are Windows version dependent. Beware, however, since copies from your server might cause you memory issues. However there might be other, more creative solutions that people come up with.
Imagine that you track the transfers, keeping meta data about the process. Perhaps you can find a way to send less data overall the next time. In some sense, this is the idea behind log shipping. If you only send the changes (the transaction log backup), you might save a tremendous amount of time and resources. That assumes, of course, that the changes in a period of time contain less data than the overall database. That might not be the case in some workloads.
There are other services that might help. Imagine that you back up to the cloud, either because you use a tool, feature, or service if your application exists in the cloud. Spinning up a new instance with a copy of that data in the cloud might eliminate the need for transfers completely if the other location can just access the data in the cloud.
I’m sure some of you have other ideas, and I’m sure many of us would like to hear what creative ways you might have for moving data between locations.
Steve Jones
The Voice of the DBA Podcast
No podcasts today due to some personal issues. The podcasts should return tomorrow.
Are you too fearful or just paranoid enough?
I’m a conservative DBA. I get nervous when backups aren’t running, code isn’t in source control, and developers have access to production systems. I’ve had too many late night pages and weekend phone calls, not to mention many extra hours spent in the office from changes to systems that didn’t go well. That latter item leads me to limit the number of changes I make to systems whenever I can, including avoiding applying Cumulative Updates to SQL Server.
When I read an editorial from Glenn Berry, I had to stop and think of whether or not I had a healthy respect for the problems that can occur from change, or if I was being overly conservative (or fearful). Glenn makes a good point that so many people do not upgrade or change their drivers, firmware, or other software. People don’t patch their SQL Servers, even with Service Packs. I’m sure some of that is fear, but some of it is neglect as well.
For me the decision usually comes down to examining the reward/risk ratio, trying to understand if improvements are balanced by the risk of downtime. I do value stability above new features, mostly because if problems do occur, I will be the person fixing them. That doesn’t mean I avoid all changes. I think Service Packs need to be installed, though not necessarily the first month. I’ve also come to embrace some of the continuous integration (CI) and continuous deployment (CD) ideas as ways to both reduce a software inventory as well as hold developers to a higher quality standard. However if you want to deploy (and perhaps patch) in a continuous deployment environment, then you should ensure that your CI process performs strong checks and make sure your developers are holding themselves to a high level of quality.
We change the way we work, and the tools we use in technology often. Change is a concept we embrace, and we should since the ways in which our systems work are regularly changing. Bugs are patched, new techniques and tools are developed that should make us more efficient and productive. Those don’t always work, and we should be wary, but we should also not fear change. We should evaluate each new possibility with the attitude that our decision to move forward “depends.” It depends on the ease with which we can integrate something or apply a change, and the ease with which we can roll back our changes if they do not perform as expected. It also takes practice to ensure that all those things are easy.
Steve Jones
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast - 18.0MB WMV
- Watch the iPod Video Podcast - 21.2MB MP4
- Listen to the MP3 Audio Podcast - 4.3MB MP3
The CHOOSE command is new in the T-SQL as of SQL Server 2012. I hadn’t ever had the chance to work with it, but after seeing someone submit a piece recently, I decided to play with it a bit.
This feature is essentially an indexing value from an array. Here’s a short example.
SELECT CHOOSE(1, 'First', 'Second', 'Three');
The first parameter to the CHOOSE() function is an integer, which is the position. The second (and subsequent) parameters are the array of values. Here’s a better example. Let’s say I have a lookup table of titles:
CREATE TABLE titles ( titleid INT , title VARCHAR(20)) ; INSERT titles VALUES (1, 'Manager') , (2, 'Developer') , (3, 'DBA') , (4, 'Sysadmin') , (5, 'Storage Admin') , (6, 'Help Desk') ;
I might potentially have a very large table. Imagine that I work at SalesForce.com or I have some type of EAV table here. If I am performing a join with employees, I could easily do this:
SELECT e.firstname , t.title FROM dbo.employees e INNER JOIN dbo.titles t ON e.titleid = t.titleid
However, suppose I have performance issues, or I’m joining to lots of tables. Perhaps I’d prefer not to actually join to another table for some reason. I could do this instead:
SELECT e.firstname , CHOOSE( e.titleid, 'Manager', 'Developer', 'DBA', 'Sysadmin', 'Storage Admin', 'Help Desk') FROM dbo.employees e
Is this useful?
I’m not sure. Most of the examples and places I can think of for using this are rather trivial, or inflexible. This seems like hard coding values into a procedure or function when a table join might be a better option.
I haven’t found any blogs that present practical uses that make sense, but I’m sure some are out there. Let me know if you know of any.
UPDATE: Rob Farley mentioned he uses this in the date dimension of data warehouses. I’m not sure if that’s the best solution, but Rob’s a smart guy, so I’ll take his word that this is a good use of the function.
Here’s my Airplay receiver.
I ran across this piece on the joy of being a programmer, which resonated with me. I started at a programmer, at about the same age, 10 or so. I’m a little older than the author, but I’ve spent most of my life in the technology business, primarily working with SQL Server, but I’ve built software as well. I’m still building a little software, mostly for fun. I recently set up a Raspberry Pi to stream Airplay music from my idevices and created a ball shooting Lego Mindstorm robot. Granted the latter wasn’t much programming, but I’ve backed a Kickstarter project that will allow me to write some more complex code for the Legos with my kids and I’m looking forward to receiving the kit.
Programming has been a fun hobby and career for me. Whether that’s been working in a front end language like C++ or VB, writing T-SQL that produces reports, or even scripting administrative tasks in VB Script or Powershell, it’s always a challenge. I find myself going through the same pattern of emotions over and over. Excitement as I start a project, confidence in an initial solution, frustration when things don’t work, and a smile after the burst of creativity that finds a solution. Granted, I may iterate through these emotions a few times, perhaps even spending more time struggling than solving, but it’s been an endeavor that I enjoy most of the time.
Just like the author, I’ve had success in my career, and found technology to be a better job than many other jobs that people I’ve worked with have had. Looking at the accountants, the managers, the salespeople, and all the other workers in the various organizations I’ve been a part of, I think I’ve had one of the better jobs that exist in the world today. Physically easy work, mentally challenging, well paid, and with demand for workers. I hope you feel the same way about your technology career.
Steve Jones
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast - 14.8MB WMV
- Watch the iPod Video Podcast - 17.8MB MP4
- Listen to the MP3 Audio Podcast - 3.6MB MP3
Would you trust software to drive you around?
We are increasingly living in a world that is affected by software (and the data it uses). I have to admit that I’m slightly worried about this as I look at the quality of software, the bugs, the lack of effective testing. It’s not that companies don’t perform a lot of testing; many do, but it seems that many developers, and even QA people, don’t really know how to effectively test. This is especially true of testing for abnormal or unexpected situations. It there’s one area of software development that seems woefully immature, it’s testing.
However testing isn’t the only problem. We might not like the way that software is designed, and used, to alter our world. As we become more dependent on software to accomplish tasks for us, we will run into new situations that the software may not have been designed to handle. This opinion piece on the ways in which software might alter our lives is both fascinating and scary. How do we want software to behave in unfamiliar situations? It’s not an easy question to answer, and it’s certainly not a question with a simple answer in many scenarios.
We learn to depend on systems when they work for us. Whether in business, government, or our personal lives, when a system works, we want to use it more. However that’s not always the best long term solution. Humans adapt, and software, especially the systems using a constant stream of historical data for decisions, has flaws. More than a few of you might have encountered this type of situation with Netflix or Amazon recommendations when you have kids sharing your account.
In some sense I think that we not only need adaptive algorithms, that we can customize over time, but we also need to understand the data that flows through the algorithms. We need ways to remove some data from consideration by the software, when we find it is removing more value than it is adding.
Steve Jones
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast - 16.0MB WMV
- Watch the iPod Video Podcast - 19.0MB MP4
- Listen to the MP3 Audio Podcast - 3.9MB MP3
About 3 or 4 years ago I was talking with my wife and a colleague of hers that worked in the mobile industry. This person and my wife thought that the mobile industry would explode, and it’s impact and expansion would dwarf the PC revolution that had most of our companies buying new machines every two to three years.
I think that is part of what is happening with this article on the hemorrhaging of the PC industry. I see quite a few people moving to the mobile space, and using smart phones, texting, and more to handle the needs of their lives. The growth of tablets as well is changing the world. I see more and more non-technical adults carrying tablets to do their work. Kendall’s volleyball coach, our Scoutmaster, and more are using tablets, and ( I am guessing here), sticking with an older computer at home when they need it.
There’s also the factor that we can get so much done on any device that we don’t need that many. These days email, video, audio, browsing, and camera work can happen on almost any device.
The use of older hardware is another issue. We don’t see the fantastic pace of growth in hardware occurring anymore. I know machines get more powerful, but it doesn’t seem to be happening as quick as in the past. Part of this may be psychological with the clock speed ratings not changing as quickly, but it’s also that the technology seems to be focusing on power improvements more than anywhere else. Even the core growth has tailed off. I just bought a laptop, 4 years after my previous Windows laptop. They both had quad core chips, and while the new one seems to be faster, it’s not substantially faster.
Hardware not speeding up, tablets and phones, but there’s something else as well. Microsoft did a fantastic job with Windows 7. In many cases people are finding that the OS is faster and slimmer, without the bloat of previous versions. One can actually put Windows 7 on older hardware and it will run great. I know lots of people are still running Windows XP, which works fine, but even if they upgrade, they don’t need to move to new hardware o do so. I suspect that other OSes are in similar situations. The software is so good, there’s no reason to upgrade.
All of this isn’t good or bad. It just means a changing industry, and PC makers should both learn to adapt to new form factors (tablets), but also expect that we will buy fewer devices in the future.
Tomorrow is SQL Saturday #200 in Philadelphia. I’m traveling today to the City of Brotherly Love for the event. My partner, Andy Warren, co-founder of SQLServerCentral and SQL Saturday, is also making his way there for the celebration. This is the bicentennial event, and even though it might not be the two hundredth event to actually occur, it represents an amazing success of the SQL Saturday franchise and I am thrilled to be a small part of these events that have helped train, teach, and network so many SQL Server professionals all around the world. We even had Bill Gates learn of the events at SQL Saturday #175 in Fargo just a month ago.
When the first SQL Saturday took place in Orlando in 2007, Andy and I weren’t sure how it would survive or grow. We wondered if we’d ever see 50 total events, or even 12 occur in a single year. Five and a half years later, these events have exploded, with over 80 taking place in the last year. Under the stewardship of Karla Landrum, more SQL Server professionals are getting free training every year than they ever might have dreamed of.
And they’re excited by the opportunities offered by these events.
I’ve seen people drive from Jacksonville to Pensacola for an event. People come from Virginia to Ohio for a day. Plenty of other people adjust their travel plans to come a day early or stay a day late to attend a SQL Saturday. We’ve had attendees and speakers bring their spouses and children, and even sessions they could participate in. Hundreds of speakers have donated their time and money, traveled long distances to help others. We’ve had networking sessions, games, BBQs and water skiing scheduled at SQL Saturdays. Summer, fall, winter, and spring, in every month, in over 20 countries, the SQL Server community has come together to teach, learn, debate, practice, and take pride in the work we do.
I’m proud of what we’ve done together and amazed at how often we have done it. I’m looking forward to tomorrow and all the events that will come in the future.
Steve Jones
PS – Putting together a SQL Saturday isn’t too hard. Some events are extremely well organized and funded, but really if you can find a venue that will allow you to use a few rooms, you’re set. Speakers will come, you can charge for lunch, and people will learn. That’s what it’s all about. Contact Karla Landrum if you want an event in your area.
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast - 19.1MB WMV
- Watch the iPod Video Podcast - 22.0MB MP4
- Listen to the MP3 Audio Podcast - 4.6MB MP3
After creating my Azure account, I wasn’t sure where to go next. Fortunately I had an immediate project that occupied my attention: a public Adventureworks database.
As we noted, Red Gate and SQLServerCentral agreed to host the database and cover costs. Once Jamie Thomson transferred the database to my subscription, which didn’t seem too hard, although slightly confusing, it showed up in my list of objects.
I drilled down to the sql databases to ensure this was the only object there, and it was.
I wasn’t sure what I could do, so I clicked on the database. I do tend to be an RTFM guy at the beginning, but I was curious how easy they’ve made things. This is what I saw:
It’s a good list of things, although since I have SSDT installed, I didn’t need the first item. I guess if you have SSDT installed you would know it, and the link is handy. The interesting thing for me was the “Server” string at the bottom of the panel.
I wonder if it works.
Jamie had given me the admin name and password, and I plugged this into Management Studio, along with the connection string.
Sure enough, it connected, and changing to the AdventureWorks2012 database, I could query the objects.
I decided to test Object Explorer as well, and that was interesting. I had a very cut down version of what I normally see for a SQL Server instance.
Most of the “instance” level stuff was gone. I could see the master database, and my own database, but no others.
Since this was a new instance, one of the first things I decided to do was create my own login account. I right clicked “Logins” and selected “New Login”. I go this:
A template for a script. That’s interesting. No GUI version of the login creation dialog. I used CTRL+Shift+M to fill in the template stuff.
That’s not my password, but I did choose a nice, long one. I executed the script to create a login and then went to add a user. Once again, I got a script from the GUI.
I changed this and created my user to map to my login.
This let me log into the system, and set myself as a db_owner. However I wasn’t an administrator. That’s for another post.
We should be able to deploy a new SQL instance as easy as this.
As database administrators we seem to be slow to embrace new technologies and paradigms. There was a lack of enthhusiasm from DBAs for SANs years ago, and virtual machines more recently, at a time when many other technology professioanls were embracing these ideas. Even today there is resistance from some people, and sometimes with good reason. SQL Servers are not like other servers and have much different hardware requirements. Too often the virtual machine and storage administrators do not appreciate that SQL Servers need different architectures.
Lately the cloud services push is seeing lots of resistance from DBAs. Various vendors and media hype the idea and potential savings, which then convince management that systems need to be moved or built in the cloud. There are some applications that fit better in the cloud, but not all of them. I certainly don’t want sensitive information in the cloud, at least until we work out some of the legalities for who owns, controls, and responds to subpoena about data.
The applications that make sense seem to be those that are distributed with lots of paying clients. I ran across a blog that looked at some of the companies that have moved into the Azure cloud with pieces of their businesses and been quite pleased with the results. Quite a few of these are cost-scalable by clients, meaning that a new user or customer is paying some fee that makes it economical to add new databases and servers for that client. If they leave, you can shut down their server.
It’s definitely easier to start an application in the cloud rather than move an existing one. The architecture is different from an on-premise application, and that means code changes. Not something many companies want to engage in, given the past success of such projects. However the cost savings can be significant for new projects, if those new projects involve investment in equipment, facilities, people, or some combination of all of those.
However one of the biggest items I see mentioned why companies like cloud platforms like Azure is the speed of deployment. To me that means that the IT infrastructure people, from storage to admin to DBAs, are falling down on the job. There’s no reason we can’t deploy new machines as quickly as Azure these days.
Steve Jones
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast - 7.1MB WMV
- Watch the iPod Video Podcast - 21.7MB MP4
- Listen to the MP3 Audio Podcast - 4.4MB MP3
Vote today! See if we can help MS help us.
I think SQL Server is a fantastic product. I’ve been using the platform over 20 years, across 8+ versions and 2 operating systems. It’s provided me with a great career, and one I’ve enjoyed. However the software isn’t perfect, with numerous bugs and holes. The Connect system was introduced years ago as a way for people to submit bugs and suggestions, vote on them, and provide feedback for Microsoft.
Does it work? I think Connect is broken, and while I sympathize with the volume of suggestions that Microsoft must deal with, I’d hope that they actually listen, consider, and act on something other than documentation bugs. Those seem to be the only ones acted on lately.
Most of the items submitted are sent in by individuals, and they get one or two votes, but that’s usually from the submitter’s friends. Many of the items are, IMHO, not important enough to submit to Connect. My guess is they cause plenty of people in Microsoft to view Connect as a slop bucket where most users toss every complaint they have about the product. I sometimes wonder if items are submitted by users before they even spend a few minutes on a search engine trying to resolve their problem.
However there are some great suggestions, and this is one. It’s asking for a new virtual table: Errors. This is in addition to the inserted and deleted tables. It’s not just that I think so, but there are over 300 people that have voted it up. It’s the number 6 highest voted item on Connect. Users see value in this, especially data warehousing users. If you read the comments, this thread, or this blog, you might agree. If so, then I’d encourage you to vote it up. If not, vote it down. Either way, leave a comment on why this would help (or not help) your work. I don’t know if this will change Microsoft’s mind on the issue, but it would be interesting to see if they respond with anything other than there are “resource constraints” preventing this from being implemented.
While you are at it, look over the list of top voted on items. There are some good ones, like fixing this error, create or replace, and enhancing the OVER clause. If any of them would make your job easier, let Microsoft know. They always say that customer problems and situations give them reasons to enhance the product or fix issues. Let’s give them a chance to actually prove that statement is true. Participate in our experiment and vote today.
Steve Jones
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast - 19.7MB WMV
- Watch the iPod Video Podcast - 23.2MB MP4
- Listen to the MP3 Audio Podcast - 4.7MB MP3
