Wednesday, September 29, 2004
WOW....what an interesting last couple days. I was priviliged to attend a couple incredible meetings. I was unfortunate enough to attend a session that was not so great (cough, cough)!!!!! Such is life in the world of conferences. :)
SQL Server Locking Internals and Troubleshooting - **Good**
This session covered a lot of the tools and techniques used by PSS in troubleshooting issues. He went really into depth on how scheduling, threading, fibers, crabbing work. SQL Server 2005 is changing the way threads are allocated. By allocating them to tasks, we will be able to get better management of available threads. It also now supports the option to dynamically change threads. Right now it requires a reboot.
There were also a lot of good utilities covered. It's good stuff I tell you. Everyone should check out the new read80trace tool available at Microsoft.
DBA 101 – SQL Server 2005: Transferring Existing Skills to a New Platform - **Needs Help**
This session was not so great. The session speaker didn't seem to actually know that much about 2005. I'm not sure how much he actually used it. It would be nice to have a session that compares the two and gives you items to watch out for as a DBA.
Prescriptive Architecture Guidance on SQLCLR - **ROCKS!!!!!!!!** Speaker - Ramachandran Ven Katesh
This guy is now one of my heros. :) He should speak or train SQL Server for a living. He explained the architecture of the CLR, including CLR, best practices, examples, etc. Many of his samples can be found by searching MSDN. If you are a PASS member and didn't get to attend, buy the conference DVD and download his slides. It will be worth it.
He also showed us the Visual Studio database project in 2005. This is a project that allows you to debug and register items automatically in a database. It's much easier than the Beta 1 way of doing things. In addition, it allows debugging of both CLR and TSQL in the same context. It's a great upgrade and tool. One of the things I'm really looking forward to is being able to tell vendors I don't let them put XPs on my server because the SUCK!!! It was nice to hear an MS person say the same thing. The CLR chnages all that.
Relational Data Warehousing with SQL Server 2005 - **Good**
After this, I'm tempted to ONLY attend conferences with MS speakers. They have really done a great job with their presentations this week. We covered the vast improvements to partitioned, indexed views. They have been extended to allow use in more situations. They have also improved the matching algorithms and now allow concurrent processing against multiple segments of the index. Index functions have also been expanded with online index functions and the INCLUDE clause.
Enhancing your SQL career by making a name for yourself - **Good**
I typed my blog in this session. It was great. :) Seriously, the biggest thing it stressted was:
DO SOMETHING!!! Help people out, write a blog, answer a question, start a website. It pays off in the end.
Later....I'm out of here!!!!
High Availability with SQL Server 2005
This was a great session. It was cut a little short by an EMERGENCY EVACUATION!!! Go figure. :) It focused on the new enhancements that SQL Server 2005 has added to provide for greater flexibility and implementation of high-availability networks. For those who are members of PASS, the slides can be downloaded from www.sqlpass.org when the conference is over. Here are my takeaways from the session:
- Covered Database Mirroring.
- Can be a long ways away.
i. Have to keep transactions in sync though, which can cost performance.
ii. Don’t need to be on compatability list like clustering.
iii. Should run equal hardware though; however, it’s not a requirement.
iv. No shared disk.
- Can pick synchronous/asynchronous.
i. A client attached to the “principal” server.
ii. Principal server sends “secondary transactions” to the “mirror” server.
iii. There is a “witness”. Provides vote on who is principal and mirror. Provides the winning vote. Allows failover if the connection breaks between principal and mirror vs. the actual servers failing.
- We can use database snapshots on the mirror, put it in read-only, and use it for reporting.
- To become the Principal, a server must talk to at least one other server. Can provide some long distance problems.
- Big question came up on whether you can mirror the master database.???
i. This is a great question. The answer appears to be no.
ii. Would be a problem.
- It’s pretty efficient because it catches the writes in the log buffer and automatically sends the log entries to the other server. There’s two types of commits:
i. Safety mode: Verifies the commit on the other side before it commits. Has automatic failover capability.
ii. Safety off: Just forgets about the transactions after it sends them.
- Because it’s just sending stuff, when you rollback a transaction it’s slower. It has to verify the rollback on the other side as well.
- DDL Changes are captured.
- Is able to handle multiple databases together with cross-database updates.
i. Need to find out more information. This wasn’t covered as the session was cut short.
- Can you tell how much latency exists between the mirrors?
i. Would be very efficient in a SAN configuration.
- There’s a client library to automatically redirect to mirror. Requires no changes to application code. Client automatically redirected if session is dropped. It’s aware of the principal and mirror servers. The library caches mirror name upon initial connect to principal.
- There is a manual failover process if you decide to use asynchronous.
i. ALTER DATABASE db SET PARTNER FAILOVER
- Covered replication, log shipping, cold standby.
Database Snapshots (new)
- Not changing log shipping. Lots of sessions on replication.
- Good chart on how to choose different types of failover.
- Allow recovery from user erros by allowing the database to go back in time.
- Multiple snapshots are allowed.
- It’s read only. To drop the database it’s attached to, you need to drop the snapshot first.
- CREATE DATABASE mydbsnap ON (filelist) AS SNAPSHOT OF mydb
- DROP DATABASE mydbsnap
- You can restore from a snapshot database.
i. RESTORE DATABASE mydb FROM DATABASE_SNAPSHOT = ‘mydbsnap0600’.
ii. Good Question: Can you restore to a different database?
Just thought I'd give a brief rundown of what's going on at PASS. I just attended the keynote address and my first session. The session was Developer Productivity and Server Extensibility with SQL Server 2005. The session covered many of the concepts in 2005 that involve CLR integration and benefits to development from this the extensions to the XML datatype and associated tools.
Each session I attend or book I read just enforces the fact that 2005 is by far the biggest “learning curve” upgrade since the advent of SQL Server and it's migration off the Sybase framework. It will be interesting to see how current DBA's face these challenges and rise to the occassion. It's a great time for new DBA staff to fly ahead using technology as the leaping board to the future.
This week, I'll mainly be focused on SQL Server enterprise database management and SQL Server 2005. I'll try to give everone little tidbits as I find them. If anyone from here or any other group I'm part of wants to hook up, let me know. My email is firstname.lastname@example.org.