Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2010
    Posts
    33

    Unanswered: Sql 2008 Snapshot / Database triggers

    I am lost on how a Snapshot will affect database triggers. First below is my e-mail to the IT department:

    Code:
    DBA,
    
    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:


    Code:
    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 11:53. Reason: Made it easier to read.

  2. #2
    Join Date
    Jun 2010
    Posts
    33

    My Quest Continues

    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.

    Once I figure out the outcome of my tests I will post it in this form. I could still use some help on setting up my triggers. I came across the below forum:
    How do I track data changes in a database

    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    Wim

    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

  4. #4
    Join Date
    Jun 2010
    Posts
    33

    Can't take a snapshot

    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 came across the below article:
    Generating csv format text form sql table by using pure T-SQL stored procedure
    Generating csv format text form sql table by using pure T-SQL stored procedure.

    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?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  6. #6
    Join Date
    Jun 2010
    Posts
    33
    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...

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Without CDC, you would have to depend on a last_updated column on the tables you are looking at.

  8. #8
    Join Date
    Jun 2010
    Posts
    33

    Take 10... argh!!

    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:

    Scheduling Jobs in SQL Server Express - Part 2 - SQLTeam.com

    But I am extremely new to SQL and have no idea how I can modify the code to make it fire every fifteen minutes. Can anybody point me in the right direction?

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What if you exported the view every 15 minutes using Windows Scheduled Tasks?

  10. #10
    Join Date
    Jun 2010
    Posts
    33

    Sheduled Tasks.

    I am running SQL 2008 Express Edition on a windows server 2003 box. According to the scheduled task wizard your only options on the frequency of a job is:
    Code:
    Daily
    Weekly
    Monthly
    One Time Only
    When my Computer starts
    when I log on
    So that I am aware of it will not run every 15 minues.

  11. #11
    Join Date
    Jun 2010
    Posts
    33

    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.

  12. #12
    Join Date
    May 2011
    Posts
    1

    Database

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •