Transcription
SQL Server 2008 R2 (Pubs, Subs, and Other Replication Appetizers)Database Backup (Database Maintenance Plans)Database and Log BackupsSQL Server 2008 R2 InstallationInstallation, Firewall Settings, etc.-Windows XP Firewall Settings-Windows 7 Firewall Settings-Sql Server Connection ConfigurationCreating Publications in Sql Server 2008 R2Using the WizardCreating Subscriptions in Sql Server 2008 R2Using the Wizard-Windows XP Synchronization-Windows 7 Synchronization-Database Permissions / Synchronization Propertieso Windows XPo Windows 7Prepared by Kyle Joersz, North Dakota Industrial CommissionFor the RBDMS Annual Users and Training Conference April 2011Much Thanks to the following for input and assistance:Rick Sims, Mississippi Oil and Gas BoardChuck Borcher, Nebraska Oil and Gas Conservation Commission
Maintenance Plans – Database / Log BackupWhenever a database is created, this needs to be done. Don’t forget.In Sql Server Management Studio-ManagementRt-Click on Maintenance Plans-Maintenance Plan WizardFYI, In Sql Server 2008 Express, Maintenance Plans can be created or deleted, butnot edited. This must be done on your server version.Also I like to separate each database into it’s own plan and also the systemdatabases (master, distribution, etc.) together in one plan. Additionally, you willneed to make a plan backing up the LOG file. This will be another separate plan,which you could put all of your non-system databases together or not. DON’TFORGET TO MAKE A LOG BACKUP PLAN or your log file will GROW andtake up the entire hard disk space, right Chuck and Kyle! Thanks Chuck for thesuggestion to put this in this document.Database Backup Maintenance plan.Click Next
Name the Plan, Change the Schedule if desired, Click NextSelect the desired tasks and click next.I found the below tasks as recommended somewhere on the internet, so it must be right!In the following order (also found recommended) . (move up or down)
Define Shrink Database – Select your databaseDefine ReOrganize Index Task – Select your databaseDefine Database Check Integrity Task – Select your database
Define Back Up Database Task as desired. I believe in putting the backup files on aseparate drive other than the default C: drive Sql is installed on.Select Report OptionsClick Finish.
Database Log Backup Maintenance plan – Must schedule or your log file will grow andgrow .And set up similar to above schedule as often as desired (daily, hourly, etc).
SQL Server Express 2008 R2 InstallationMust be Logged in as domain Administrator if on a Subscribers computer.For Windows XP, if Windows Installer 4.5 isn’t installed, install the following:WindowsXP-KB942288-v3-x86.exe. This updates the XP Windows Installer to 4.5.Install Sql Server Express 2008 R2:Windows XP - SQLEXPRADV x86 ENU.exeWindows 7 - SQLEXPRADV x64 ENU.exeClick on “New installation or add features to an existing installation”Accept the License termsNext (Feature Selection)Next (Installation Rules)
Server Configuration – Set all Account Names to NT Authority/Network Service, no passwords,Startup Type – all to Automatic except Full-text Daemon Launcher leave ManualDatabase Engine Configuration – Mixed Mode (enter SA Password)Add “ogd-ou-admin” group from ndgov\ to “Specify Sql Server administrators”Next (Reporting Services Configuration)Next (Error Reporting)
Notes:Installation ErrorsPrior to completing installation, an inventory of projects will show and if any errors are listed,they should be remedied before continuing. For example, one of the steps listed involvesWindows XP and Powershell 2.0. If this is listed as failed, you must install this, which at thetime of this writing is in an Optional Windows Update. I was able to leave this on the stepshowing the failed steps and install this one window update and then go back to this installationbecause no restarts were required for the window update installation. Re-runing the installationsteps should then work. We didn’t see this problem in Windows 7, but didn’t run as manyinstallations on the OS. I didn’t save a screen shot of this error, but it is similar to the one belowwith a direct reference to Powershell 2.0.AGAIN, IF YOU DON’T REMEDY THE FAILED INSTALLATION RULES, MOST LIKELYTHE SQL INSTANCE WILL NOT WORK PROPERLY. TRUST ME.And if you do proceed to install with the failures, YOU WILL NEED TO TOTALLY Un-Installthe entire program through Add/Remove Programs and start again.If un-installing the entire Sql Server 2008 Express R2 is involved, YOU MUST also remove acouple of files manually after the un-install is done or it will error on Reporting Services duringthe installation. Leave it to Microsoft! This can done while leaving the installation programopen where it is.Do the manual cleanup below and then return to it and click Re-Run.You will then have to remove the following files (they all may not exist) from the "C:\ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA" directory:ReportServer.mdfReportServer log.LDFReportServerTempDB.mdfReportServerTempDB log.LDF.Installation progresses to Completion - RESTART
Configure Sql Port Firewall Connections for Windows XPInstall Windows XP Firewall exceptions (through Windows Firewall – Exceptions – Add Port,add each of the below ports, or to make it easier, we made a batch for this calledSQL2008Express FireWall.bat:(netsh firewall set portopening protocol TCP port 1433 name SQLPort mode ENABLEscope ALL profile CURRENTnetsh firewall set portopening protocol UDP port 1434 name SQLBrowserPort mode ENABLE scope ALL profile CURRENTnetsh firewall set portopening protocol TCP port 135 name SQLWMIPort mode ENABLE scope ALL profile CURRENTpause)The .bat file runs in Windows 7 and works but throws a couple of errorsManually:Notes since I didn’t know what these were:WMI Windows Management InstrumentationUDP User Datagram ProtocolManual: Open Windows Firewall – ExceptionsClick Add PortName: SqlPortPortNumber: 1433
Repeat for SqlBrowserPort UDP 1434Report for SqlWMIPort TCP 135Configure Sql Port Firewall Connections for Windows 7Open Windows Firewall – Advanced Settings – Click on InBound RulesClick on New RuleClick Port
Select TCP and Specific Local Ports 1433Click Allow the ConnectionSelect Domain (unselect Private and Public)
Name the RuleRepeat for UDP 1434 as SqlBrowserReport for TCP 135 as SqlWMIPortConfigure Sql Server Manager SettingsLog in as AdministratorOpen Sql Server Configuration Manager (Start-Programs-Microsoft Sql Server 2008 R2Configuration Tools-Sql Server Configuration Manager)Sql Server Network Configurationo Protocols for SQLEXPRESS Named Pipes – Enable TCP/IP – Enable- Sql Server Serviceso SQL Server (SQLEXPRESS) set to Automatic– Restart to apply the aboveo SQL Server Browser set to Automatic and start it.Make sure both the Sql Server (SqlExpress) and Sql Server Browser are set to Automatic Startmode and running.You will most likely need to either restart SQL Server (SQLEXPRESS) above (rt-click) orRestart the entire computer for the client protocols to be effective immediately.Verify a Remote connect to the instance through Sql Server Management Studio can be made.
Creating a Publication in Sql Server 2008 R2Using the WizardConnecting to the Server instance, rt-Click-Local Publications-New Publication
Select the databaseSelect a publication typeSelect Subscriber TypeIf you are publishing to an older Sql Server such as 2000, you will only able to create thesame type of publication that was created using the older version. In other words for us,only Merge publications. When we upgrade our web server to Sql Server 2008, we willswitch the publications to strictly 2008. See Next screen shot!
Set Article (Table) properties and for all table articles to start with.We didn’t want the User Triggers replicated so we set all table properties to this.
To cut down the number of conflicts that could be encountered, it may be desirable tochange the “Tracking level” setting for all tables from the default of “Row-leveltracking” to “Column-level tracking”. Row-level implies that if changes from twodifferent users (one being a subscriber) change two different columns in the same row, aconflict will be generated. By changing this setting to Column-level, conflicts will onlybe generated if it on the exact same column for the record. (Thanks to Chuck (Nebraska)and Rick (Mississippi) for this information.Now for individual table properties:Select a table and select Set Properties of Highlighted Table Article
For ALL tables containing an Identity column . You should define the Publisher /Subscriber ranges. In some tables the default will work, but they should be reviewed.Adjust the Publisher and Subscriber Range sizes for all table columns that areIDENTITY. There can be only 1 Identity column per table.Range Sizes- Publisher Range SizeMake this large enough to handle current record count PLUS room for enough futuregrowth until the next time you break replication. For example there are approximately94,000 rows in tblAPD WorkFlow History. I would set the Publisher range size to2,000,000. The top limit is around 1 billion for identity columns of integer types so thisshouldn’t present a problem at all for this table. The default is 10,000. Every table mustbe evaluated separately and precisely.- Subscriber Range SizeMake this one large enough to handle what a subscriber needs for inserting recordsBETWEEN synchronizations. For this particular table no subscribers insert into thistable, so I would leave it at the default of 1000.We didn’t realize the Publisher Range Size worked this way thinking that Publishersimply had a larger range given out during synchronization so we didn’t make therange large enough for a handful of tables and thus we have gone over the PublisherRange Size with the current record count. What has happened seems to be the Publisher(server) now acts as a Subscriber as to the Range Size (1,000), and it doesn’t update byitself when it gets above the threshold percentage, since it doesn’t ever synchronize.Upon extensive research, and trial and error, 2 ways for the publisher to get its rangesreset once it reaches it top side. Either a Merge agent runs, or the user that is insertingthe record is a dbo. Since the ranges are reviewed for each subscriber when theysynchronize (via the merge agent running), they are all take care of. Since the mergeagent doesn’t run on the publisher side, this doesn’t get reviewed automatically. So whensomeone connected directly to the server for this particular table(tblAPD WorkFlow History), when the 1,000 ranges maximum value is reached, theuser will get the following error:
To “open up” the problem table, the user inserting on the server must either be a dbo, inwhich case the range will be updated or the following procedure must be run (Mastertable system stored procedure):sp adjustpublisheridentityrangeThis will assign a new range to any table that is locked up. We have this scheduled to runevery hour, but occasionally have to run it upon request. We also have determined thatthe threshold setting (default 80%) doesn’t seem to apply to the Publisher, soscheduling it doesn’t seem to eliminate the error occuring.Other examples:tblLeaseProdWell current record count approx. 1,900,000. I would make the PublisherRange 100,000,000 and the since no subscribers add records to this table SubscriberRange 1,000 should do.tblFieldInsp current record count approx. 350,000. This table is updated and insertedinto primarily by the subscribers, but they are extremely good about synching regularlyand would never enter more than 1000 between synches, but I would set the SubscriberRange at 2,000 or 5,000 just in case and the Publisher Range at 10,000,000.Click NextClick Next
Take out the “Create Snapshot immediately” if you want to control the location of theSnapshots. By default it is checked. You can modify the location path and then run thesnapshot at that time. We will visit this shortly.You can modify the snapshot schedule now or wait until later.Click Next
The default for the snapshot agent is to run under the following windows account. Wehave this running as shown below, but you could enter in an account to run as.Click Next and Finish to create the Publication.We left this one for now.Click next and Enter a Name for the publication and click Finish.
Now review and adjust the location of the Snapshot.Rt-Click – Properties on the PublicationClick on Snapshot and if you want a different location than default – RECOMMENDED,click on “Put files in the following folder:” and put the desired path in.
Although there are other things to be done in Properties, we will close this and run thesnapshot and come back later to this.Rt-Click – Properties on the Publication.Click Start to generate the snapshot. Close all Publication Properties windows.To adjust the snapshot schedule, Open Database name in Management Studio - rt-Clickproperties on the job in Sql Server Agent - Jobs
Click on Schedules – and then on Edit. Other settings are available in this includingnotifications, etc. Play as you have time.Adjust as desired.Close the job and all forms relating to it.Return to Publication Properties (Rt-Click-Properties on the Publication).
General – Make sure you adjust the Subscriptions expire Interval. The default is14 days. This means if a subscription hasn’t synched in 14 days, it will expire and willhave to be recreated. We change all of our subscriptions to 60 to cover any chance oflengthy absences or illnesses.Articles can be adjusted here in case you included something you didn’t necessarilyneed. Filters can be created here if necessary. We previously visited Snapshot.Click on Publication Access List (very important)
If you use Windows synchronization for subscriptions, each of the user (or rolesincluding the user) must be included here. Click Add. All users must also have access tothe database the publication is for, so they need to be in Database-Security-Users.Click on Agent Security – Security Settings.Adjust if desired.Close Publication Properties and all affiliated windows.
Creating Subscriptions in Sql Server 2008 R2Using the WizardRt-Click on the Publication – New SubscriptionClick NextSelect the publication, and Click Next
We like to run pull subscriptions so select “Run each agent at the Subscriber”, Click NextClick “Add Sql Server Subscriber” and Connect to that instance.
Select the database (Select New database if doesn’t exist yet). Click NextClick on the build button ( .). You can change the Windows account the Merge Agentruns under or leave the default (below). Click OK
Click NextClick NextClick Next
Click NextClick Finish to create the SubscriptionCloseInitialize the Subscription by logging onto the Subscriber as a Sql Server Administratoror a database dbo. We did this through Remote Desktop Connection for our Field officepersonnel. The logon must also be in the Publication Properties – Publication AccessList. Since there is no merge agent in Sql Server Express, Windows Synchronization isthe vehicle to initialize and synchronize the subscription.
Windows XP SynchronizationStart Windows Synchronization (Program Files – Accessories – Synchronize).Recommend desktop shortcut to Synchronize and put it in the All Users – Desktop.Select the Subscription and click Synchronize.This could take a while, depending on the amount of data, especially if over the network.
Windows 7 SynchronizationStart Windows Synchronization (Start-All Programs-Accessories-Sync Center)Rt-Click-Open Microsoft SQL Server 2008 to view subscriptionsRt-Click on Subscription to Start Synchronization
Database Permissions / Synchronization PropertiesPermissions for the user must be copied or entered into the sql server instance in order forthe user to be able to synchronize using their login account. The permissions can bescripted from the server or entered manually.Add a sql synchronization account into the system. This must be entered in bothinstances’ databases (Server and Subscriber). The account MUST be a dbo on bothinstances. I have never been sold on giving a user any account with dbo permissions, butI haven’t found any other way to synchronize seamlessly.The user will be required to enter the password twice every time they synchronize somake it simple.Open Windows Synchronization, Select the Subscription, and click on Properties (ordouble click on it).Windows XPSelect the Subscriber Login tabChange to “Use SQL Server Authentication” and enter in the account and passwords.Click Apply and OK. From then on, the user will be prompted with this screen. Only theLogin Name will be filled out. After they enter the passwords, the synchronizationshould run.
Windows 7Rt-Click-Properties on the subscriptionClick on More Click on Subscriber Login and input the Administrative credentials.We are currently working on a VB application that will emulate the WindowsSynchronization, use the currently logged on Windows account, but synchronize as (runas) an Administrative Sql Server account in the module. This way the end user will nothave to enter the passwords. Also, something lost in Sql Server 2008 that was in 2000was the Merge Agent. In 2000, the MA allowed for synchronization to be scheduled atthe user’s discretion. There is not MA in “Express”. We also are working onprogramming this VB app with a scheduling capability.
SQL Server 2008 R2 (Pubs, Subs, and Other Replication Appetizers) Database Backup (Database Maintenance Plans) Database and Log Backups SQL Server 2008 R2 Installation Installation, Firewall . The top limit is around 1 billion for identity columns of integer types so this shouldn't present a problem at all for this table. The default is .
