Replication Bug with Partitioned Tables

Recently, we came across a bug in SQL Server 2005 on one of our production servers. Apparently, if you execute an ALTER TABLE statement on a replicated table with more than 128 partitions, the log reader will fail. A relatively obscure bug, I know. Microsoft has recognized this as a confirmed bug, but I couldn’t find it anywhere on the intertubes, thus the inspiration for this blog post. Microsoft’s official solution for this issue is to upgrade to SQL Server 2008.

For various reasons, we were unable to execute an upgrade at the time. And since this was a 2 terabyte database, we wanted to come up with a solution that wouldn’t involve reinitializing the entire publication. Our quick-fix while we were troubleshooting the issue was to create a linked server to the production box. Not ideal, I know, but it worked in a pinch and minimized exposure of the issue. Fortunately for us, we were able to solve the problem on the publication database pretty easily. All of the affected partition functions had empty partitions created several months in the future, so we simply merged any empty partition ranges for future dates. Our solution to our now-out-of-date subscribers was to apply static row filtering to any table with more than 100 million records. While this would introduce some overhead with the replication of these tables, it would allow us a much faster recovery time. We decided to use the start of the most recent hour as our filtering criteria, just to give us a “clean” filter, so we had to delete data from any table where we were going to apply the filter. After that, it was simply a matter of resuming replication.

All things considered, it took us a little over a day to recover from the issue. Most of that time was spent troubleshooting the problem and identifying a workable solution; actual execution of the changes was pretty quick. Moral of the story? Upgrade to SQL Server 2008. :)

Live Blogging: Women In Technology Luncheon

I was invited to live-blog for the Women In Technology (WIT) luncheon at the Summit on Tuesday.  As was the case with all of my “live blogging,” I mostly updated Twitter with near-real-time updates, which I then attempt to transcribe into a blog post for later reference. So here follows that transcription. :)

11:49 AM PST
The room is filling up nicely! There’s a nice distribution of men and women in the room. This is great! I’m actually pleasantly surprised at the number of women at the Summit.  If it weren’t for this luncheon, I probably wouldn’t have even noticed just how many SQL ladies there actually are.

12:00 PM PST
The WIT luncheon is underway! We start off with watching an energetic video displaying various types of women and men who each state “I am a technical woman” or “I support technical women.”

Rushabh Mehta, Executive VP of Finance for PASS, opens the WIT luncheon with the statement “PASS Supports Technical Women.” He then hands the floor over to Wendy Pastrick (@wendy_dance) for moderation of today’s panel.

Today’s panel is:

12:10 PM PST
Kathi starts off with a great message: “I want to encourage women to enter technology, but I want to encourage boys too.” She started off as a pharmacist because “I was probably born 5 years too early” but later switched to technology. She makes less money but enjoys it more. Her daughter had to learn HTML for school and actually helped her get her first work in IT by showing her how to program. “I want both boys and girls to have the opportunity to discover lots of different things and to find what they love. And hopefully that’ll be technology.”

12:17 PM PST
The floor is then handed over to Jessica Moss, BI guru extraordinaire. She gives a great example of how influential a father can be in a young girl’s life: her father was the one who got her interested in technology and who encouraged her career. She says she never felt like she could *not* be technical because she was raised to believe she could do anything. She ends with a challenge for everyone at the Summit: talk to just one young woman and encourage her interest in technology.

12:23 PM PST
Cathi Rodgveller shares her background in education and how she started IGNITE (Inspiring Girls Now In Technology Evolution). The goal of IGNITE is to excite young women, minority races, and low-income youth, about technology and about technical careers. Rodgveller gives us a powerful message: “You can have an impact in your community. One [positive technology] event can change a young girl’s life.”

12:28 PM PST
Last, but certainly not least, Lynn Langit takes the floor. She starts off with a challenge to all audience members: tweet or text one person to say “I’m a technical women” or “I support technical women.” The room gets active while people are busy typing or texting, and Twitter is abuzz with various tweets and retweets. Lynn then takes the floor back and talks about her background and about her charity work. She mentions that every time someone buys one of her books, a donation is made to the MONA foundation. Langit also shares some of her experiences as a technical women: “I’m a developer evangelist. I’m often the only woman in the room, and I’M the one giving the presentation.”

12:35 PM PST
It’s now time for Q&A with the audience. I’ve also invited members of the Twitter community to send in their questions or comments, and we’ll do our best to get them answered. Following is a brief summary of the questions and answers provided:

Q: First up is a father of 2 teenage girls. He wants to know why WIT programs have continued to fall since 1985.
A: WIT is a low priority for schools. Schools have so many other priorities, and not enough time or funding, to address everything they need to. We need intervention from outside sources to stimulate change and ensure it’s being addressed. Rodgveller is working with her state Senator to try to enact change on the national level.

Moss mentions that studies show the top 2 issues for WIT are recruitment and retention. She also points out that middle school years are very formative and important for young women to foster their interest in technology. Rodgveller interjects that even high school is not “too late” to inspire young women.

Q: Another father asks, how can he remove stereotypes for children?
A: Parents are the best resources, period. Parents need to support their children at home and to let them know that stereotypes are negative and not okay. This includes not just gender issues, but also issues of race, religion, sexual orientation, etc.

Q: Today’s youth are concerned about the technical job market in US because of the prevalence of off-shores outsourcing. Is it still a good idea for young people to join today’s technical workforce, and how can we encourage them?
A: There are still plenty of opportunities in IT. In fact, one of the hottest trends today in technology is BI. The best way to ensure that your job is not outsourced is to stay relevant and keep up with the newest technology; those are not the jobs that are outsourced overseas.

Q: In the South, there are still lots of stereotypes. For instance, women frequently are not hired by companies for technical positions. Comments like “We can’t hire a woman for that job because it’s too valuable; what if she gets pregnant?” are still made. What can be done about this?
A: One of the executives from CA, the sponsor of the WIT luncheon, takes the stage to answer. He says in no uncertain terms that, at his company, those individuals making the disparaging remarks would be terminated. He says that the type of attitude described has to come from the top down, and the company is limiting itself by not hiring women. He ends with a message for employees to not tolerate discrimination and to go to HR whenever they see it happening.

Q: What can parents do to help WIT?
A: Parents are the greatest resource kids have. Parents set the attitude for their kids; if your attitude is positive, it will encourage your daughter to try new things and will open her up to opportunity whenever it presents itself. Also, parents need to raise the issue with schools, i.e. through PTA meetings, to make them realize that it’s important to you and it’s important for your children.

Moss: “But at the end of the day, it is the parent’s responsibility to expose your children to as much technology and as many experiences as possible.”

Kellenberger: “It is up to the parents to break stereotypes, for jobs, gender, race, etc. It’s the parent’s attitude that makes the difference.”

So that’s all I have for the Women In Technology luncheon. There was a lot of great content and some very positive messages from our panel. For more information on this topic, please check out the following resources:

Live Blogging: Keynote at PASS, Day 3

Today is the 3rd and final day of keynotes at the PASS Summit. Following is highlights of the keynotes. During the keynote, refresh often for updates!

8:36 AM PST
Keynote kicks off with Don’t Stop Believing by Journey. Awesome.

8:38 AM PST
PASS VP of Marketing Bill Graziano just takes the stage. He promises the shortest keynote of the conference, and appears to deliver on it. First up are Board announcements.

Outgoing Board Members are:

If you these folks at Summit, make sure to thank them for their hard work!

PASS President Wayne Snyder comes out to honor and thank Kevin Kline for his 10 YEARS of service.  Yes, that’s right, 10 YEARS. Wayne doesn’t get far into his speech before he gets choked up.  Great quote from Wayne:  “Kevin (@kekline) is a man of honor and integrity. He’s… well, he’s full of it.”  Kevin then gets a well-deserved standing ovation from the entire audience.

New Directors-at-Large are also announced:

Next year’s PASS Summit is also announced.  It will be in Seattle from November 8th – 11th, 2010. The decision was made to have the conference in Seattle because it’s a launch year, so access to Microsoft employees will be invaluable.  The registration rate is $995 if you register soon. Details and registration can be found on the PASS site at www.sqlpass.org/summit/na2010.

8:52 AM PST
Dell keynote just started with Patrick Ortiz, Solution Architect with Dell’s Infrastructure Consulting Services for SQL Server & BI.  The keynote is pretty uneventful.

9:17 AM PST
Woot! Dr. David DeWitt, Technical Fellow, Data & Storage Platform Division at Microsoft, takes the stage. His presentation is entitled, “From 1 to 1000 MIPS.” He promises a very technical talk, against Microsoft Marketing’s wishes. He’s not going to be announcing any products, but instead plans to discuss the changes in database technology and what’s in store for us in the next 10 years.

Highlights (or at least, the ones that my simple mind was able to grasp):

  • Basic RDMS design is essentially unchanged, but the hardware has changed dramatically.
  • Interesting statistics in disk trends last 30 years: 10,000x capacity, 65x transfer rate, 10x avg seek time… not balanced at all
  • “CPU’s and disks are totally out-of-whack in terms of performance.”
  • The benefits of 1,000x improvement in CPU is almost negated by lack of improvement in disk
  • Transfer bandwidth/byte trends: 1980 = 0.015, in 2009 = 0.0001… 150x slower today! “It’s like trying to provide drinking water for the town through a garden hose.”
  • “Can incur up to one L2 data cache miss per row processed if row size is greater than size of cache line.”
  • DBMS transfers the ENTIRE ROW from disk to memory even though the query required just 3 attributes.
  • “Takeaway: DBMS must avoid doing random disk I/O as much as possible.”

In short, DeWitt shows us the power of indexing and vertical partitioning in very technical terms. He also gives us a taste for column-oriented design, which we’ll catch a glimpse of in SQL Server 2008 R2.  Awesomeness.

The keynote wraps up with a promise to include DeWitt’s presentation on the Summit DVD.  If you missed the conference, then trust me, DeWitt’s presentation is worth the cost alone; all of the sessions are just a nice bonus on top of that.  :)

Live Blogging: Keynote at PASS, Day 2

So today is day 2 of the PASS Summit conference. The conference has been excellent so far, with tons of great content and tons of great folks. Yesterday’s keynote revealed some interesting tidbits, including SQL Server 2008 R2 setting the world record for TPC-E (transactions per second) and QphH (queries per hour). Be sure to refresh this page frequently for updates, or follow along on Twitter.

8:00 AM PST
Michelle shows up extra early today, having learned her lesson from yesterday. Coffee in hand. Ah, much better than yesterday. :)

8:41 AM PST
Rushabh Mehta just took the stage to the tune of All Star.

8:46 AM PST
Interesting statistics:

  • 15% reduction in revenue
  • 40% growth in the SQL PASS community
  • 67% reduction in IT expenses

Rushabh encourages support in the PASS community. He challenges everyone to volunteer at least an hour a month with your local user group. If you would like to become more involved in the local East Iowa SQL Server user group, please let me know!

8:51 AM PST
Wayne Snyder just took the stage to present the PASSion Awards. Folks recognized include:

  • Tim Ford (@sqlagentman) – for his work on the Program Committee and Quiz Bowl. Tim was no where to be seen!  Probably eating left-over bagels.
  • Grant Fritchey (@GFritchey) – for his contributions to getting SQL Server Standard relaunched. Grant also had the opportunity to show off his fabulous kilt!
  • Jacob Sebastian (@jacobsebastian) – for his contributions to the Asia community. He has started 6 chapters in India. Wow!
  • Amy Lewis – for her contributions to the BI Virtual Chapters and growing the sub-chapters.

9:00 AM PST
2009 International PASSion Award recipient is Charlie Hanania. Accomplishments include Swiss Chapter Leader and managing the entire 2009 PASS European Conference.

2009 North American PASSion Award recipient is Allen Kinsel (@sqlinsaneo)! Allen’s accomplishments include 5 years with PASS, and Program Manager for the 2009 Program Committee. He helped tremendously with Summit 2009, so if you see him around Summit, be sure to thank him!

Read more about the PASSion Award winners here.

9:07 AM PST
Tom Casey just took the stage. His first challenge is for all Summit attendees to act as agents of change in their respective organizations. Interesting statistic: only 20% of business users have the data they need to do their job effectively. Tom’s call to arms is to improve that statistic.

9:13 AM PST
Tom Casey: “PASS Summit is the place to be for B-I.” Statistics from Summit 2009: 2 dedicated BI tracks with 50+ BI-related sessions. Last year, 20% of attendees expressed interest in BI; this year, that number is 31%.

9:16 AM PST
Ron VanZanten, Directing Officer of Business Intelligence for Premier Bank Card, just joined Tom on stage. He works with over 25 TB of data and has used BI to leverage the data that they collect. His 3,200 employees use this information to perform their job. VanZanten wanted a flexible BI stack that would grow with the company. He’s successfully tested Madison to improve performance & scale out his environment. This is very encouraging news for any large data warehouse environment.

9:23 AM PST
Tom recognizes that data is available in a wide variety of mediums and that the numbers of sources will only continue to increase. He also introduces a new term: “spreadmart” – spreadsheets that are used as data marts.

9:30 AM PST
Amir Netz just takes the stage to give everyone a demo of the self-service BI offerings in SQL Server 2008 R2. Features include PowerPivot and SharePoint services. Amir shows us an Excel spreadsheet PowerPivot table with 100m rows. It’s amazingly fast. While I shudder to think of end users actually asking for 100m rows in Excel (and trust me, I’m sure it’ll happen), this is going to be a great breakthrough for our BI admins and our power IW users. It’s a time saver and gives greater visibility into data. While I realize the self-service BI offerings are still young, I’m excited for the future of BI. I can’t wait to implement what I’ve seen in our reporting environments.

11:55 AM PST
Tom Casey returns to center stage. Tom announces a contest to win an X-Box 360. To enter, follow @powerpivot & retweet: “Want to learn more, go to http://bit.ly/4n5vpd & sign up for the November CTP #powerpivot.” Tom concludes the keynote with his call to arms to increase the number of business users who have access to the BI data that they need to do their job effectively (currently 20%).

So there you have it, today’s keynote. Check back tomorrow for details on Thursday’s keynote; it looks to be the best yet.

Live Blogging: Keynote at PASS, Day 1

I’m honored to be invited to live-blog during the Summit keynotes. Most of my updates will be via Twitter, but I’ll periodically consolidate and update into blog posts throughout the keynote. Please check back frequently during the Summit for updates.

8:00 AM PST
Michelle gets situated at the blog table, running late and doesn’t even have time for coffee. Ack!

8:05 AM PST
Computer finishes booting up, Twitter loaded. Interesting statistics: 31% of folks expressed interest in Business Intelligence, up significantly from previous years. More than 400 Microsoft product developers and managers, and 98 SQL Server MVP’s, are in attendance.

8:06 AM PST
Wayne Snyder talks about Virtual Chapters. Did you know there are 5 sub-chapters in the Business Intelligence virtual chapter?

8:07 AM PST
Wayne reviews 24HoursOfPASS. Interesting statistics: 50,123 registrations, and 3,524 folks in 70+ countries.

8:09 AM PST
Wayne announces the return of SQL Server Standard. The first article was posted this past week. Content is free but only available online. Seasoned SQL Server authors can earn $500 per article. Interested parties should contact Grant Fritchey (@GFritchey).

8:18 AM PST
Closing words by Wayne: “Remember, no one should be a stranger at Summit.” Make sure to say “hi” to at least one person you have never met before. The more people feel welcome, the more successful the event!

8:24 AM PST
I receive my first-ever press announcement and immediately start leaking sharing the news. First up: SQL Server 2008 R2 CTP scheduled for November release! Also, looks like Madison is being rebranded as “SQL Server 2008 R2 Parallel Data Warehouse.” I think I prefer the simplicity of “Madison. :)

8:26 AM PST
Microsoft announces a new TPC-E record of 2,012 tpsE. This is a platform-independent world record for *any* OLTP system. Oracle, eat your heart out.

8:30 AM PST
More details on the TPC-E benchmark world record: Unisys ES7000 model 7600R Enterprise Server on 96-core Xeon platform (first server with >64 cores). The same Unisys server also reached 102,778 QphH (queries per hour), a data warehousing performance improvement of 70%.

8:39 AM PST
Bob tells us that the IO strain on virtualized machines is there but negated by Hyper-V. We’re given a demo of live migration: it appears that migration is seamless and that transactions are unaffected by the process. Very cool.

8:42 AM PST
Bob talks about the future of SQL Server as more companies move toward the cloud.

8:50 AM PST
Bob concludes his speech by discussing the future of the data professional, and how the DBA role will not become obsolete but merely transformed.

8:52 AM PST
Ted Kummert, Senior VP of SQL Server, takes the stage.

8:56 AM PST
Ted’s Top 5 Reasons to be at PASS Summit:

#1 You are part of the world’s largest gathering of SQL Server professionals
#2 You can take your questions directly to the “source”
#3 We’ve got Wayne and Rushabh
#4 You can work hard and PLAY hard
#5 You will build skills & knowledge on the #1 database in the world

9:16 AM PST
Dan Jones, Principle Group Program Manager for SQL Server Manageability, takes the stage.

9:20 AM PST
Dan gives us a demo of SQL Server 2008 R2, including Utility Control Points.

9:30 AM PST
I need to leave to check in for my 10:30 am presentation. Bummed I’m going to miss the last of the keynote. :(