Share
Report
208Hello Everyone,
So I"m running CRM 4.0 and boy has it been a wild ride these last few days.... but that's not necessarily why I'm here asking this question.
1. The Question: How to Selectively take one or more organizations offline (disable) to prepare the DBs for daily backup through SQL Management Studio 2005?
2. What I know So Far: The following is my knowledge on how the back end works:
- Whether an organization is enabled or disabled is stored as a binary value in MSCRM_Config in the table Organization with the field specifically called State (1 = Org enabled, 0 = Org Disabled)
- The name that appears in the upper right on the web client (e.g. AdventureWorks, Microsoft, Demo, etc.) is stored in the same table but in a field called FriendlyName
- if the organization is disabled (state = 0) then users cannot access it from the web or outlook clients thus making it ready for backup
- All my production organizations have a friendly name like "iCARE xx.xx (PRODUCTION ENVIRONMENT)", "iCARE xx.xx (TRAINING ENVIRONMENT)", "iCARE xx.xx (DEVELOPMENT ENVIRONMENT)"
- These names help me and users know which system they're in just by looking at the top and the names break down like this (1) iCARE is how the CRM is referred to internally in documentation (2) xx.xx represents an internal version number (example 14.2i) always consisting of a number (1-99) followed by a dot then a number (0-9) and a letter in lowercase (a-z) (3) in parenthesis after the internal version number is friendly identifier such as "PRODUCTION ENVIRONMENT", "DEVELOPMENT ENVIRONMENT", "TRAINING ENVIRONMENT", etc.
3. What I want to accomplish: every day between 12:00 AM and 12:30 AM disconnect all logged on users, disable the organizations who's FriendlyName begins with "iCARE" and who's state is 1, kill all SQL connections, check the database integrity then take a full backup the database to C:\Database_Backups\iCARE_Backup\ID_YYYYMMDD.bak thus the backup files can be caught by my ntbacup scheduled job at 03:00. Once the Backup completes bring each database back to a usable state where users can log on to it. I want al this done sequentially (e.g do all the above for one instance an when that completes do it for the next and so on) Note: ID_YYYYMMDD is a placeholder for a name like "PRODUCTION_20210815" Thus I know which iCARE DB goes with which backup and when it was done
4. What I've Done So Far:
I've attempted to at least make sure that my logic is sound running the following select query:
USE MSCRM_Config
SELECT FriendlyName, State
FROM Organization
WHERE (FriendlyName LIKE N'[iCARE*') AND (state = 1)
I built this using the query designer and when I hit check it validates just fine but when I run it I get 0 records back
The reason I want to make sure that I only get active instances that start with "iCARE" is because I have an instance that uses the demonstration data with a friendly name of AdventureWorsCycle that I do not want backed up every night. In fact, I've built a custom script to restore the one backup I have made of it in the state I want it in every day at midnight thus resetting the demo... maybe something from that script can help to make my auto-backup hing work...
Find below the code of my auto-restore script:
USE [msdb]
GO
/****** Object: Job [Restore Database - demo_MSCRM] Script Date: 08/12/2021 19:47:52 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/12/2021 19:47:52 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Restore Database - demo_MSCRM',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'WINX-SPARKS-SQL\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Inactivate Organization] Script Date: 08/12/2021 19:47:53 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Inactivate Organization',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SELECT DatabaseName, FriendlyName, State
FROM Organization
WHERE (FriendlyName = N''AdventureWorksCycle'') AND (DatabaseName = N''demo_MSCRM'')
UPDATE Organization
SET State = 0
WHERE (FriendlyName = N''AdventureWorksCycle'')',
@database_name=N'MSCRM_CONFIG',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Close All Current Connections and Restore DB to Save] Script Date: 08/12/2021 19:47:53 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Close All Current Connections and Restore DB to Save',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE master
ALTER DATABASE [demo_MSCRM] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [demo_MSCRM] FROM DISK = N''C:\Database Backups\Demo CRM\Sample DB\Sample-Data.bak'' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
ALTER DATABASE demo_MSCRM SET MULTI_USER',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Reactivate Organization After Restore] Script Date: 08/12/2021 19:47:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reactivate Organization After Restore',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SELECT DatabaseName, FriendlyName, State
FROM Organization
WHERE (FriendlyName = N''AdventureWorksCycle'') AND (DatabaseName = N''demo_MSCRM'')
UPDATE Organization
SET State = 1
WHERE (FriendlyName = N''AdventureWorksCycle'')',
@database_name=N'MSCRM_CONFIG',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Reset Demo DB',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20210813,
@active_end_date=99991231,
@active_start_time=23000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
So with that script in mind can we figure out a way to do what I want strictly using the tools in SQL server 2005, windows, and MSCRM4.0?
5. MSCRM 4.0, SQL Server 2005, Server 2003, Office 2003/2007 and Exchange Server 2003 are all NO LONGER supported my MS... Why on earth haven't you upgraded?
There are many reasons why I haen't upgraded this setup and here are just a few of them:
1) All the software for this was given to me by a friend who's o longer among the living and thus I personally could never afford it on my own... plus I want to keep the software theist someone paid for running as long as I can (if even in virtual machines) thus not letting the software go to waist
2) If it ain't baroque... why fix it?
3) I really don't like change for medical reasons so see point 2
4) I don't like the idea of loud CRM because of how it would balloon out of hand cost wise for more users than just me (see point 1 in this section) and in the loud I couldn't have 4 simultaneous instances running with any number of users (without having to pay per-instance and per-seat in each of those instances... thus the cost baloons out of control real quickly
6. What about securing all of this because it's all severely out of date software?
Well, here's by strategy:
1) run all server 2003 with SP2 and IE 8 and RDP client 6
2) have all servers except the "gateway" connected to an internal private virtual LAN in VMWare Workstation
3) have a server 2003 running Terminal Services that has two NICs (One that talks only to the other VMs (10.x.x.x IP Address) and the other that is assigned a static IP from the home router (192.168.1.x IP Address)) Thus to get onto the network with the CRM on it one would need to either have physical access to a network jack in the house (thus we'd have to let them in the front door or they'd have to break in) or have WiFi access (Thus we'd either have to give such a person the password or they would have to hack it)
4) DON'T OPEN ANY ports other than the default at the home router and enable the firewall
5) TAKE LOTS OF BACKUPS OF ALL VMs and store them in the cloud (like google drive or some such) and on an external HDD thus if the laptop breaks, the home network gets hacked, etc.. I've got a "check point" of sorts
6) DON'T THINK IT'S NOT POSSIBLE FOR A COMPROMISE TO HAPPEN!!! it is very possible for any point in the system to fail... the home network could get compromised, my VM backups could fail, the laptop dies, etc.... honestly I think my laptop is probably the weakest link in all that. Plus if someone broke in here and physically waked off with my laptop I've got passwords in various places and an encrypted drive... thus it'd be useless
7) My Setup: Here's information about the machines and how software is set up to help in fixing this:
- Server OS: WS2003 R2 Standard (all)
- SQL Server: 2005 Enterprise SP4
- Exchange Server 2003 Enter[rise w/ SP2
- CRM Server: 4.0 Enterprise with all roles on same box and SSRS 2005 (could never get the stupid connector for SSRS to work right)
- No. of DCs: 1
- No. of DNS Servers: 2 (the DC and SQL server are sharing the DNS responsibility for the internal network)
- CRM Updates Applied: NONE (Haven't been able to track down the update rollups)
I hope that's enough to answer my question. Thanks for any help given in this extremely add scenario...
All responses (
Answers (
