Working with Developers and SQL Server
Introduction
Moderator: Stephen_D (Microsoft)
Welcome to today's Working with Developers and SQL Server chat. I will ask the hosts to introduce themselves.
Host: DanWinn (Microsoft)
Hi. I'm Dan Winn, a Program Manager in the SQL Server Engine team.
Host: swynk (MVP)
Hi I'm Stephen Wynkoop, SQL Server MVP and founder of SSWUG.ORG, the sponsor of this web chat.
Host: bwunder (DBA)
Bill Wunder DBA for Wall Street on Demand, an internet content provider located in Boulder, Colorado
Host: Max (DBA)
Hi, I'm Max Myrick - Program Manager for SQL Server "Upgrade" and previously the SQL Server Customer Lab manager.
Host: MarkA (MVP)
My name is Mark Allison, SQL Server MVP based in UK. I've been working with SQL Server since 1995 in a production and development role.
Host: StephanieSaad (Microsoft)
Hi, I?fm Stephanie Saad, a Program Manager on the Visual Studio team.
Moderator: Stephen_D (Microsoft)
And... I am Stephen Dybing, SQL Server MVP Lead. Glad you all could make it today!
Let's get started! Please fire away with your questions for our hosts.
Start of Chat
Host: swynk (MVP)
Q: isn't it about time to begin?
A: YES!
Host: DanWinn (Microsoft)
Q: Is there a way to integrate VSS with Query Analyzer for easy checking in and out of objects? Is this something that will have been addressed with the new Workbench?
A: SQL Server 2000's Query Analyzer does not support Visual Source Safe. There are, however, a number of third party tools which have received positive reviews in this space. SQL Server Yukon's Workbench does indeed support direct integration with source control services.
Host: MarkA (MVP)
Q: TVN: Can you export EM registry and import in another computer (to save time re-registering the servers)?
A: Yes you can! Go to HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X. Export that. Then merge the file into your other PC.
Host: swynk (MVP)
Q: JD: What is your opinion on the CRL integration into SQL Server?
A: I personally think it's going to be a good thing. There will be challenges with server scaling as processes move to the server, and I think DBAs will have to pay very close attention to security but I do think the flexibility will be key.
Host: MarkA (MVP)
A: Good question. I think that it is a good thing provided that it is not abused. The great debate will be should procs be written with a CLR language or T-SQL. Procedural logic should be written in CLR, and set based operations, i.e. working with inserting, modifying and retrieving data should be done with T-SQL. This is open to abuse, rather like cursors!
Host: DanWinn (Microsoft)
Q: Given the new world of SQL and .Net, what would be a 'beginners' approach working with .Net and Visual Studio .Net/MS SQL Integration
A: The easiest way to get started would probably to create a "SQL Server Project" in Visual Studio Whidbey and add a User Defined Function to the project. The templates will provide you with a working object that you can start experimenting with. Once you get a bit of a feel for this, check out the Whitepaper on SQL/CLR.
Host: bwunder (DBA)
Q: How do handle development of multiple projects against a single db structure: one db or many? Then how do you put them into integration test?
A: Given the $49 cost of Developer Edition of SQL Server, consider setting up a unit testing environment for the project you are working with on your desktop. Coordinating promotions to the integration environment is the real challenge. Don't know your situation but how about having a single person do promotions into the integrated environment for starters?
Host: Max (DBA)
A: What is the process to convert 2000 DTS Packages to Yukon?
A: There will be a DTS Migration Wizard which will help convert these to the Yukon version which may be called ETL.
Moderator: Stephen_D (Microsoft)
Q: NTML vs. SQL Authentication: the former is "secure", the latter supports connection pooling (and encrypted connections), the documentation keeps pushing NTML, but in reality, SQL Auth is more common in real-world apps. Where is Auth in SQL heading?
A: I don't know the complete story here, but I do know that SQL Authentication will be improved with things like enforcing policies for SQL Login passwords. There's a bit up on https://www.microsoft.com/technet/prodtechnol/sql/2005/default.mspx
Host: bwunder (DBA)
Q: Will Yukon have the ability to backup DTS packages with all the configurations with no user intervention?
A: Everything is supposed to be scriptable but I haven't figured out how to script with SMO yet.
Host: StephanieSaad (Microsoft)
Q: Will there be new collaboration features in Yukon to help with the multiple users working on a database project?
A: Yes, there will be collaboration features to support multiple users working on a DB project. DB projects are developed via Visual Studio which has full support for team development through integrated source control
Host: DanWinn (Microsoft)
Q: What is your opinion on the CRL integration into SQL Server?
A: Mark raises a good point. Managed code will not replace T-SQL. T-SQL remains the only way to do data access and procs which do primarily data access should certainly remain written in T-SQL. However, SQL/CLR will allow you improve dramatically the performance of many functions, leverage the frameworks in your procs, and extend the database in ways you previously couldn't with types and aggregates.
Host: Max (DBA)
Q: Max_MS- ETL?
A: Extract, Transform, Load. However, the final name has not been confirmed.
Host: bwunder (DBA)
Q: What is the best process to integrate source control using SourceSafe into SQL Server 2000 databases?
A: It?fs all the honor method for developers no matter what tool you use. You could always pull everything into SourceSafe every night using Bill Wunder's DDL Archive Utility (free at sqlservercentral.com).
Host: swynk (MVP)
Q: JohnJ: Is there any documentation for Yukon out yet?
A: For Beta users, or PDC folks received BOL with the Beta Bits. It's not yet posted to MSDN.
Host: MarkA (MVP)
Q: JD: Will Yukon have a new SQL DMO?
A: Yes, this API has been totally re-written for SQL Server. It is much faster, can scale much further and integrates into CLR. The new API is called SMO - SQL Management Objects
Host: DanWinn (Microsoft)
Q: Why can't Profiler run without logging in as Admin? Profiler is very useful for developers, but I don't want to give them admin rights.
A: In SQL Server 2000, one requires admin rights to create a SQL Trace. As such, Profiler must log in as an admin. In Yukon, there is an ALTER TRACE permission which may be granted to non-admins which allows them to create a SQL Trace.
Host: bwunder (DBA)
Q: Can Yukon's workbench work with SQL 2000 and 7 to manage the tables, SPs, and other objects, the way that SQL 2k can connect to SQL 7 DB Servers?
A: Workbench seems to work just dandy on a SQL 2000 and a sql 7 server.
Host: swynk (MVP)
Q: TVN: Has NDA been lifted officially, I read about that in SQLCentral...
A: Information you receive through public forums is ok to repeat and talk about, but information you receive under specific NDA is not to be made public.
Host: bwunder (DBA)
Q: Is there going to be improved ability/reliability in send/receive email in the next release of SQL 2000 (Yukon?)?
A: Looks like the same old MAPI client dependency to me.
Moderator: Stephen_D (Microsoft)
Q: We're in the process of rewriting our database build scripts, is there an OSqlW on the horizon?
A: I'm not sure what you're really asking for here, but there will be a new, much improved command line utility calls sqlcmd. There will not be an OSqlw, but there will be a new graphical tool replacing QA as well.
Host: DanWinn (Microsoft)
Q: How do you perform documentation of a database? From the programmer wanting access? From the user's perspective who needs data definition? I'd rather not document separately from db design.
A: There are a number of strong third party tools in this space which address this requirement. If you want to roll your own, you could build a simple tool to query various system tables and produce output based on their contents.
Host: MarkA (MVP)
Q: ATLAS: Does anyone have a good scheme for forcing developers through Dev/test/Prod cycle?
A: You need to get management buy-in into the benefits of streamlining your environment. You really need to get prod as close to test in every way possible, as much as you can. Also, do not allow developers access to test or prod. Then try and get your dev servers as close to test as you can in terms of configuration, security. Use this for system testing. Finally, get developers to actually develop their units of code on their local dev box using SQL Server Developer Edition. Make this part of your business processes and document it. The result will be faster transition from dev to prod, and fewer bugs, especially in the area of security.
Host: bwunder (DBA)
Q: Will Yukon have a new SQL DMO?
A: Yes, it?fs what others have been talking about: SMO.
Host: Max (DBA)
Q: Is there going to be improved ability/reliability in send/receive email in the next release of SQL 2000 (Yukon?)?
A: Yes. In addition to the existing readmail and SQLmail, there will probably be a new queue-based mail system designed specifically to improve these.
Host: MarkA (MVP)
Q: usagi: Marka: Oh for the luxury of so many servers....
A: :) You could use VMWare GSX Server to make the most of the resources in your existing hardware. I do this and it works well.
Host: DanWinn (Microsoft)
Q: DanWinn_MS: "check out the Whitepaper on SQL/CLR." What white paper is that is there a link?
A: The whitepaper can be found on the BetaPlace site if you are on Beta 1. It may also have been included in the PDC CD.
Host: Max (DBA)
Q: How much of an overhead does the backup of the translog put on the system? Is it negligible?
A: This depends a lot on what I/O load you have on your log disk. Generally, your log should be on it's own spindles which means you may have the available disk overhead available to do this backup without noticing any hit. This is especially true if you have multiple spindles under your log disk. The opposite true if your log disk is heavily accessed and currently your limiting factor. In general, there is no way to limit the "effect" of the Read operation on the writing you are doing to your log as part of normal operation. Therefore you'll have to look at this for your environment.
Host: bwunder (DBA)
Q: Can you recommend best practices for comparing and maintaining differences between production, test and dev versions of the same database?
A: We had to roll our own to get something that would work for use. You can find my email address on one of my articles at sswug.org and contact me off line if you?fd like details on how we solved this. (I will share some of the code in an upcoming article
Host: StephanieSaad (Microsoft)
Q: With the integration of the CLR do you feel we will be faced with every "Tom, Dick and Harry" creating database code because they know VB?
A: Yes, if a developer is comfortable with database concepts and more comfortable coding in C# or VB, that developer will be more easily able to write stored procedures and the like. However, in general I don't believe providing more options in terms of coding languages would substantially change the landscape - good database programming will always require knowledge of the database and its capabilities. HTH.
Host: swynk (MVP)
Q: Howardd21: Is there a good book on the process of Database development with SQL Server, accounting for multiple Devs, testing, rollout, etc.?
A: Sorry, we don't know of anything specific - but we'll do some searching and include anything I can find. (in the transcripts)
Host: DanWinn (Microsoft)
Q: Isn't the CLR integration re-opening the xp_ can of worms. I am already getting hassled by devs wanting to code their procs in C#. When the cost per transaction is high on the db server do you want devs putting intensive processing in the db tier?
A: One of the objectives of CLR integration is actually to let you avoid the XP "can of worms". CLR objects execute within a strongly enforced "sandbox" within the server meaning that unless you explicitly allow it to, a CLR object won't be able to to destabilize your server. You're certainly right that you won't want to move all your business logic into the server, but you can actually improve the performance and scalability of your app by moving certain isolated processes closer to your data.
Host: StephanieSaad (Microsoft)
Q: Will Yukon let us write user-defined aggregate functions (a la Oracle)?
A: Yes, Yukon will. You can write user-defined aggregate functions in either C# or Visual Basic.
Host: Max (DBA)
Q: If Yukon works fine with SQL 2000 and SQL 7, why can't we use SQL 7 with Windows 2003?
A: SQL 7.0 had several fundamental issues on Win2003 which were difficult to resolve without affecting security. The most fundamental is that the SQL 7.0 service will not start if the service is owned by a domain account (which is necessary for clustering). For this reason and several others, SQL 7.0 was decided to be unsupportable on Win2003. You must upgrade to SQL 2000 or beyond before upgrading to Win2003, however a recent whitepaper does help with this upgrade path.
Host: DanWinn (Microsoft)
Q: What is the go-forward recommended target for logins -- Integrated/NTML or SQL Auth, or are there specific situations where each are recommended?
A: In general, you should use NT Auth whenever you can. However, certain environments (particularly heterogeneous ones) will require SQL Auth and it remains fully supported (and much improved in Yukon).
Host: swynk (MVP)
Q: dotBomb: Hi, I'm wondering how I can shrink the size of the Transaction Log?
A: The fastest way is to sp_detach_db then re-attach. It'll bring the transaction log back online at the minimum size needed.
Moderator: Stephen_D (Microsoft)
Q: What makes SMO better, faster, stronger, then DMO?
A: SMO only pulls down the data that it needs, and it includes all the new Yukon capabilities.
Host: MarkA (MVP)
Q: trevlac: What is the general consensus of using tools like Red Gate's SQL Compare to produce database upgrade scripts? Good? Bad? Indifferent?
A: I can give you my personal opinion. I prefer to lovingly hand-craft my own SQL Scripts for change control purposes because I am in full control. Code will have been thought through properly. Having said that, I am sure that some people will find it very useful, but I think it leaves scope for unwanted changes going to production.
Host: bwunder (DBA)
Q: What exactly does the new XML "type" consist of (is there a whitepaper, etc.)?
A: If you can get your hands on a Yukon beta there?fs a good bit of information in there. The December SQL Server Magazine has a good article.
Host: Max (DBA)
Q: Max_MS: A**:** In addition to the existing readmail and SQLmail, there will probably be a new queue-based mail system - MAPI underneath?
A: No, the new version is not extended MAPI-based. (SQLmail uses Extended MAPI).
Host: DanWinn (Microsoft)
Q: What is the best way to do version control using SQL 2000 without 3rd party tools?
A: Many development houses simply use a source control mechanism (such as VSS) without any integration into their editor. This is generally easiest if you put each object into a similarly named .sql file which your keep under source control.
Moderator: Stephen_D (Microsoft)
Q: If no xp's, how do you complete command shell?
A: No, xp_cmdshell still exists in Yukon.
Host: Max (DBA)
Q: Max_MS: A: No, the new version is not extended MAPI-based. (SQLmail uses Extended MAPI). Does this mean that SQL Mail will be supported in a cluster?
A: "SQL Mail" will not be supported on clusters - same as today. However the new version is planned to be fully supported on clusters.
Host: MarkA (MVP)
Q: vypTravis: Why not have the DEV use the MSDE locally for Dev?
A: It comes down to my earlier point that to make transition between environments smoother, you should make test like production and dev like test, i.e. if prod is Enterprise Edition, then make your central dev server, Enterprise Edition. SQL Server developer edition on your local machine gives you all features of Enterprise, but is not licensed for production. MSDE is more suited to distributing small applications to your customers.
Host: Max (DBA)
Q: will there be the separate versions of Yukon (Standard vs Enterprise) and if so, what will the key differences be?
A: There will certainly be some separate SKUs of Yukon, however we have not yet determined exactly what skus will be available and what their feature sets will be. I would not expect them to be radically different from SQL 2000 SKUs (Enterprise, Developer, Evaluation, etc)
Host: DanWinn (Microsoft)
Q: DanWinn_MS, which system tables in particular? I know, I'm fishing for specifics.
A: I'd start with sysobjects and syscomments. These will give you basic information about your procs, functions, tables, views, etc. From there, you can start branching out to other tables if you want to increase the scope of your documentation.
Host: swynk (MVP)
Q: chris: When you look at the permissions for the fixed database and fixed server roles in SQL 2000, you see many lower-level permissions available. Will Yukon let me manage those lower-level permissions?
A: You'll have more granular control over permissions - you won't necessarily be able to modify the built-in roles, but you'll have more ability to control permissions and create security to address your needs. You'll finally be able to run profiler without being sysadmin. :)
Host: bwunder (DBA)
Q: sp_xml_preparedocument - Does the xml datatype in Yukon make this redundant? Its consumption of 1/8th available memory to SQL Server does give me cause for concern regarding its scalability.
A: DTS?fs BULK XML may be another option. I think it uses OPENROWSET but not for sure on that off the top of my head. I?fm still of the mind that the XML datatype is going to be more useful for pass through data rather than data you actually need to normalize and query.
Moderator: Stephen_D (Microsoft)
Q: Can SQL Server2000/Yukon take advantage of improved performance with subsequent releases of MSXML?
A: Yes, but you may need to update MDAC.
Host: MarkA (MVP)
Q: usagi: MarkA; VMware: good point, though still hard to get the beancounters to swallow. Must try that on them again next time I'm buying new development "tin"
A: Well, you have to put to them the costs of employing staff to resubmit code through the testing phase because the environment wasn't properly set up to start with. :) Touche!
Moderator: Stephen_D (Microsoft)
Q: Will there be any e.g.(QOS like or otherwise) prioritization support for connections or procedures in Yukon?
A: We have no prioritization support in Yukon. We do have a dedicated admin connection, but it's not given any different priority.
Host: DanWinn (Microsoft)
Q: Is there anything for object based storage in Yukon (ala object oriented DB, rather than RDBMS)?
A: "Object Spaces" is a new technology which provides a solution for object persistence in SQL Server. It's worth noting that CLR UDTs should _not_ be used for object persistence and are instead intended for Scalar Types in Yukon.
Host: Max (DBA)
Q: I recently read (in a blog) that dynamic sqls are better than sps. What is your take on this considering everyone recommends to use SPs whenever possible? Does SPs actually execute faster and are more efficient?
A: SPs have an advantage over dynamic code in some ways within the engine. The difference is that it is a bit easier for the engine to cache and reuse the cache for SPs. With Dynamic SQL you should keep an eye on your statement plan reuse to make sure you are not using more cpu bandwidth to generate plans. One advantage of dynamic SQL is that it can be much shorter and re-usable. Another is that you don't cache as many plans if you can not reuse them anyway. Long-term, please note that some dynamic code is harder to maintain long-term because you'd have to use a profiler trace to allow the code to be "viewable to a tool." I'm thinking specifically of an upcoming tool for finding backcompat/deprecation items in your application code which can not necessary find these in dynamic SQL.
Moderator: Stephen_D (Microsoft)
There were numerous questions surrounding SQL Mail and how it will work in Yukon. We didn?ft have any experts online so Euan Garden, a Product Unit Manager over on the dev team, provided the following information for inclusion in this transcript:
SQLMail will still exist in Yukon with minor changes, however there will be a new mail solution (currently called SQLiMail). SQLiMail replaces most of the functionality on SQLMail and:
It does not need MAPI,
It does not need Outlook,
It does not execute mail calls in the server process,
It is supported on a cluster,
It does support SMTP.
We are still working on providing an SMTP solution for Agent and hope to do so before Yukon ships.
Moderator: Stephen_D (Microsoft)
Thanks for joining us today! You've asked some great questions. We'll finish the final questions that we're working on, but unfortunately, it's time to go.
Host: bwunder (DBA)
See you later. Lots of really good questions and clearly every one is more interested in Yukon than in the development environment. Thanks everyone!
Host: MarkA (MVP)
Thanks for your questions! See you on the sswug discussion lists!
Host: swynk (MVP)
Thanks everyone for coming - sure appreciate all the excellent questions! We'll get the transcripts posted (with as many additional answers as possible) at www.sswug.org ASAP.
Host: Max (DBA)
Enjoyed it folks! Hope to work with you again soon.
Host: DanWinn (Microsoft)
Thanks all.
For further information on this topic or about SQL Server, please visit the following:
Newsgroups: SQL Server Newsgroups
SQL Transcripts: Read the archive of past SQL chats.
Website: Visit the Microsoft SQL Website