I have a local copy of SQL Server 2000 installed with full admin
rights. I am working with a database on a remote server and I only have
permissions to that DB. I cannot setup
backup/synchro/replication/push/pull/etc due to not having the proper
permissions.
I have been using DTS to transfer the data to my local DB. But, is there
a good way to automate this process? Basically I just want to copy the
data locally everyday as a backup in case the remote server dies. Would
it be possible to create local job that transfers the data or am I stuck
doing this manually?
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comSchedule your DTS package to run every day.
I believe you just right-click on the package to scedule it.|||Thanks. I will settle with doing that. I was also considering a 3rd
party tool if this does not do the job.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||So far it is working fine. But, I remember the reason I did not use the
schedule was because sometimes tables would be added and skipped by the
DTS since I am not copying all objects. I find that copying tables is
more reliable then objects/data. If only DTS could also add any new
table created. But, to make a long story short, this not seems to be the
best option considering it is the only option I have to transfer data.
Thanks
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||Well I have to copy objects anyway, just realized copying table data
fails when the table object already exists in the source database...
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||You may consider using DB Ghost (http://www.dbghost.com) which can be
scheduled and as it does a comparison - it will only get the data that it
needs where as DTS will have to get all the data. In fact I syncrhonize my
local database with the production system every day using this script:
http://www.innovartis.co.uk/downloa...J
ob.zip
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Lewis - Website Nation" wrote:
> Thanks. I will settle with doing that. I was also considering a 3rd
> party tool if this does not do the job.
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 4/24/2005 3:13 PM, Greg Gilman wrote:
>|||Thanks for the script Mark. I will also checkout your software. So far
the DTS object/data transfer is working fine.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/25/2005 3:58 AM, mark baekdal wrote:
> You may consider using DB Ghost (http://www.dbghost.com) which can be
> scheduled and as it does a comparison - it will only get the data that it
> needs where as DTS will have to get all the data. In fact I syncrhonize my
> local database with the production system every day using this script:
> http://www.innovartis.co.uk/downloa...br />
rJob.zip
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database chang
e
> management for SQL Server
>
> "Steve Lewis - Website Nation" wrote:
>
Showing posts with label local. Show all posts
Showing posts with label local. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
Best way to promote DTS packages
To make the packages portable I have made connections as (local). Because once the packages go from say Development to Production, they would still work without re-designing them on a different box. The '(local)' as a the servername always looks for a the (local) server its on. But this seems too simple.
I have tired different methods such as using a INI file, Dynamic Protperties task etc., I have used global variables in the packages. Ultimately, I have used configuration tables to supply all the global variables, file names, etc.
Is there a flaw in this idea? Has anyone tired this before? If not how have you made DTS packages portable? Any ideas?
Thanks in advance.Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Originally posted by SHICKS
Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.|||Originally posted by vmlal
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.
Updating the registry on remote machines is an administration issue I don't think I can anwser, but here is how I read the registy in an ActiveX script
Dim sh
Set sh = CreateObject("WScript.Shell")
path=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\FileLocations\Path")
server=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Server")
database=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Database")
username=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Username")
password=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Password")
I have tired different methods such as using a INI file, Dynamic Protperties task etc., I have used global variables in the packages. Ultimately, I have used configuration tables to supply all the global variables, file names, etc.
Is there a flaw in this idea? Has anyone tired this before? If not how have you made DTS packages portable? Any ideas?
Thanks in advance.Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Originally posted by SHICKS
Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.|||Originally posted by vmlal
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.
Updating the registry on remote machines is an administration issue I don't think I can anwser, but here is how I read the registy in an ActiveX script
Dim sh
Set sh = CreateObject("WScript.Shell")
path=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\FileLocations\Path")
server=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Server")
database=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Database")
username=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Username")
password=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Password")
Friday, February 10, 2012
Best way for a SQL Server Service to Log On to the Local Computer
What's the recommended way for all of the SQL Server Services (database
engine, browser, SSIS...) to Log On to the Local Computer. Should it be the
Local System account or should I specify another account. Usually I set the
Log On during installation and never think about it again but I thought it
was worthwhile specifically asking the question.
Michael HocksteinIt is often a good idea to use a domain account with only the privledges it
needs to do what it has to an no more. Have a look at this site for lots of
security related info:
http://www.microsoft.com/sql/techno...ty/default.mspx
Andrew J. Kelly SQL MVP
"michael" <howlinghound@.nospam.nospam> wrote in message
news:9282F5DE-D413-472C-ACA0-5120BD884757@.microsoft.com...
> What's the recommended way for all of the SQL Server Services (database
> engine, browser, SSIS...) to Log On to the Local Computer. Should it be
> the
> Local System account or should I specify another account. Usually I set
> the
> Log On during installation and never think about it again but I thought it
> was worthwhile specifically asking the question.
>
> --
> Michael Hockstein|||Hi, Michael,
I agree with Andrew. In most cases, it is recommended that you use domain
user account for your SQL Server services since trusted security can be
easily established between computers. The domain users are also easily
managed by Active Directory on Domain Controller. As a server side service,
its startup service account should be stable since some operations like
backing up database, creating database, executing xp_cmdshell and SSIS
packages etc may need more permissions (sysadmin or local administrator).
It is convenient that you just add a domain user to your local
administrators group. When you log on your Windows with any login account,
your SQL Server services related jobs, stored procedures and packages
executions will not be affected.
Please refer to the "Server farm-level accounts" section of this article:
Plan for administrative and service accounts
http://technet2.microsoft.com/Offic...5b6-4a7a-8da0-2
d07639d04a11033.mspx?mfr=true
For now, could you please let us know if you need further assistance on
this issue? If you have any other questions or concerns, please feel free
to let us know. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
engine, browser, SSIS...) to Log On to the Local Computer. Should it be the
Local System account or should I specify another account. Usually I set the
Log On during installation and never think about it again but I thought it
was worthwhile specifically asking the question.
Michael HocksteinIt is often a good idea to use a domain account with only the privledges it
needs to do what it has to an no more. Have a look at this site for lots of
security related info:
http://www.microsoft.com/sql/techno...ty/default.mspx
Andrew J. Kelly SQL MVP
"michael" <howlinghound@.nospam.nospam> wrote in message
news:9282F5DE-D413-472C-ACA0-5120BD884757@.microsoft.com...
> What's the recommended way for all of the SQL Server Services (database
> engine, browser, SSIS...) to Log On to the Local Computer. Should it be
> the
> Local System account or should I specify another account. Usually I set
> the
> Log On during installation and never think about it again but I thought it
> was worthwhile specifically asking the question.
>
> --
> Michael Hockstein|||Hi, Michael,
I agree with Andrew. In most cases, it is recommended that you use domain
user account for your SQL Server services since trusted security can be
easily established between computers. The domain users are also easily
managed by Active Directory on Domain Controller. As a server side service,
its startup service account should be stable since some operations like
backing up database, creating database, executing xp_cmdshell and SSIS
packages etc may need more permissions (sysadmin or local administrator).
It is convenient that you just add a domain user to your local
administrators group. When you log on your Windows with any login account,
your SQL Server services related jobs, stored procedures and packages
executions will not be affected.
Please refer to the "Server farm-level accounts" section of this article:
Plan for administrative and service accounts
http://technet2.microsoft.com/Offic...5b6-4a7a-8da0-2
d07639d04a11033.mspx?mfr=true
For now, could you please let us know if you need further assistance on
this issue? If you have any other questions or concerns, please feel free
to let us know. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
Subscribe to:
Posts (Atom)