SQL Copy Database – Detach and Attach Drops Permissions
By jeremy_stoltzfusApparently, in SQL Server Management Studio, copying a database via Tasks -> Copy Database and choosing the “Detach and Attach” method can drop all user permissions from the source database. Awesome.
I’ve yet to figure out whether this always happens or if it’s only in specific circumstances, but I can say that I’ve had it happen to me multiple times. My advice: use the tried-and-true method of backing up the source database and restoring it to the new destination instead.
Installing SQL 2005 on Windows 2008 R2
By greg_hessAnother area where you may receive a warning is the Windows Firewall. You will need to configure the firewall to open up the proper ports. A discussion of the ports is available here. Microsoft has a KB article here that provides a script that you can use to open up the proper ports.
This can be come tricky when working with a named instance since SQL Server uses dynamic ports for those. There are two different options for dealing with this, the method I have used is to configure a fixed port for the named instance. Directions for doing that are here .
Web Services Part II
By lsk123In my last post, I had given a brief overview of web services and it many components.
From my perspective, web services are very useful when
provided by a database and it is a safe way to indirectly expose
data to an external source without having to provide direct
access to the data.
The following is a great article which describes how web services
can be hosted by sql server 2005/2008
http://www.developer.com/net/asp/article.php/3767311/Creating-Native-Web-Services-in-SQL-Server.htm
Reporting Services without MSSQL
By jeremy_stoltzfusFun Fact: It’s possible to use SSRS with a Non-MSSQL back end.
I was once tasked with creating a web front end to a directory that we used internally. It seemed like a perfect job for .NET and Sql Server Reporting Services (SSRS), both of which we were already using extensively, but there was a hitch in the plan: the directory was stored in FileMaker.
Fortunately, it’s possible to use SSRS with ODBC datasources and, in turn, any other database system you can connect to via ODBC. It’s as simple as creating a Data Source through Windows Administrative Tools then choosing “ODBC” as the type in your SSRS Shared Data Source. I’ve used this method to connect SSRS to both FileMaker and MySQL back ends.
SQL Server Full Text Indexing
By lsk123My current project requires searching for key words across PDF documents and so I spent some time researching on indexing and searching.
Most of the data that I needed to search on is stored in Text and BLOB columns. The site below was particularly helpful in understanding the use of iFilters and how to install iFilters for Adobe.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
Another web blog I found helpful in gaining additional theoretical knowledge on various kinds of indexes is show below.
SQL Server Storage Internals Part 5 - Properties of Clustered and Non Clustered Index pages.
