TechEd Braindump: SQL Server 2005: Bridging the Gap between Development and Administration

 Speaker: Kimberly Tripp

 

  • SQL Server stared from a team of five (Sybase) to 400 (for 7.0) to over 1,000 (for 2005). Geez, do you think that they’ve put some money into this thing?
  • Query Analyzer and SQL Enterprise Manager are now lumped in to SQL Management Studio
  • Analysis Manager turns into BI Developer Studio
  • Profiler can now be run outside of the admin role.
  • You can manage SQL Sever 2000, SQL Server 2005 and SQL Express from the SQL Management Studio.
  • Source Safe (anything that uses the Windows Source Control API) integration is included into SQL Management Studio. ABOUT FREAKIN TIME!
  • SQL Management Studio looks a lot whole like Visual Studio.
  • Using SQL CMD Lines in a SQL Script allows the ability connect to multiple servers.
  • If you are writing function to be run on the database work with the DBA to expect the proper results.
  • Good Scenarios for CLR Usage
    • Data validation & network traffic reduction.
    • Writing general purpose function: Data passes as arguments, little/no additional data access, Complex computation applied on a row by row basis to the data.
    • Scalar types & custom aggregations
    • Leveraging the power of the .Net frameworks
  • Bad uses of the CLR
    • Heavy data access – SQL set based access will be faster
    • Complex types 8K limitation
  • TSQL Enhancements
    • ROW_NUMBER
    • RANK
    • PIVOT/UNPIVOT
    • Common Table Expressions
    • CROSS APPLY and OUTER APPLY
    • TRY/CATCH
    • DDL Triggers (synchronous)
    • Event notifications (asynchronous)
    • Parameterized TOP
  • She demoed using .Net assemblies that are calling the Amazon Web services running in SQL Server. She called the assembly function from TSQL. Very powerful.
  • You can create XML Indexes in SQL Server.
  • XML Data is now shown as a hyperlink in SQL Management Studio. Click on the link and it shows the XML on a separate tab.
  • SQL Server 2005 can listen to HTTP traffic using kernel mode HTTP.SYS. It does NOT depend on IIS. This is off by default.
    • Clients can use SOAP to interact with SQL Server. This is great for platforms with no SQL middleware support.
    • Trade-off of flexibility for performance. SQL clients will perform better.
    • This can be created in TSQL.
  • Troubleshooting Tools:
    • Debugger support for TSLQ & CLR
    • Dynamic Mgt Views
    • Flight Recorder
    • SQLClient/OLEDB tracing
    • XML Showplan
    • Complete cleanup of error messages
    • SQL Profiler (with Perfmon integration) – You can open a perfmon trace and a profiler trace together. Both logs must be correlated by time. Also, the servers should have their clocks synchronized.
  • SQL Server Express has most of the features of SQL Server 2005!

Disclaimer: These are my notes from my experience at TechEd 2005. I do not guarantee the validity or accuracy of these notes. They are my thoughts and what information I felt was important at the time.