You may be thinking installing SQL Server 2008 Express Edition is easy, yes it is when you have simple terms of usage. No issues for version 2005 though, just keep in mind about SQL Server 2008 installation when Visual Studio 2008 is/isn't present? issues to resolve.
Just to talk in list of documentation, SQL Server Express makes it easy to develop data-driven applications that are rich in capability, offer enhanced storage security, and are fast to deploy. And with SQL Server 2008 Express with Advanced Services edition includes a new graphical management tool, features for reporting, and advanced text-based search capabilities. You got it right that both of these editions are available to download for free and can be redistributed subject to agreement. For testing and managing smaller application this SQL Server Express edition is an ideal choice for ISVs, server users, non-professional developers, Web developers, Web site hosts, and developers who are building client applications.
So for the installation gotchas such as installation issues and guideliness about dependancy on Visual Studio SP1 and .NET (dot-net) framework service packs & versions here is an excellent post by Bob Ward SQL2008Express_dotnet_Framework issues resolution and other links from SQLExpress team. Make sure you are following the above post if you have any issues during the installation!
Its over 2 weeks that SQL Server 2008 RTM is released and no doubt in stating that many DBAs might have performed downloads from their MSDN/Technet subscription to install within their environment.
Remember feature pack download for SQL Server 2005 couple of years ago, in the same situation you have to be aware about such pack of installation which will have a collection of stand-alone install packages that provide additional value for your SQL Server 2008 management. They consists redistributatle components, add-on providers and backward compatibility components too.
This SQL-2008-Feature_Pack download link gets you there to choose what kind of feature pack you need to install within the existing SQL Server 2008 platform. For the sake of clarity you can download the appropriate file by clicking the links on that page and then run the downloaded file to install that package. Not only that the classification of package that is required or intended are marked as Customer, Partner, Developer to indicate that which package suits for which audience.
Last piece of word is don't forget to download and test them before you deploy as a whole in to your new 2008 Production environment, you never know it may hit a problem and be proactive to solve such teething issues.
In continuation to SQL Server 2008 installation when Visual Studio 2008 is/isn't present? post that you might be aware that one of the issues with the SQL Server installation was a dependency on Visual Studio 2008 SP1.
Though it is not a complete show stopper to continue the installation of SQL Server 2008, few Enterprises will wait for correct and actual file so that they can get update to install Visual Studio Service Pack1 and continue the SQL Server 2008 instllation. The good news is It's now available on the Microsoft site. If you're installing SQL Server 2008 on a system with VS, or VS components, you'll want to get this update so you're ready.
In continuation to the SQL2008-InstallationVows on Windows Server 2008 there was a brief notes on pre-requisite of Visual Studio 2008 Service Pack when you are trying to install the SQL setup. The release notes were bit confusing that you have got with setup files.
To correct the confusion and make it more clear Microsoft released the following updated notes: SQL2008ReleaseNotes on Visual Studio
After I knew about the SQL 2008 RTM release news I have blogged SQL2008RTM-official here and as usual I have attempted to download the setup media from Technet which took a while (3 hours) to download the file, due to the obvious reasons of MSDN/TEchnet premium subscribers all over the world were attempting the same.
Ok, I have got the setup media and to test upon I have downloaded the files for following editions:
SQL Server 2008 Enterprise. SQL Server 2008 Enterprise is a comprehensive data management and business intelligence platform that provides enterprise-class scalability, data warehousing, security, advanced analytics and reporting support for running business-critical applications. With this edition, it is possible to consolidate servers and perform large-scale online transactional processing.
SQL Server 2008 Web. SQL Server 2008 Web is designed for highly available, Internet-facing Web-serving environments running on Windows Server. SQL Server 2008 Web provides the tools necessary to support low-cost, large-scale, highly available Web applications or hosting solutions for customers.
As the SQL Server 2008 Web edition is quite interesting and suitable for web-related applications as referred above. I need to test the editions in order to ensure the Web edition is suitable for set of clients I support to reduce the costing of deploying the SQL Server 2008 within their data platform, interesting eh.
Extracted the setup files and as one of the best practice I haven't attempted to install SQL Server to my laptop directly (remembering the Old-CTP6 installation issue), rather I have created a new Virtual Machine (Virtual Server 2005) with Windows Server 2008 to install. So far so good, now the flurry of troubles are started after a double-click of setup file on [en_sql_server_2008_web_....] directory!
First one is :
- SQL Server does not install the .NET Framework 3.5 software development kit (SDK). However, the SDK contains tools that are useful when you use the .NET Framework for SQL Server development. You can download the .NET Framework SDK from the .NET Framework Web site.
- Requirements to restart computers during SQL Server Setup: Installation of the .NET Framework requires a restart of the operating system. If Windows Installer installation also requires a restart, Setup will wait until .NET Framework and Windows Installer components have installed before restarting.
Next one is requisite for Visual Studio (for a moment I wondered what this is for!)
Rule "Previous releases of Microsoft Visual Studio 2008" failed.
Struggled to find out why the setup is bombing out with same error (2 times), a couple of searches on MVP private newsgroups found the resolution, bingo!
956139 Visual Studio 2008 SP1 may be required for SQL Server 2008 installations
Then all good with the installation and completed with no further errors, phew.
Failed to start SQL Server services, with no obvious errors or indication. Inspite of checking the local policy on the server to ensure the relevant service account has got privilege to logon locally with ADMIN privileges. Like the one below:
Finally I have found the detailed information and resolution on the issues that might occur on Windows Server 2008 Firewall issues - Windows-SQLServer_2008_FirewallWatchout with an excellent post by Shawn Hernan (thanks).
Hope you might fall into one of the above issues or all of them and I believe this should help.
Ok, SQL Server 2008 is RTM'd now and its time to change the section of this blog section as 'SQL Server 2008' without any tags!
to
Yes it is.
Microsoft press announces that SQL Server 2008 is available for users and as per PressRelease :
...announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company’s acclaimed data management and business intelligence platform. This version of SQL Server provides powerful new capabilities such as support for policy-based management, auditing, large-scale data warehousing, geospatial data, and advanced reporting and analysis services. SQL Server 2008 provides the trusted, productive and intelligent platform necessary for business-critical applications.
It is indeed a great news for the SQL Server users about 2008 RTM, as their journey begins since last year from CTP4 onwards from this CTP-Post & [SQL Server 2008 features and getting through CTP, RC, RTM. Are we there yet?] here.
So now the question is about number of editions that are available, it will be (as per BOL):
SQL Server 2008 Enterprise. SQL Server 2008 Enterprise is a comprehensive data management and business intelligence platform that provides enterprise-class scalability, data warehousing, security, advanced analytics and reporting support for running business-critical applications. With this edition, it is possible to consolidate servers and perform large-scale online transactional processing.
SQL Server 2008 Standard. SQL Server 2008 Standard is a complete data management and business intelligence platform that provides best-in-class ease of use and manageability for running departmental applications.
SQL Server 2008 Workgroup. SQL Server 2008 Workgroup is a reliable data management and reporting platform that delivers secure, remote synchronization and management capabilities for running branch applications. This edition includes core database features and is easy to upgrade to the Standard or Enterprise edition.
SQL Server 2008 Web. SQL Server 2008 Web is designed for highly available, Internet-facing Web-serving environments running on Windows Server. SQL Server 2008 Web provides the tools necessary to support low-cost, large-scale, highly available Web applications or hosting solutions for customers.
SQL Server 2008 Developer. SQL Server 2008 Developer allows developers to build and test any type of application with SQL Server. This edition features all of the functionality of SQL Server Enterprise but is licensed only for development, test and demo use. Applications and databases developed on this edition can easily be upgraded to SQL Server 2008 Enterprise.
SQL Server 2008 Express. SQL Server 2008 Express is a free edition of SQL Server that features core database functionality including all of the new SQL Server 2008 data types, in a small footprint. This edition is ideal for learning and building desktop and small server applications, and for redistribution by ISVs.
SQL Server Compact 3.5. SQL Server Compact is a free embedded database designed for developers and is ideal for building stand-alone and occasionally connected applications for mobile devices, desktops and Web clients. SQL Server Compact runs on all Microsoft Windows platforms, including the Windows XP and Windows Vista operating systems, and on Pocket PC and smartphone devices.
As Microsoft keeps its words about costing on deploying this product, in terms of licensing as per License_pressrelease information.
So whats the catch on the downloads, it is now available to MSDN and TechNet subscribers and will be available for evaluation download from Aug. 7, 2008. SQL Server 2008 Express and SQL Server Compact editions are available for free download now FromHere.
When it comes to the data encryption within SQL Server 2008 you would think about Transparent data encryption (TDE) which is a new encryption feature.
It sounds good that this is a high level security feature you can deploy to secure the data, and a catch is you can do it without affecting existing applications. Traditional methods in implementing encryption in a database traditionally involves complicated application changes such as modifying table schemas, removing functionality, and significant performance degradations. A good example of taking the scenario within previous versions such as use of encryption in SQL Server 2005, the column data type must be changed to varbinary; ranged and equality searches are not allowed; and the application must call built-ins (or stored procedures or views that automatically use these built-ins) to handle encryption and decryption, all of which slow query performance. There may be other references to usage of third party tools in this regard but still they are not exceptional to this type of performance issues when you deploy other type of encryption with the application data management.
What TDE can offer to solve this issue?
TDE solves these problems by simply encrypting everything. Thus, all data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk. This works underlying feature of Windows Operating system using Encrypting File System (EFS) and BitLocker Drive Encryption (BDE) on a similar scale and are transparent to the user.
Good to know on more about SQL Server Encryption is we have 2 levels of encryption, database-level and cell-level that uses a key management type of method. Microsoft Technet documentation refers on methods on key management that (source & credit- Technet):
At the root of encryption tree is the Windows Data Protection API (DPAPI), which secures the key hierarchy at the machine level and is used to protect the service master key (SMK) for the database server instance. The SMK protects the database master key (DMK), which is stored at the user database level and which in turn protects certificates and asymmetric keys. These in turn protect symmetric keys, which protect the data. TDE uses a similar hierarchy down to the certificate. The primary difference is that when you use TDE, the DMK and certificate must be stored in the master database rather than in the user database. A new key, used only for TDE and referred to as the database encryption key (DEK), is created and stored in the user database.
This hierarchy enables the server to automatically open keys and decrypt data in both cell-level and database-level encryption. The important distinction is that when cell-level encryption is used, all keys from the DMK down can be protected by a password instead of by another key. This breaks the decryption chain and forces the user to input a password to access data. In TDE, the entire chain from DPAPI down to the DEK must be maintained so that the server can automatically provide access to files protected by TDE. In both cell-level encryption and TDE, encryption and decryption through these keys is provided by the Windows Cryptographic API (CAPI).
The following figure shows the full encryption hierarchy. The dotted lines represent the encryption hierarchy used by TDE.
.gif)
Figure: SQL Server encryption key hierarchy with TDE and EKM
So the next question will be how to enable TDE?
Books Online (BOL) documentation has lots of information and code examples to do so. Moreover you must ensure to have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
As per BOL (again)
Perform the following steps in the master database:
1. If it does not already exist, create a database master key (DMK) for the master database. Ensure that the database master key is encrypted by the service master key (SMK).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;
2. Either create or designate an existing certificate for use as the database encryption key (DEK) protector. For the best security, it is recommended that you create a new certificate whose only function is to protect the DEK. Ensure that this certificate is protected by the DMK.
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;
3. Create a backup of the certificate with the private key and store it in a secure location. (Note that the private key is stored in a separate file—be sure to keep both files). Be sure to maintain backups of the certificate as data loss may occur otherwise.
BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’ WITH PRIVATE KEY (FILE = ‘path_to_private_key_file’,ENCRYPTION BY PASSWORD = ‘cert password’);
4. Optionally, enable SSL on the server to protect data in transit.
Perform the following steps in the user database. These require CONTROL permissions on the database.
5. Create the database encryption key (DEK) encrypted with the certificate designated from step 2 above. This certificate is referenced as a server certificate to distinguish it from other certificates that may be stored in the user database.
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert
6. Enable TDE. This command starts a background thread (referred to as the encryption scan), which runs asynchronously.
ALTER DATABASE myDatabase SET ENCRYPTION ON
To monitor progress, query the sys.dm_database_encryption_keys view (the VIEW SERVER STATE permission is required) as in the following example:
SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys
As mentioned before TDE is designed to be as transparent as possible, virtually with no application changes are required. The only task you need to ensure is not to have any READONLY filegroups on the database and still there is a possibility to perform TDE as such, that involves the filegroups must first be set to allow writes. After the encryption scan completes, the filegroup can be set back to read only. Key changes or decryption must be performed the same way.
The impact as such of CPU & memory usage is minimal if you want to compare the system resource monitor during the TDE tasks, as the whole process of encryption involves the database level access that can leverage indexes and keys for query optimization. But the main task of encryption is CPU intensive and is performed at I/O cost, so you need to ensure to schedule or perform TDE during a low period of I/O & usage on the server.
Don't assume that TDE can be used to restrict the access to data or control the accessibility, all it performed is dependant on users who have permission to access the database are still allowed access; they do not need to be given permission to use the DEK or a password. As per the documentation TDE feature is only available on Enterprise edition and Developer edition (which is not for production usage) which means other editions cannot be encrypted by using TDE and TDE-encrypted databases cannot be used in other editions. If you attempt to perform TDE such as restore from a TDE'd database then the server will error out on attempts to attach or restore.
SO how about backups when you use the TDE?
Obviously the database backups are encrypted too when TDE is enabled. The backup encryption will follow the same method as it performed on usual data encryption method, so you need to ensure or take utmost care to keep the certificate and if this is lost, then the data will be unreadable. So the best practice is to backup the certificate along with the database and store it on same directory or server in order to access when the restore task is performed. Bear in mind the certificate will have 2 files and both of them should be seperated from the database backup file for obvious security reasonse and you can consider using the EKM feature to store & maintain the keys used for initial TDE process.
There may be discussions on using Encryptng File System methods rather than TDE directly and just a last thought on what kind of disadvantage will be in using EFS will be that primarily in performance and administration. EFS is not designed for high-concurrency random access and I/O operations may become bottlenecked and serialized. Such factors and information are already documented within KBA You may experience decreased performance in some features of SQL Server 2005 when you use EFS to encrypt database files & EFS and Vista... and XP blog entry. Due the fact that involvement of additional manpower when decrypting the EFS mechanism on operating system, as most of the Enterprises may not have similar levels of Operating System Administrator level privielges to the DBAs for obvious reasons.
So next time when you have a the data encryption requirement then think about using or testing TDE methods on SQL Server 2008, as the different levels of encryption available in SQL Server and Windows can be leveraged to provide defense in depth and greater overall security. Transparent data encryption provides a good blend of ease of administration, ease of use, performance, and security. TDE also provides a comprehensive defense because the encryption stays with the database even when it is moved to different locations. Also you need to wait upon what SQL Server 2008 RTM can offer in terms of the new features that are discussed earlier.
Until SQL Server 2000 the sample databases or files are installed along with binaries & program files, since version 2005 this has been changed and they are available from Microsoft download site and SQL Server Reporting Services (SSRS) has no exclusion.
No doubt that Reporting Services API is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. Usage of SSRS is increased for OLTP & OLAP based services, such as Business Intelligence framework can provide the data management capabilities of SQL Server and Windows Server with familiar and flexible office System applications (Sharepoint & SCOM) to deliver real-time information to support daily operations and drive decisions.
Samples databases and projects contains Reporting Services samples in the following five categories: Application Samples, Extension Samples, Model Samples, Report Samples, and Script Samples.
As you can see these are available on CodePlex site which is an open source site where the users (even you can) contribute any samples that might be useful for the community. To download these samples click on the Releases tab on that link and optionally select another (older) release, then click on the Windows Installer package (MSI file) which matches the architecture of the installation computer.
The usefulness of the above link is you can download samples for the versions 2005 and 2008, on the categories Application Samples, Extension Samples, Model Samples, Report Samples, and Script Samples.
To admit few days ago I was wondering where I can test the samples for Reporting Services on one of the BI based project and I have achieved what I need.
Are you desperate to get Relase To Manufacturing (RTM) release of SQL Server 2008?
Well, long wait for SQL Server 2008 is nearly over.
It is an official announcement that SQL Server 2008 will be on the August price list. This means that customers and partners will soon be able to place their orders for SQL Server 2008 as we get ready to RTM in Q3 of this year. As per Bob Kelly, Microsoft Corporate Vice President of Infrstructure Server Marketing announced during Worldwide Partner conference on July 09th 2008. Good news for finance people is cost of deploying SQL Server 2008 in terms of licensing will same as SQL Server 2005 costs (no additional or small prints).
You may be aware that we have seen a preview/rehearsal of SQL Server 2008 during June 2008 which is RC0, more from this SQL Server 2008 Release Candidate - public download is available now. post here and for more joy don't forget to upgrade the local SQL 2008 documentation aka BOL - SQL Server 2008 RC0 and 3.5 Compact edition Books Online - download and update your copy fyi.
So it is defiently a long wait for the users to get hands-on with RTM on SQL Server 2008 which has been announced during June 2007 (CTP) and promised earlier to release by February 2008, as you may refer SQL Server 2008 features and getting through CTP, RC, RTM. Are we there yet? post here.
So having the official releases of Windows Server 2008, Visual Studio 2008 and now with SQL Server 2008 it will be an interesting career path for IT Pros & Developers in coming years (atleast until 2010). More interestingly I would say how many companies out there will be officially jumping into pool of these 3 hard-core 2008 version products immediately and no wait until next Service pack release, better deploy first than waiting for long time.
Being one of the launch leader of HeroesHappenHere & MVP got a free copy of Windows Server/Visual Studio/SQL Server 2008 DVDs and now can't wait to get my hands on RTM which is the final product of version 2008 on SQL Server.
More to come.....
SQL Server Reporting Services has been introduced in 2000 version but not catched within the user environment until the Reporting Services 2005 has been rolled out. In this regard administering a Reporting Services installation requires that you know how to perform routine maintenance tasks for server components, and how to manage the content and ongoing operations of the report server.
Reporting Services components include Report Server Web service, for background processing applications, which handle scheduled operations and report delivery and a report server database on SQL Server. All of these included on Server side and what it takes to manage them, the membership in the local Administrator group on the report server computer. If your installation includes server components that run on remote computers, you must have administrator permissions on those computers if you want to manage those servers over a remote connection. Obviously DBA permissions must have SYSADMIN privilege and if SSRS is on domain controller then the DBA login must be domain administrator.
So how about scaling up the Reporting Services solution using 2005 and 2008, more to know what has been changed between these 2 major versions in SQL Server arena. Refer to Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned link for more information.
In continuation with the BOL update topic on SQL2008-RC0_BOL post here I have had troubles in installing the RC0 Books Online on to my laptop.
As per the error below I thought it may be problem with the Windows Installer service on my laptop (Vista Ultimate) and tried to install afresh on a central monitoring server (Windows 2003 server) & local machine (Windows XP), still no luck to install the documentation.
On my laptop I have Windows Installer service 3.1 and that is not sufficient to carry out this BOL installation, so searched Microsoft KBA to see if there is any latest version is available. Bingo, here it is Windows Installer 4.5 is available and you need to download and apply on to the machine where you want to upgrade the SQL Server 2008 RC0 Books ONline documentation.
Hope this helps.
I'm sure that by now you are aware about the list of new features within SQL Server 2008 and recently I have had couple of emails from newbie DBA/users in SQL Server asking about any reference they can get on these new features, with a video.
In this regard Microsoft learning has provided right to the point of subject references as follows:
As in continuation with the latest release of SQL Server 2008 that is Release Candidate, I strongly recommend you update your local copies of SQL Server Books ONline aka BOL.
Here is the link to download the latest copy of SQL Server 2008 RC0 BOL - Link_to-Download and also I recommend the SQL Server 2008 Compact Edition documentation upgrade, such as CompactEdition information.
In continuation to my post about best learnings in Tech-Ed conference on SQL Server Data Services (SSDS), I'm kind of more interested to know about this new 'cloud based' data management solution for the Data Platform.
So the first question will be, what is this SSDS?
As per Microsoft documentation '... a highly scalable and cost-effective on-demand data storage and query processing web service. It is built on robust SQL Server technologies and helps guarantees a business-ready service level agreement covering high availability, performance and security features'. In simple terms, this helps the Enterprise to host the mission critical applications as a 'data platform', having the capability of presenting Data As A Service (DAAS) as cloud based application to build/get data quickly within LAN & WAN (internet). Documentation also refers that SSDS is accessible using standards based protocols (SOAP, REST) for quick provisioning of on-demand data-driven & mashup applications.
What will be data size limit on cloud?
Virtually there is no limit to store the data in the cloud, it depends on the business situation that you need to present data for your application. Rich features of XML with software standards such as SOAP interface can be used to design on-live applications that will help developers and service providers quickly run on-demand applications with ease.
You are talking Virtual storage of data, which simulates VIEW kind of provision. If so is it possible to get data dynamically or modify the data?
Yes, the schema accessibility is flexible and can be modified dynamically from/by the application, this is what gives 99.999% of high availability to the application and reliability for the database .... data any place.. any time!
What kind of application baseline can be formed/targeted?
Process high volume of data for on-demand applications without compromising high availability, also with data-archiving facility and storage for large data types (structured or semi-structured data with a flexible schema definition or unstructured data with related structured metadata). Also the ability to associate entities with large unstructured data objects (blobs) which could be accessed as URL addressable resources.
What kind of pricing & licensing is defined?
Still awaiting confirmation from Microsoft on the licensing/pricing model.
So what is this mode of virtual cloud data?
Cloud - virtual storage gives access to store on-demand data, process data for mission critical business applications. With the expertise of .NET (ASP.NET) & Biztalk services you can develop rich-content application with better performance.
To continue the discussion I would like to refer the following links such as webcasts & demos:
I was able to obtain most of the above subject answers from this SQL Server Data Services (SSDS) Primer link and finally excellent reference by Dave on Best Practice - Data Partitioning taking advantage of SSDS.