On June 6 I presented this session at Tech.Ed. It was also WebCast via MSDN (MSDN Webcast: Working with SQL Server 2005 from Visual Studio 2005). There were a lot of questions that came from online attendees (text Q&A). Lance Delano, our lead PM and Mahesh Prakriya, GPM of SQL Tools answer these questions. I thought I’d share them here.
Q: Which builds of SQL 2K5 and VS 2K5 were you able to get on one machine?
Visual Studio Beta2 and SQL's CTP April work together. Also, Visual Studio Beta2 and SQL's CTP June also work together.
Q: Can we install VS 2k5 on VPC?
Yes. In fact I believe (tho am not 100% certain) that there are VPC images our marketing team is making available.
Q: Is the June CTP instead of Beta 3?
The SQL team went to the CTP style of releases a while a back. So there are a series of CTPs not just June
Q: Is .NET 2.0 Beta 2 stable enough to install on a production box and use with the go-live license? Thanks.
w.r.t. go-live, yes but on CTP June of SQL which contains .NET Fx Beta2+. We are running few internal systems in Microsoft on SQLCLR
Q: What's happening to extended stored procedures (XPs) in SQL Server 2005?
XPs continue to exist in SQL2005 - However, the strong (very strong) recommendation is to take a look at SQLCLR if you are thinking of writing a new XP as the need for XPs has gone down but we need to continue shipping this functionality for backward compat
Q: When you say only one processor is used with SQL Express, what happens on a hyper threaded or dual core processor?
If the hardware allows for selection of a single CPU, SSE on uses the one.
Q: Has the CTP June new examples?
SQLCLR examples? Yes, they have updated but am not sure new examples.
Q: Would all the Express products be free after release also?
If you mean Visual Studio Express products? No. There is a small fee for those. However, you will likely find that you can get it from a variety of sources very cheaply.
Q: Now that SQL Server Express will apparently be the "end user" database of choice, will Microsoft still be making significant enhancements to Access?
The Office team has not made any plans re: Access public yet.
Q: Who has redist rights for SQLExpress 2005? and do those redist rights include XM (Express Manager)?
The developer can redist SSE.
Q: If XP's should not be preferred, will there be replacement CLR functions in the base product?
In this release we still are shipping XPs as part of the base product. Starting next release our goal is to provide SQLCLR replacements for these. e.g. xp_regread, etc
Q: VISUALFOXPRO is integrated with sql express?
No. VFP is not integrated in SSE.
Q: When will SQL Express be available for download?
SQL Express is just a SKU of SQL product and is available for download at http://www.microsoft.com/sql/express/default.mspx today
Q: So me being the developer, how do I get redist rights for SQLExpress? Is it the same as it is today (i.e. own MSDN Universal)?
Same as today.
Q: Does SSE have to work as windows service?
Yes. As opposed to a DLL. However, SSE can run in a private user's account so you can run as a normal user.
Q: Is Visual Studio 2005 GA now? Thx.
I am assuming that by GA you mean generally available - VS Beta2 is generally available. The final product will be available this year.
Q: How big is the SSE distribution? Is this something that we would include on an install CD, or would the end user grab it from MS downloads?
The 32 bit only version is around 55 meg. The 32/64 bit version is around 75.
Q: Does that also include the new Express Manager tool?
Not sure about XM and the redist plan on it.
Q: If I want to run small office app (3-5 users) off SSE, will this work ok? I know I can’t use http/soap that is available in full sql2k5
Yes, you need to set SSE up for that specific scenario though. You need to do an advanced setup. By default, it will set up for local data.
Q: MSDE requires a license of another product like Office or SQL Server. Will SQL Express have the same limitation?
No.
Q: I think you mentioned a 4G limit on SSE. Is that for a single table or the entire database?
Entire database.
Q: Is 4G limit per database or per whole server?
Database
Q: Can you describe REAL business case of using CLR inside SQL?
Yes. Computationally intensive functions, table valued functions (as they return iterators as opposed to full data set), defining your own aggregates in the DB (as there is no other way to do this and there are many advanced features as well - e.g. replacing XPs, using UDTs in a smart way, etc.
Q: Once you have made VS Application, copy app.exe and mdf, does it require SQL Express to be installed prior for the app to function?
It does require SSE to be on the machine you deploy to. But, it only needs to be installed once.
Q: What will ISVs need to do to upgrade MSDE 2000 instances to SQL Express for our clients? Can it be an in place upgrade?
MSDE can be upgraded in place with SSE. However, there are number of things to take into consideration. You should not take this step blindly.
Q: If you run 2 instances of your app and mdf, will each have their own copy of the mdf active, or will they share on attached database?
Not sure what you mean by two instances of the app and MDF. If you mean that you run your database app that connects to a single MDF. SSE will only run once and manage connections from both running instances of the app.
Q: Is there any support for schema changes in database side (since CLR embedded db objects inside code)?
SQLCLR objects are exposed as SQL objects in the end - SQL functions, triggers, types, aggregates and stored procs. So you treat them as with any other SQL objects and schema changes are allowed for these.
Q: Is there still a ‘database project’ available in VS2005?
Yes. It’s in Standard and above. ( I think. I know it’s not in Express).
Q: Are SQL 2005 and VS 2005 going to be released at the exact same time?
Yes
Q: Can I obtain better performance with SQL CLR or with T-SQL?
That depends on your scenario. Generally speaking you get much better perf for functions, table valued functions and aggs (there’s no other way to write aggs). Stored procs (heavy data access) will be slower in SQLCLR in this release (SQL2005) so it is discouraged in those scenarios. Your mileage may vary, so please measure.
Q: If I have SQL2K5 CTP and VS 2K5 Beta 2, which order should I install them?
Depends on which CTP. However, generally, you’ll be better off installing SQL first.
Q: If a MSDE database application exists what is the upgrade process for going from MSDE to SSE with the least number of manual configuration and data upgrading steps?
Depends on the database. It can be automatic. The MDF will simply get upgraded. However, if you are using replication, you will not get all the functionality you had in MSDE.
Q: If I register the SQL CLR assembly, then take a backup of the database, and restore the backup on another instance of SQL Server, do I need to register the assembly again or does the restore process takes care of registering?
Restore should care of it as the assembly as its stored as part of the database
Q: Will SQL Express have any Visual tools (MSDE does not)
Yes. It absolutely will. Currently SSE is available with XM which is a graphical tool. Additionally, Visual Studio works with SSE and provides graphical tools as well.
Q: Does sse support encryption?
You mean Windows encryption? Yes when you run it in User-Instance mode as a normal user. And, there are additional other new encryption features available as well.
Q: Are “XM” (available with SSE) and SQL Management Studio (available in SQL2K5 CTP) the same thing?
No - SSMS is a higher end tool set that allows you to manage SQL in a comprehensive way (show plans, visually design maintenance plans, XML editor integration, project systems, src code control system integration, registered servers, etc.) SSMS also allows you to manage Analysis server, Report Server, DTS (SSIS), SQL Mobile etc. XM is designed as a simple query tool for SSE.
Q: I have seen the term CTP discussed a few times here, what is it? Thx.
Community Technology Preview - these are interim releases before/after Beta’s typically every few months so you don’t have to wait a longer time for a Beta and can check em out between Betas.
Q: In looking at the pricing for Visual studio 2005 that has been published at the Microsoft site the pricing goes as high as $11,000 per user. What are the basic tools that a SSE developer needs to be able to develop database applications using a mixture of TSQL and CLR stored procedures?
The various versions of VS help automate certain tasks as they go higher up the SKU level. You can call and work with SQL CLR in the Express versions. If you want debugging functionality, you need VS Pro and above.
Q: When I create projects using VS2005 Beta2 on Win XP Pro running on a Virtual PC, do I necessarily need to create the projects on local drive. Because, when I tried doing that on the shared network drive, the whole thing was hanging all the time! Are you aware of any specific issues in this regard?
Privately: I’m not aware of any specific issues. I know of a number of developers that use VPC to work with interim builds.
Q: There was already “great feature” - cursors, which was used inappropriately. Then it was told that SQL Server is slow... Will CLR repeat this mistake?
For most part, we’ve taken pains to ensure that the default usage of SQLCLR is secure, reliable and performant. However, it is still possible for someone to get themselves wedged especially in unsafe bucket (which only sa’s have access to)
Q: SQL Express is only valuable to an app that requires a database only accessible to it self?
No. SSE can be used in multi-user and multi-app scenarios.
Q: 1) MSDE is available for free. Would the same be true for SSE after release? 2) Do you recommend any book that focuses specifically on using SQL Server CLR and VS 2005 with SSE features.
Yes. SSE will be free. Any book on SQL CLR and VS 2005 will work with SSE as well.
Q: A lot of DBAs I know write Perl scripts for SQL Server. Will there be anything new on the Perl front in SQL Server 2005?
Great question. Regular perl won’t work in SQL. Perl.NET could if the provider of that perl compiler & libraries has done the reliability work to run in SQL (or runs in unsafe context in SQL - equivalent to running XPs)
Q: Does this mean that you can have a VS project that contains all of the TSQL stored procs for a database project (perhaps you can do this today, but I normally just created SP’s in the DB itself)
Yes. There are database projects that house TSQL script.
Q: What about source control for SP?
If you use database projects in VS, and use scripts, you can check these in and out of SCC.
Q: Would it be possible to automatically install SSE on the target m/c while installing the app, as is the case with bundling .Net framework on m/c’s that don’t have one?
Yes. Exactly like installing the .NET framework.
Q: aspx page could call these SQLCLR objects right?
As SQL objects, yes. (All SQLCLR objects are manifested in DB as just SQL objects - functions, triggers, types, procs, aggs)
Q: How are .NET UDTs stored in database? XML serialization?
Great question :-) - We (SQL) implemented our own serialization library when selecting UDTs (by default Format.Native in the custom attribute). However, you (as a dev) can change this to use XML serialization if you desire. Think through the ramifications carefully tho and it is worth reading few articles on this before doing so.
Q: Back to XPs. You indicated “the need for XPs has gone down.” Can you elaborate? Thx.
Much of the need to write XPs is justified on the need for writing more complex code (e.g. string manipulation, complex mathematical calculations). With SQLCLR, this kind of code can live in the database.
Q: How can I view the registered Dlls that will show the permission level?
select * from sys.assemblies - also check out the dmv’s for assemblies; dm_clr* in SQL2005
Q: What - in your opinion - are some of the cool NEW sp’s in SQL Server 2005?
Great question - more than new sp_ (there are few but am not well prepared to iterate through them now - may blog later on them) I am quite excited by the DMVs - check out all the sys.dm_* that provide you a ton of detailed internal information about your server (and component such as CLR, SSB etc) state and diagnostics
Q: DB growth or functionality. Which is more important in deciding if SSE is an appropriate solution?
It depends on the app and how much complexity you need. SSE is SQL Server. So, from a growth perspective, it can immediately scale up to higher level SKUs. But, SSE also has significant functionality that most other local data stores don’t have.
Q: Can SQL2005 and SQL2000 exist on same devel machine?
Yes, they are supported SxS - and our test team signs off on these. If you see issues please let us know.
Q: Why can’t a “default” Database Diagram be automatically included with each user database?
Great idea and recommendation - this is something we considered but Didn’t want to take up any more space in your DB than we needed to :-). If others think this is a good idea as well, let me know. Something for us to consider
Q: I’m using stored proc / DTS that is posted on knowledgebase...is there a faster way in SQl 2005...
DTS itself is significantly faster so w/o changing anything I think you’ll find it to be faster using DTS
Q: Is there a new row pointer function in SQL 2005? Like telling SQL to have a starting record index, offset value?
No - not to my knowledge
Q: Can you then store a business object directly into the db by creating a user defined data type? And then convert it back to a class object when pulling it out?
You could; but we don’t recommend storing biz objs in DB’s. There are several reasons for this including 1 page upper limit on UDT size
Q: Can Visual studio 2005 be used to manage sql 2000 replacing enterprise manager?
Not entirely. You get the table designer, query designer, database diagrams, and data base projects in VS Pro and above.
Q: UDT in TSQL is faster than CLR UDT right?
UDDT in TSQL just gives you a different name for existing data type and doesn’t create a new type. So am not sure what you mean
Q: What happens if a database with a UDT is deployed onto a system that does not have the CLR enabled for SQL?
It simply won’t run.
Q: So if you serialize UDTs into XML, can I query that (xml) data column from SQL statements using new XML functions in SQL2K5?
It’s stored as a binary blob in the end so you cant do xml manipulations on it this release unfortunately.
Q: Are there methods to take data from an Express db and merge them to a Primary SQL Server db? IE Using Express as a “Client db”?
You just the MDF and attach it to a higher SKU of SQL Server.
Q: How do I get a list of assemblie names from SQL? I presume assemblies are stored in the USER db, such that if I backup the DB and restore some other server, I should be up and running (presuming I have .NET FW installed).
select * from sys.assemblies
Q: When you talk about attach an MDF-file with XCOPY it sounds nice. What about the log-file? Does SSE only use one file?
Yes. By default, when you use the attach-style connections, SSE creates a single LOG file in the same directory as the MDF.
Q: I mean, why would you create a TSQL UDT in SQL CLR project where you can create an UDT in TSQL thru Enterprise Manager
Because UDTs (really UDDTs) are not new types in SQL2K - they are aliases to existing datatypes. So you’re using a new name against an old type (e.g. myint for int)
Q: A single LOG-file sounds that there is two files, one MDF an one LDF?
Yes, there is a MDF and LOG file. The log file gets the name of mdffile_LOG.LDF
Q: How do u determine the size of a UDT… with all its properties?
You should right click in management studio in the object explorer tree and look at the properties
Q: Speaking of row pointers, has anything changed WRT cursors? Also (probably a dumb question) - does client/server still have a role, or is everything SOA now?
Yes, client server still has a role even with SOA :-) - not a dumb question at all.
Q: If you create a UDDT through SQL CLR, is the UDDT stored in the Database or is it stored somewhere else?
In the DB