| Edwin Daniel's profileMicrosoft technologies a...BlogLists |  Tools  Help |
|
February 26 Scripting PublicationsThey say scripts are for actors and manuscripts are for writers. In SQL Server, you can have scripts for publications. One of those reasons people setup replication is because they wanted a disaster recovery solution. One thing that a lot of people don't realize is that SQL Server replication was not designed as a disaster recovery solution. The original intention for replication was to come up with a reporting server or a mobile worker scenario where one or both ends can push data changes. In case you will be using replication as a disaster recovery solution, better be ready for tons of headaches that may come your way. One of this is breaking the replication process on the subscriber side so that users can modify the data on the subscriber. I am assuming either a snapshot or transactional replication scenario as merge replication. The first step is to create a backup of your publication by generating a script. You will need this after failing back to the original server after the failover. There is an option in SQL Server Enterprise Manager to do this. Under the Replication folder, select the Publications folder on your Publisher server. Right-click on an existing publication and select Generate SQL Script. It gives you options on how to generate the script to recreate the publication once you managed to bring it up after a disaster. The script executes the following stored procedures: sp_replicationdboption, sp_addpublication, sp_grant_publication_access, and sp_addarticle. These stored procedures are used in the background whenever you create publications using the wizards February 25 Dynamically pass filenames to a DTS packageOne of the things that you will need to do to import data in a database is to read a file. It could be a CVS, TXT, or any format that can be read by the transformation engine. The problem is, in a dynamic environment, you will always have to deal with different files with different filenames that need to be read in the process. Imagine importing data from a text file generated by a mainframe (or any application or that matter) that is dumped on a specific folder on a daily basis with the filenames including the timestamp. Your transformation would require reading different files from this folder. Creating a simple DTS package to do this is pretty straight forward. What's a bit challenging is to dynamically read those filenames as they constantly change. You have a lot of ways to do it. One of which is to use a script to read the filenames from the folder and pass that as a parameter to your DTS package using the DTSRUN utility. The DTSRUN utility is used to execute a DTS package outside of SQL Server. You can call this from a batch file, a scheduled task or from a custom script. The /A switch allows you to pass a global variable to a DTS package. Another way is to use the ActiveX Script Task in your DTS package to do the same thing - read filenames from the folder and pass it as a global variable as well. In both cases, you definitely need to have a global variable. The former is quite straight-forward and easy to implement. The only problem here is that you may not get the desired results. Imagine importing millions of records and the processing time to read the files is definitely faster than DTS executing the package. The latter, however will be more challenging but you will have the advantage of allowing DTS to take control of the entire package execution. All you need to do is simply run the package. To do this, Add an ActiveX Script Task in your package. This will be used to read the filenames of the files in the specified folder. The ActiveX Script Task starts with an empty script window where you can write your own scripts. The scripts could be anything - read from Active Directory, run services, etc. But in this particular case, we need to deal with the file system. Below is a sample script to do this.
You also need to create a Dynamic Properties Task which will contain the values of the filenames. Name the variable importFilename as this has to be the same as the one you defined in your ActiveX Script Task. The rest is pretty straight forward - define the source, transformation, and destination February 17 .NET 1.1 patch can ruin your daySecurity and critical patches from Microsoft are being released on a regular basis. Chances are that you are pushing them directly on your machines every now and then. What's worse is that these patches may be securing your machines but probably breaking your applications. One of them is the .NET Framework 1.1 service pack 1. A client of mine called up saying their applications are not working fine. Their application is still on classic ASP but calling an ASP.NET web service. Users are compalining that they can log in properly in the application but cannot use it once logged in. Further investigation showed that prior to this happening, they run Automatic Updates on the server hosting the application and rebooted the server. Before the server was rebooted, everything is still working fine. I checked the Event Logs and this is what I found out.
The application of .NET Framework service pack 1 had the ASPNET account reset the password. This, of course, also caused the password to get locked out as application users are being impersonated by this account (by default, if you didn't change the proces model execution for ASP.NET) when they access an ASP.NET application. No wonder the server's application event log got filled up by these errors. There are a lot of ways to get around this but since my client is going crazy about users not being able to access the application, the simplest (and fastest, I should say) is to re-install ASP.NET 1.1 by running the aspnet_regiis.exe -i tool. This installs ASP.NET at the IIS metabase root, which also means re-registering the ASPNET account. So the next time you run automatic updates on a machine with critical applications running, make sure you test them thoroughly on a test environment before pushing those updates on a production server. You'll never know what will hit you next. February 14 Change MSDE's authentication modeIf you're running applications like Windows Server Update Services, Backup Exec, Windows Sharepoint Services, etc., you might be faced with a tough challenge of managing your MSDE instance using the command line. Tasks like changing the security mechanism, setting general instance startup parameters and the likes will be a total headache for a big fan of Enterprise Manager. If you want to set the security mechanism of your MSDE, you have to do it via your registry. Now, you have to be very confortable with your Windows registry and you have to be very careful modifying it. Make sure you do have a backup before doing anything on your registry. In order for you to change the security settings, check the HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode subkey. For a named instance like WSUS and Windows Sharepoint Services, it will be the
HKLM\Software\Microsoft\Microsoft SQL Server\%InstanceName%\MSSQLServer\LoginMode (you can also do this if you're using SQL Server 2005 but you wouldn't bother as you have the Management Studio Express unlike MSDE -
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer) The value will actually tell you your MSDE security mechanism. A value of 1 means that you are using a Windows only authentication while a value of 2 means you are using Mixed mode authentication. Before modifying this value, stop the MSDE service you want to change. After changing the value, restart the MSDE service and try logging in using either a Windows account or a SQL Server login. February 02 SQL Server Agent gone - from (W)MSDE to SQL Server 2005 ExpressA lot of applications and third-party software use MSDE as a backend database as it comes as a free download from Microsoft. As SQL Server 2005 got released, a free version called SQL Server 2005 Express also came as an upgrade for MSDE with a lot of cool features. What's surprising is that it doesn't come with SQL Server Agent unlike MSDE. SQL Server Agent is a service being used by the SQL Server engine (MSDE included) for jobs, alerts and operators. You can create database maintenance plans, raise custom errors and send email alerts to operators using this service which is dependent on the database engine service. I know there has to be a reason behind the change but I'm still in the process of searching and trying to find the correct one until I get something official from Microsoft. |
|
|
