Asynchronously mirroring a SharePoint Content Database between Two Farms
Recently, I triumphantly setup an asynchronous content mirror between two separate SharePoint farms.
My SharePoint architecture consists of two separate farms in two different cities (San Diego and Las Vegas), both accessible over a corporate intranet. Each farm is unique and the configuration and administration databases contain unique information that cannot be mirrored from one site to another. The technique outlined below works, however, because the content databases are identical between the two sites and that is the only information that must be mirrored. One farm is a fail over for the other farm, so each farm contains the same sites.
After searching the Internet for information on how to mirror SharePoint data, I only found one MSDN article which stated that it should be done either with SQL Server clustering or with synchronous mirroring requiring a witness server. Eventually I found a picture in the MSDN showing a way to mirror only the SharePoint content databases between two farms asynchronously.
This picture outlined what I wanted, but I knew SharePoint well enough to know it was going to cause problems. I decided to try my hand at a solution, nevertheless. I started on the primary farm in Las Vegas, where I set up mirroring on the Sharepoint_Content database and mirrored it to the Sharepoint_Content database in San Diego, which was designated as the backup farm. By design, the San Diego content database was inaccessible while the mirroring was in place, so I had little chance of the backup farm actually loading up any pages. This was acceptable, however, because the backup farm did not need to work unless I failed over to it. I made some changes to my primary farm by adding a few announcements to a web part on a site and attached a picture to it. After some time, I decided to remove mirroring from the primary database. The reason I removed mirroring instead of doing a fail over, is because I could not have downtime on the primary farm. Nor did I want to take any chances breaking the database by reversing the mirroring if my technique did not work.
After breaking the mirror, the mirrored database showed as "Restoring.." until I ran the command: RESTORE DATABASE [SharePoint_Content] WITH RECOVERY.
This method is used to make the mirror available again as a stand-alone database. Once the database was available, I loaded up my SharePoint site on the fail over farm with my fingers crossed. As the page loaded, I saw a dreaded "Error 400 - Bad Request." I figured this would happen, as things are rarely straightforward with SharePoint. I didn't have time to address the issue so I decided to just restore from my most recent backup and put my experiment on hold. After issuing a restore command using stsadm, I saw a peculiar error which stated that it could not restore my site because no content databases were associated and suggested that I add a content database before proceeding.
Perhaps this could work after all! I loaded up the administration panel, found at http://servername:8080, and navigated to Content Databases under Application Management. The Sharepoint_Content database was listed as it should have been but I figured since it was not working as desired anyway, I could remove the SharePoint_Content database from the configurations and re-add it. First, I removed the content database and re-added a new content database with the same name and selected my SQL Server from the drop-down menu. I clicked OK and SharePoint updated its configuration. Soon the new content database was listed.
I hurried back to my site, refreshed the browser and to my delight, the page loaded and the announcements I had added in Las Vegas were displayed on the site running in San Diego. It was at this moment when I realized that this instance of making the impossible possible would be perfect fodder for my first blog post on netnerds.net.
Free SQL Server eBooks available
Brad McGehee just published what's sure to be another incredibly informative ebook on SQL Server Maintenance Plans. A list of free eBooks, including the SQL Server Tackle Box, can be found on Brad's blog. Check it, one time.
How to Import Data into SQL Server 2005 From DB2 (AS400/iSeries)
Ever since I read how much IBM's DB2 SMOKED Oracle and SQL Server in the tpc-c (OLTP) benchmarks, I've wanted to work with it. The benchmarks go a little something like this:
| RDBMS | tpc-c |
| IBM DB2 | 6,085,166 |
| Oracle | 4,092,799 |
| Microsoft SQL Server | 1,231,433 |
I mean, I don't work with servers that require nearly that horsepower but it's interesting to see.
So now I have a chance to work with DB2 on an iSeries server. Granted, I'm not doing much other than SELECT statements, but I'm having fun. The initial setup was a little confusing for me. According to IBM's documentation website, there's two ways to access the DB2 server: via SQL or via "traditional file access." The firm I work with uses traditional file access terminology so translation to SQL terms was necessary. Overall it was pretty easy; here's a cheat sheet:
| SQL Server | IBM file access |
| Schema/Owner | Database/Library |
| Table/View | File |
| Row | Record |
| Column | Field |
The accounting department gave me a PDF which contained the following information.
| DB Info | |
| File | CARPDIEM |
| Library | CDCLIENT |
| Fields | M00000, M00001 |
To get the Linked Server going, I installed the iSeries Client Access on the SQL Server which supplied the IBM AS400 OLE DB Provider. From there, I created the Linked Server with the following information:
| General | |
| Linked Server | AS400 |
| Provider | IBM AS400 OLE DB Provider |
| Product Name | <anything> |
| Data Source | <IP of server> |
| Security Options | |
| I mapped a local account using the credentials supplied to me by the accounting department. | |
Initally, when expanding the table structure, I received an error: The OLE DB Provider "IBMDA400" for linkedServer "AS400" reported an error. Acccess is denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider... Error: 7399. but after researching, found that I simply needed to select the box "Allow Inprocess" under the IBMDA400 Provider options and this error went away. I then experimented with a few different SELECT statements to see which one was easiest. Ultimately, I ended up using the following queries to import the data from the DB2 server into SQL Server 2005:
SELECT * INTO importClients from AS400.ServerName.CARPDIEM.CDCLIENT
If you don't know the variable for ServerName in the above query, you can refer to this tutorial. Also, I was able to use Microsoft's DB2 Provider, as described here. The Initial Catalog is the ServerName and the Package Collection and Default Schema are both the schema/username. Not sure what the speed differences between the two providers are, if I find the time to benchmark, I'll be sure to post the results.
Create a DB2-C Express 9.5 Linked Server in SQL Server 2008
In order to emulate a DB2/iSeries<->SQL Server setup that I deal with at work, I installed DB2 Express-C on SuSE Linux Enterprise Server 10. The DB2/Linux Server details, as well as a sample database and table name, are as follows:
| IBM DB2 Database | |
| Network Name | LUCAS |
| TCP Port | 50000 |
| Instance | DB2INST1 |
| Database | SAMPLE |
| Schema/Owner | DB2USER |
| Table | EMPLOYEES |
After the initial install and sample database/table creation, I set out to create a Linked Server in SQL Server 2008. Initially, I installed IBM Data Server Client client tools, which includes a few JDBC drivers, on the SQL Server. One of these providers is "IBM OLE DB Provider for DB2," showed up in the list of Linked Server Provider in SQL Server after the software was successfully installed.
Setting up the Linked Server was pretty straight-forward and initially seemed to work, using the following details:
| General | |
| Name | IBMDB2 |
| Provider | IBM OLE DB Provder for DB2 |
| Product Name | IBMADDB2.DB2COPY* |
| Data Source | SAMPLE |
| Provider String | <blank> |
| Location | LUCAS |
| Security Options | |
| Login Info | Your call. I mapped a local user to db2user. |
| Security Options | |
| All Options | Default |
| * IBMADDB2.DB2COPY is the name of the IBM DB2 provider in the Linked Server Provider list. | |
The server was created easily enough, but SQL Server's automatically generated SELECT statements kept erroring out. The syntax was as follows: SELECT fName, lName FROM IBMDB2..DB2USER.EMPLOYEES. Upon execution, the following error was returned:
Msg 7318, Level 16, State 1, Line 1
The OLE DB provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2" returned an invalid column definition for table ""DB2USER"."EMPLOYEES"".
So I added in the database name (IBMDB2.SAMPLE.DB2.EMPLOYEES) to the SELECT statement, executed the query and received the error:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2".
Past experience taught me that it was now probably time to look for a new driver. After some research, I found an IBM DB2 Driver in the SQL Server 2008 Feature Pack which can be found: OLEDB Provider for DB2 (DB2OLEDB.exe). According to Microsoft..
The Microsoft OLE DB Provider for DB2 version 2.0 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor.
After extracting the contents and clicking setup.exe as instructed, the full-on GUI-based installer failed. Ultimately, I had to run the install from the command line: setup.exe /L c:\HISInstall.log /S /INSTALLDIR C:\HIS and it worked flawlessly after a reboot that was required anyway by Windows Update.
Once the promising new driver was installed, it an additional 2 hours to figure out exactly what Linked Server parameters were required. I kept running into an error that said "The parameter is incorrect." Ultimately, I figured out how to set it up by running the Data Access Tool included with Microsoft's DB2 Provider. After I setup a successful connection to the DB2 Server, the Data Access Tool gave me the Provider String that I needed to create a successful connection.
| General | |
| Name | DB2 |
| Provider | Microsoft OLE DB Provider for DB2 |
| Product Name | DB2OLEDB2 |
| Data Source | LUCAS |
| Provider String |
Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37; PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER; Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW; DBMS Platform=DB2/MVS;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True; Connection Pooling=False;Derive Parameters=False; |
| Location | <grayed out> |
| Catalog | SAMPLE |
| Security Options | |
| Login Info | Your call. I mapped a local user to db2user. |
| System Options | |
| All Options | Default |
The Data Access Tool gives the username and password in clear text in the Provider String but I just removed that and chose to setup the username and password under the Security Options. Ultimately, this was the setup that worked for me. I am now able to query the data using a simple 4 part name: SELECT fName, lName FROM DB2.SAMPLE.DB2.EMPLOYEES. Success! If you'd like to create the Linked Server using only T-SQL, run the following command, suited to your environment, of course.
EXEC master.dbo.sp_addlinkedserver @server = N'DB2', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB',
@datasrc=N'LUCAS', @provstr=N'Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;
Host CCSID=37;PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER;
Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS;
Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=False;
Derive Parameters=False;', @catalog=N'SAMPLE'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2',@useself=N'False',
@locallogin=NULL,@rmtuser=N'db2user',@rmtpassword='########'In my next post, I'll cover a more likely DB2<->SQL Server scenario: creating a linked server in SQL Server 2005 to IBM DB2 running on an AS400/iSeries server.
Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008
To my knowledge, Microsoft has not yet updated their Knowledge Base to inform administrators how to migrate SQL Server logins to SQL Server 2008. The good news is that the SQL Server 2005 login migration knowledge base article applies to SQL Server 2008 as well. Below I've included a compilation of all of the migration procedures between various versions of SQL Server.
Migration is very easy -- basically, a stored procedure named sp_help_revlogin is created and executed on the source server. The resulting output is then copied and executed on the destination server.
Detailed instructions can be found at Microsoft KB Article 918992 and Microsoft KB Article 246133. Oh, also, Dell has a marvelous 54-page SQL Server 2000 to 2008 Migration Guide. It actually outlines, in an easy-to-read style, a lot of the best practices I learned about while studying for my MCITP certification.
Code For Login Migrations Between SQL Server 7 and SQL Server 2000
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----Code For Login Migrations From SQL Server 7/2000 to SQL Server 2005/2008
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GOCode For Login Migrations From SQL Server 2005/2008 to SQL Server 2005/2008
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GOMmm, hella swole code.
PowerShell 1.0: Backup and Restore a Database Between Two SQL 2005 Servers
My technical partner-in-crime, Brandon, created this PowerShell script last year in an effort to help him understand PowerShell a bit more. He noted that while it may not all be best practices SQL-wise, he tried his best to use as much PowerShell as possible.
#Powershell to backup and restore a SQL database from server1 to server2.
#2008, Brandon Abshire / netnerds.net
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
# create source and destination sql servers
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server1'
$d = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server2'
# set backup directory, dbname and grab date
$bkdir = 'e:\'
$remoteDir = 'e$\'
$dbname = 'databasename'
$dt = get-date -format yyyyMMddHHmmss
#create backup object and set properties
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 0
$dbbk.BackupSetDescription = "Backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.Devices.AddDevice($bkdir + "\" + $dbname + $dt + ".bak", 2)
$dbbk.MediaDescription = "Disk"
$dbbk.Incremental = $false
#Backup the database
$dbbk.SqlBackup($s)
#create restore object and set properties.
$dbres = New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
$dbres.Database = $dbname
$dbres.Action = 0
$dbres.ReplaceDatabase = $true
#Add the backup that is on the remote server by whatever method you wish.
#You can copy it locally first. I just used named pipes to grab it off the network, since it is a fast enough connection.
$dbres.Devices.AddDevice("\\" + $s + "\" + $remoteDir + $dbname + $dt + ".bak", 2)
$dbres.NoRecovery = $false
#Without the KillAll, my restore got stuck in "Restoring..." forever.
$d.KillAllProcesses('databasename')
#restore the database.
$dbres.SqlRestore($d)
#The database is now restored from one server to another.
#We must re-map the mappings on the database for my user, or login will fail.
#Set db to the specific database we want on the destination server.
$db = $d.databases["databasename"]
#Set newUser to the user we want to give permissions to and drop it.
#This user was restored from the other database and doesn't match up
#to the one on the destination SQL server.
#sp_change_users_login 'autofix','userlogin' could also be used but I
#wanted to practice PS moreso than SQL
$newuser = $db.users["username"]
$newuser.drop()
#Create a new user. Set the parent to the database we restored.
$newuser = New-Object ('Microsoft.SqlServer.Management.Smo.User')
$newuser.parent = $db
#Set the username and login name.
$newuser.name = "My User"
$newuser.login = "username"
#Set the default Schema, create the user and add the appropriate roles.
$newuser.defaultSchema = "dbo"
$newuser.Create()
$newuser.AddToRole("db_datareader")
$newuser.AddToRole("db_datawriter")Credit goes out to the Allen White, MVP of the MSDN Forums for a PowerShell/SQL quickstart.
SharePoint Server 2007 SP1: Incorrect syntax near ‘COLLATE’ When Joining a Farm
While attempting to add a new Indexing Server to my firm's SharePoint farm using the SharePoint Products and Technologies Configuration Wizard, I ran into the following error when clicking Retrieve Database Names:
Incorrect syntax near 'COLLATE'
************** Exception Text **************
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'COLLATE'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.PostSetupConfiguration.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.DatabaseTableWithColumnExists(String table, String column)
at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.GetV3WSSConfigurationDatabases()
at Microsoft.SharePoint.PostSetupConfiguration.ConnectConfigurationDbForm.GetDatabasesButtonClickEventHandler(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)The SharePoint_Config's database and set to collation is set to Microsoft's recommended collation for a SharePoint farm: Latin1_General_CI_AS_KS_WS while SQL Server 2005's default collation on the master database is set to SQL_Latin1_General_CP1_CI_AS. For whatever reason, this is causing a very time consuming issue on our SharePoint farm. With the help of my SharePoint Consultant friend, Trevor, I was able to resolve it by doing the following:
- Ensuring the newly installed SharePoint server's service pack matched that of the farm. Initially, the RTM was installed on the server but the farm was running SharePoint Server 2007 SP1.
- Typing in the Sharepoint config's database name (SharePoint_Config) manually instead of relying on the "Retrieve Database Name" button to populate the drop-down list.
I know it's not a solution so much as it is a workaround but after three days of failed configuration attempts, I really just wanted it to work and that did the trick.
Efficiently Compare Two SQL Server Tables Using Mostly VBScript
Recently, a friend of mine asked to write a script to compare two SQL Server tables using mostly VBScript. The very thought made me shudder; I wanted desperately to compare the tables in SQL Server but ultimately, the company's requirements prevented us from doing so. Here were the stipulations:
- First, the script should be as resuable as possible. Many tables will be compared.
- The column names are unknown -- only the database name and table were consistent.
- The SQL Servers were 2005 and both located on the same domain.
- Windows Authentication must be used.
- SSIS/DTS cannot be used.
- Files can be written to the hard drive, but only as a last resort.
- OPENROWSET/OPENDATASOURCE/Linked Servers cannot be used.
- The script's output must be either TRUE or FALSE. TRUE if the tables matched, FALSE if they didn't.
- IDENTITY columns must be ignored.
I told him I'd be back in 20 minutes as my brain figured it out. This is what it came up with:
- ADO's recordset getstring() will be used for direct comparisons. No variables will need to be assigned, no data will need to be written.
- The INFORMATION_SCHEMA will be queried to discover column names. Column names will be reused by both SELECT and ORDER BY.
- ORDER BY will be used to ensure the data is returned in the same order on both tables.
- COLUMNPROPERTY will be used to check if the column is an identity (I know excluding the ID means that the table may not be exactly a match, but it's what was requested, so I obliged.)
strServer1 = "server1"
strServer2 = "server2"
strDatabaseName = "mydatabase"
strTableName = "mytable"
CALL CompareTables(strServer1,strServer2,strDatabaseName,strTableName)
SUB CompareTables(server1,server2,databaseName,tableName)
'This is a SQL Server 2005 Connection String with Windows Authentication. Change as necessary.
connString1 = "Provider=SQLNCLI;Server=" & server1 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
connString2 = "Provider=SQLNCLI;Server=" & server2 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
'Since this is a template script, we don't know what the columns are so we'll use the INFORMATION_SCHEMA to figure it out.
'Not only will this be useful in the SELECT statement (SELECT col1, col2, col3) but also is necessary for the ORDER BY.
'Because we'll be using a text comparison, columns and order by must be in the same exact order on both server1 and server2
strSQLColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & tableName & "' AND (COLUMNPROPERTY(OBJECT_ID('" & tableName & "'),column_name,'IsIdentity')) != 1 ORDER BY ORDINAL_POSITION ASC"
SET rsColumns = CreateObject("adodb.recordset")
rsColumns.Open strSQLColumns, connString1, 1,1
IF rsColumns.eof and rsColumns.bof THEN
Msgbox "The table has no columns or does not exist."
rsColumns.close
SET rsColumns = NOTHING
Wscript.quit
ELSE
do until rsColumns.eof
strColumns = strColumns & rsColumns("COLUMN_NAME") & ","
rsColumns.movenext
loop
'Take off last comma
strColumns = left(strColumns,len(strColumns)-1)
rsColumns.close
SET rsColumns = NOTHING
END IF
'Ninja
strSQL = "SELECT " & strColumns & " from " & tableName & " ORDER BY " & strColumns & " asc"
SET rsserver1 = CreateObject("adodb.recordset")
rsserver1.Open strSQL, Connstring1, 1,1
IF rsserver1.eof and rsserver1.bof THEN
Msgbox "No Rows to Compare in Table 1"
rsserver1.close
SET rsserver1 = NOTHING
wscript.quit
END IF
SET rsserver2 = CreateObject("adodb.recordset")
rsserver2.Open strSQL, Connstring2, 1,1
IF rsserver2.eof and rsserver2.bof THEN
Msgbox "No Rows to Compare in Table 2"
rsserver1.close
SET rsserver1 = NOTHING
rsserver2.close
SET rsserver2 = NOTHING
wscript.quit
END IF
IF rsserver1.GetString() = rsserver2.GetString() THEN
Result = TRUE
ELSE
Result = FALSE
END IF
rsserver1.close
SET rsserver1 = NOTHING
rsserver2.close
SET rsserver2 = NOTHING
MsgBox Result
END SUBAlthough VBScript is a favorite of mine, I'm a bit surprised that it's still a requirement on new project. Anyway, thanks for the distraction, friend. It was nice to get away from VM troubleshooting for a bit.
SQL Server: Creating a User-Defined Data Type from Scratch
I've always been slightly jealous of PostgreSQL's network data types, speficially, it's IPv4-supporting cidr type. I wondered if Microsoft would ever implement a similar type (I'm still wondering) but until they do, I'll work with the ghetto I've got: User-Defined Datatypes, which were introduced in SQL Server 2005.
Here's how to create a UDDs in three easy steps:
- Create a new data type based on a native type.
- Create a new rule.
- Bind the new rule to the new data type.
Below, I willl use these 3 easy steps to create a simple ipv4 data type. First, I need to figure out which native type to build upon. Based a standard IPv4 address (herein ipaddr/IP address/IPs), IPs have a max of 15 characters (255.255.255.255) so I'll go with varchar(15) as the desired native data type for the new data type, "ipv4."
EXEC sp_addtype ipv4, 'VARCHAR(15)', 'NULL'Next, a rule must be created. I'm not going to get super picky here to include subnets and what not, so I'll just write a rule that
1. Checks to see if the value has 3 dots/periods.
2. Checks to see if the IP address is valid by getting its numerical value and...
3. Ensures the numerical value is not greater than 4294967295 (the numerical value for "255.255.255.255")
CREATE RULE IPv4Format AS
((LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3) -- if there 3 periods
AND (16777216 * CAST(PARSENAME(@strIP,4) as bigint) + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1)) < 4294967296
GONext, the rule must be applied:
EXEC sp_bindrule 'IPv4Format', 'ipv4'Now that I have a new data type called "ipv4" I'm going to try it out 
First, I'll create a table named "iptest" with a single column named "ipaddr" that has a ipv4 data type. Then I'll try to insert some valid and invalid data.
CREATE TABLE iptest (ipaddr ipv4)
GO
INSERT INTO iptest (ipaddr) VALUES ('4.2.2.2')
INSERT INTO iptest (ipaddr) VALUES ('255.255.255.255')
INSERT INTO iptest (ipaddr) VALUES ('255.255.255.256')
INSERT INTO iptest (ipaddr) VALUES ('random text')
GOThe following results were returned, as expected:
(1 row(s) affected)
(1 row(s) affected)
Msg 513, Level 16, State 0, Line 4
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'AdventureWorks', table 'dbo.test', column 'ipaddr'.
The statement has been terminated.
Msg 513, Level 16, State 0, Line 5
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'AdventureWorks', table 'dbo.test', column 'ipaddr'.
The statement has been terminated.
Excellent! And there's how to create a somewhat useful UDD in a couple easy steps. If you want to just copy and paste the code in its entirety, click here to get all the SQL listed in plain-text format. Thanks to Burleson Consulting for the posting the CREATE RULE that gave me hope.
SQL Server: Free Yet Noteworthy E-book Package from Red Gate Software/Simple-talk.com
Before I start, I thought I would mention that I've always thought there was something a little suspicious about simple-talk.com. I couldn't quite put my finger on it, but.. ok for instance, one of their bloggers, "Phil Factor" (FILLFACTOR -- get it?), kinda freaks me out. Maybe it's his avatar.
Or maybe it's because he is so very anonymous. With that kind of talent, why on earth is he hiding his identity? Also, I also don't understand where all of these tremendously talented SQL pros came from and why simple-talk isn't super well-known. And how on earth did they score such influential authors as Joe Celko or even Louis Davidson?
So I'm poking around the the site for articles by DBA wiz Brad McGehee and I realize why the company seems so strange to me.... They are British! See, while I can appreciate British music (most of my library is filled with BBC Radio 1 artists), I've never been able to "get" other things they like, like British Humor. Ab fab? Not funny. Bland food? Not delicious. Powerless Monarchy? Not even grammatically correct.
But jokes about the British aside, simple-talk articles are bloody informative and well-written. Brad McGehee's posts and e-books about SQL Server are no exception. Here are a couple of my favs, along with a bonus from Louis Davidson.
- BEST DBA CHECKLIST EVER
The title says it all. Also available in PDF format if you want to professionally print a pretty poster. - A zipped collection of exceptional DBA e-books
I didn't want to link directly to the 14MB zip, but to get just the e-book collection and not the software+e-book, click "Just the e-book please" link. This collection includes:- Brad's Sure Guide to SQL Server 2008
This Top 10 list of New Features for DBAs somehow manages to weigh in at 113 pages of awesome. - How to Become an Exceptional DBA
This e-book sports about 80 pages of exceptional advice for DBAs. With chapters dedicated to managing your Online Brand as well as Managing Your Brand within Your Organization, this book also includes advice for techs who aren't DBA. Topics covered include honing your people & communication skills, time management, education and the GOYA principle. - DBA Best Practices
PDF version of "THE BEST DBA CHECKLIST EVER" mentioned above. - Pro SQL Server 2005 High Availability
Oh whoa, this is the ENTIRE 775 page book by Allan Hirt/Apress.
- Brad's Sure Guide to SQL Server 2008
- Ten Common Database Design Mistakes
Written by Louis Davidson, the person who wrote the APress book "Pro SQL Server 2005 Database Design and Optimization.
And in celebration of passing my first MCITP exam (70-431 - SQL Server 2005 Implementation and Maintenance) last Wednesday, here's a link to McGehee's article "Professional Certifications for DBAs."
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
What's Your Job Title?
Loading ...
information
wants to be free
