Sql Server

Microsoft's Enterprise Database. Release 6.5 (circa 1999?) was the last of the previous generation releases. With the 7.0 release, they have made use of TP God JimGray and others and produced quite a astounding and solid product. Currently holds the world record for transactions.

See http://msdn.microsoft.com/sqlserver/

Recent Developments

In second half 2004 Yukon release beta2 being tested

In early 2004 Reporting Services for SQL2000 was finally released. An article that deals with the impact of this package can be found at http://www.databasejournal.com/features/mssql/article.php/3304481

Is Reporting Services another enticement for companies to go MicrosoftDotNet?

resources

SqlServerYukon news and developments

Reading material

Feb04 basic info at http://msdn.microsoft.com/msdnmag/issues/04/02/YukonBasics/

Laguna: SqlServer Mobile 2005 (CE 3.0) for MobileCommerce

From http://msmobiles.com/news.php/2750.html

Will be included in WindowsMobile (after the 2003 edition) and that includes SmartPhone as well.

Has DataTransformationServices? as well, so information from non MS sources can be linked up.

SQL 2000 is XmlEnabled

Following response based on a question asked of SqlServer in XmlEnabled

The Microsoft?SQL Server 2000 relational database engine natively supports Extended Markup Language (XML).

You can now access SQL Server 2000 using a Universal Resource Locator (URL). You can define a virtual root on a Microsoft Internet Information Server (IIS), and URLs that reference the virtual root have access to the XML functionality of SQL Server 2000.

IIS applications can use URLs, ADO, or OLE DB to work with the XML functionality of SQL Server 2000:

You can define XML-Data schemas that provide an XML-based view of the data in SQL Server 2000 tables and views. The XML-Data schemas can then be referenced in XPath queries to give the queries access to the data in the tables or views.

The results of SELECT statements can be returned as XML documents. The SQL Server 2000 Transact-SQL SELECT statement supports a FOR XML clause that specifies that the statement results be returned in the form of an XML document instead of a relational result set. Complex queries, or queries that you want to make secure, can be stored as templates in an IIS virtual root, and executed by referencing the template name.

You can store XML documents in SQL Server 2000 databases using new system stored procedures. You can then expose the data from the XML document as a relational rowset using the new OPENXML rowset function. OPENXML can be used anywhere a rowset function can be used, such as in place of a table reference in a FROM clause. This allows you to use the data in XML documents to insert or update data in the tables of the database.

In a relational database such as Microsoft?SQL Server 2000, all operations on the tables in the database produce a result in the form of a table. The result set of a SELECT statement is in the form of a table. Traditional client/server applications that execute a SELECT statement process the results by fetching one row or block of rows from the tabular result set at a time and mapping the column values into program variables. Web application programmers, on the other hand, are more familiar with working with hierarchical representations of data in XML or HTML documents.

SQL Server 2000 introduces support for XML. These new features include:

The ability to access SQL Server through a URL.

Support for XML-Data schemas and the ability to specify XPath queries against these schemas.

The ability to retrieve and write XML data: Retrieve XML data using the SELECT statement and the FOR XML clause.

Write XML data using the OpenXML rowset provider. Enhancements to the Microsoft SQL Server 2000 OLE DB provider (SQLOLEDB) that allow XML documents to be set as command text and to return result sets as a stream.

Hope this helps. -- sg

More related discussion and information sharing from that page

OK, so apparently SQL server can do the following:

It acts like a web server and lets you send SQL queries (presumably with GET/POST HTTP requests).
It returns the result formatted as an XML document.
Moreover, you can store XML documents in the database itself (does SQL Server actually parse the XML and validate it, or does it just store the raw bytes?) Am I correct? Please add features I missed. -- StephanHouben

The XML is parsed. SQL Server also provides SQL operators that perform XPath queries. This can be slightly useful, but what would really rock is a way of actually indexing and querying XML itself; you wouldn't need SQL anymore.

SqlServer utilities

Data Transformation Services (DTS) and Distributed Management Objects (DMO) are two of such tools included in the product. Additional information and sample code available from an author of ASP101 at http://www.asp101.com/authors/default.asp?author=Carvin%20Wilson

SqlServer QuickQuestions

Q Is there a tutorial on how to use SYSOBJECTS to track down procs and views with references to objects in a different database? I need to do this due to database name change and I am not sure which views and stored procedures get broken by the change. Thanks

A Use the views under the INFORMATION_SCHEMA um... owner/schema (MSSQL confuses DB users with schemas) in the master database. That should be a quick start. -- pjl

Q A colleague of mine said SQL/2000, even to this day, is open to intrusions very easily. Any comments on whether it is a fair statement compared to the alternatives? What about the security of credit information for sites with web presence? -- dl

A Mixed mode authorization encryption is not terribly secure. SQL Server makes massive use of the O/S, so any O/S vulnerability may impact SQL Server (another case of 'seamless integration of the application with the operating system' issue, which is just bad engineering). Ditto for using IIS with SQL Server. Also, never ever put SQL Server on a domain controller. The level of permissions needed (of which I still cannot find a straight answer) means that it runs as a machine admin, which on a DC is the DOMAIN admin. If you lose SQL Server, you lose the domain. Extended stored procedures allow it run almost anything on the O/S, which then allows you to issue NET and other commands on the DC as a privileged user. Very bad, but this is what MS Small Business server (where one server may play many roles) opens you up to. Never ever expose a database server to the internet. It helps if you do not use the default port (1433) but this applies to any DB server you care about. -- pjl

Microsoft SQL Server 2000 test results decertified http://www2.itworld.com/cma/ett_content_article/0,2849,1_1361,00.html (BrokenLink? 2005-08-31)

But resubmitted and they are in 2nd place now behind IBM DB2. SqlServer 2000 final has also been released.

-- sg

How many people have managed to use SqlServerWithJava?

In addition I would like to mention we got badly burned by MS Sql Server replication (the Sql Server 2000). We were replicating to a remote site as part of our dstributed reporting/disaster recovery strategy we were developing. Our production server went down, so we went to the replicated copy to recover the data and data was missing. Lucky for us we had backups. Replication is not to be trusted. You may have to buy an expensive 3rd party product to actually do this. We just canned it and are using backup and database file shipping instead. A friend of mine at another organization mentioned the same issues we ran into and they ditched replication as well. -- pjl

sounds like it wasn't set up right, it has always worked for me

{Ideally it should give you warning messages if it's not replicating properly rather than wait for you to find out the hard way. It's not the 1960's anymore. Also, I hear reliability is improving since your 2000 version because MS wants to compete on Oracle's turf now, not just the small/medium RDBMS markets. However, pricing also is starting to be Oracle-like.}

Sql Management Studio Change Gripe

Microsoft changed Sql Management Studio (SMS) 2008 to be quite different from SMS 2005. For example, in the "5" version you could easily save a query into a regular file. In 8, you can't. You have to first create a "Solution" (cough cheesy corp-speak for "project") and "save" your query into an unintuitive virtual filing system. It's become bureaucratic and a WalledGarden. Anti-Kudos MS!

What they've done is made it more like Visual Studio. It wouldn't surprise me if at some point they merged the two. Whether or not that's a good idea is another story.

Even in VS 2010, one can open and save to an individual source file. I don't mind if they make the project-oriented view as an option, just don't make it the only choice.

GUI Side-Effects

A consultant was brought into our org to give tips on migrating Oracle databases to SqlServer because the company wanted to spend less on databases and use GUI's instead of command lines and scripts to manage schemas and configurations.

The consultant recommended that scripts be used for SqlServer schema and config management instead of the GUI so that the schemas and configurations can be recreated if needed on a migrated or crash-restored server. Managements' jaws dropped. "Easier GUI's are one of the selling points of Microsoft over Oracle. Now you are telling me that best practice is to script?"