I am lost on how a Snapshot will affect database triggers. First below is my e-mail to the IT department:
I need to know the exact replication process that is setup to push data
to our Replicated Databases, so I will know the feasibility of setting up
database triggers for an interface I am creating. Of course, Microsoft
Server provides three types of replication options which are transactional
replication, merge replication and snapshot replication. I thought you had
told me there is a nightly snapshot along with transactional replication
throughout the day, which is what I need verified. Data changes are not
tracked for snapshot replication; each time a snapshot is applied, it
completely overwrite the existing data making the possibility of using
database triggers in my interface less of an option for me.
Response I received:
They are filtered transactional replications. I create a snapshot,
but it is not used unless I re-initialize the subscriptions. I only do that
if I make db changes. I do not overwrite the database.
Now let me explain what I am trying to do. I have access to my filtered Replicated Database. I need to push a file whenever a record is changed or added to a remote location. The record would then be read by API to update there tables. I had thought maybe a Database Trigger would be the soultion, but if the Snapshot actually overwrites my replicated base tables what will happen with the database trigger?
Last edited by ptyo; 05-02-11 at 10:53.
Reason: Made it easier to read.
I have been researching triggers for a few days now and still find myself a little confused on how a snapshot restoration will effect my triggers. In the event the IT department has to revert to the snapshot it is in a granular fashion...( one or two tables etc..)
I have decided to answer my question I will create a trigger and then just have them restore a table that has a trigger and see what happens.
I have numerous tables I need to link togather to provide the data I want exported in a .csv file. My thoughts are to create a view that has the fields I wish to monitor. I need a process that basically says something changed in my fields of interest, which I believe the above referenced article will do. I am pretty sure I can get that working just fine, but can I have the trigger call some kind of stored procedure that produces my .csv file and dumps it somewhere?
I would greatly appreciate any advice or links that hit on this topic area.
Create a few tables, a few triggers that operate on those tables (either by being CREATEd on them or by writing or reading data from them), INSERT some data in your tables and do your snapshot tests on that system. Keep it very simple (at the beginning).
Do nothing manually (don't use a GUI to do all that stuff), so you can redo it over and over again and tweak your scripts in a reproducible way.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Well.. I have come to find out that I am unable to take a snapshot. I am using Sql 2008 express for all my testing. I don't think the IT Department will let me take a snapshot on the Sql 2008 Pro box either. So I'm back to researching other ideas.
I have that up and working and now I am trying to get it output to a file maybe using BCP. I have no idea how I can change the Procedure to produce the result. I would appreciate any help or ideas. Maybe I am tackling this whole problem in the wrong way?
If there are not that many changes over time, you could probably ask the DBA's who own the central server if they can look into setting up Change Data Capture for the table in question. Since they have transactional replication already set up, there should not be a significant change in performance. Then they can send your remote location a daily file (or hourly, if they are impatient noodges), instead of a file for each change.
We are on SQL 2008 standard which of course doesn't support Change Data Capture according to my research on it. I am thinking that SSIS may be the way for me to go, any way to grab changes without CDC feature? Maybe I can connect my database table to theres with SSIS is what I was kind of thinking? but would it always push all the table records or just those that have been updated, inserted, etc...
Okay. I have created a view that has all the data I want to export to a .csv.. I don't have access to setup agents.. So i am limited to what I can do in SQL 2008 express edition. I need to create my .csv dump every fifteen minutes. I came across the below:
Installing SQL 2008 Standard edition / Security question
Okay in order to get my interface up and running I have purchased SQL 2008 standard which I will be installing on a virtual machine. I have also purchased a bundle learning package SQL Server 2008 DBA/Developer Training Package from Affordable Online Training Videos | LearnItFirst to help me learn. However I have a security question considering the access to my new box that I need to give the IT Department. There will be some sort of replication setup that pushes to my box and I want to ensure the account I create for the IT Department can only push data tables / procedures / and other data elements. I don't want the account to be able to view anything just push. What do I need to start researching on? I hate to do it but I wish to give them a taste of their own medicine. I want to lock my database down like fort knox.
Well, the good news with varchar2 is that it does not take up 4000 bytes. Your column can hold up to 4000 bytes, but it will use only what the length of the data/record is. Unlike char, MS SQL Server to MySQL the length used for char in the DDL will take up that length regardless of how much or how little is used.
select max(length(your_coulmn)) to see how big the largest tuple is.
You can also drill down in the stats for the table column to see data distribution, but if you need the exact max length (not what was sampled via estimate, or if not indexed), then use max.