| Edwin Daniel's profileMicrosoft technologies a...BlogLists |  Tools  Help |
|
June 29 Managing the Windows Internal Database (SQL Server 2005 Embedded Edition)I keep getting questions on the Windows Internal Database. The first time I heard about this, I thought Microsoft may have missed out on informing me about the SQL Server 2005 Embedded Edition as this does not appear in the list of Editions for SQL Server 2005 ;-). This is the replacement for WMSDE which a few of the Microsoft products - Windows Sharepoint Services 3.0 and Windows Software Update Services 3.0, for example- use as a backend database. Most of the questions I get are administration related like how to shrink the database files, move them on a different partition, etc. By default, there is no tool available to manage the database hosted in this instance (MICROSOFT#SSEE). But you can use the available SQL Server 2005 tools to do administration and maintenance. The simplest there is will be to install SQL Server 2005 Management Studio Express. This is an available download from the Microsoft Download Center. You just have to register this instance on the Management Studio Express to be able to administer it. One thing to remember is that during the registration process, you have to specify the Network Protocol option in the New Server Registration window to be Named Pipes as this is the one being used by this instance. Once connected, you can now start managing the databases running on this instance. Another way is to use the sqlcmd tool. This is the command-line utility of choice for SQL Server 2005, although you can still use the osql utility. You need to download the SQL Server 2005 Native Client and SQL Server 2005 Command Line Query Utility from the Microsoft Download Center. You need to install the Native Client first before installing the Command Line Query Utility. Once installed, you can run sqlcmd.exe from the command line. This is typically installed in this directory
To connect to the Windows Internal Database instance (MICROSOFT##SSEE), run this command
Once connected, you can now execute those TSQL scripts that you are familiar with to manage and administer databases in this instance. June 20 Views in SQL Server 2005I normally get inquiries regarding migration from SQL Server 2000 to SQL Server 2005. Most of which have something to do with security mechanisms. Introduced in SQL Server 2005 is the new concept of schemas. Objects are now grouped in schemas unlike in SQL Server 2000 where we refer to objects using their owners. Now, most vendors who did not define their objects in this manner will have problems when they migrate to SQL Server 2005 as their owners are now converted to schemas. This is going to be a big headache if there are a lot of owners owning different objects. One way to do this is to create those schemas if they are not created assign users permissions to those schemas. Another way to do it is to create views. Views provide backward compatibility by defining a view to emulate a table that used to exist but whose schema has changed. This also helps users go through the upgrade process, especially those who frequently access the database by executing TSQL queries. It may be a lot of work but this is what one has to work with if databases have to be migrated to SQL Server 2005. I never thought that this could be a possible inclusion in the migration process from the point of view of both applications and end users |
|
|
